Excel VBA-如何用科学记数法识别单元格

Question

我已经搜索过SO，但未能成功找到解决方案 - 如何通过 Excel vba 识别列中采用科学记数法的单元格？

这是我尝试通过带有科学记数法的自动过滤器行进行隔离的代码...

Private Sub cmdMoveScientificNotation_Click()

Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim objWBtoAdd As Excel.Workbook
Dim newWS As Excel.Worksheet
Dim objWS As Excel.Worksheet
Dim i As Long
Dim Lrow As Long
Dim strSearch As String
Dim myRange As Range
Dim Cell As Range


Set objExcel = New Excel.Application

objExcel.Visible = True

Set objWB = Workbooks.Open("C:\TEST\Drop\Bank.xlsx", , False)
Set objWBtoAdd = Workbooks.Add
Set newWS = objWBtoAdd.ActiveSheet

Set objWS = objWB.ActiveSheet

    With objWS

        Lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        myRange = .Range("A1:V" & Lrow)

            For Each Cell In myRange
                If (Cell.NumberFormat = "0.00E+00") Then
                .AutoFilter Field:=2, Criteria1:=strSearch
                .SpecialCells(xlTypeVisible).EntireRow.Copy
                End If

            Next Cell


    End With

newWS.PasteSpecial


End Sub

Answer 1

类似于:

If IsNumeric(cell.Value) And cell.text like "*E*" Then

如果数字太长而无法适应单元格的宽度，Excel 有时会切换到科学记数法而不更改数字格式。

如果您只需要查找已格式化为科学格式的单元格，请查看:

cell.NumberFormat