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

我已经搜索了 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

最佳答案

如果范围已格式化为科学记数法，这将适用于一个单元格并返回一个 msgbox。

Sub BillNye
Dim MyRange As Range
Set MyRange = Range("A1")

If (MyRange.NumberFormat = "0.00E+00") Then
MsgBox "Scientific"
End If
End Sub

这将扫描范围 A1:A10 以获取科学格式，并在 B 栏的单元格中发表评论(如果它是科学的)。您将需要修改您的范围并根据需要偏移等式。正如代码所示，您可以过滤“科学”的 B 列以仅查看科学格式的值。

Sub BillNye()

Dim MyRange As Range
Set MyRange = Range("A1:A10")
Dim Cell As Range

For Each Cell In MyRange
    If (Cell.NumberFormat = "0.00E+00") Then
    Cell.Offset(, 1) = "Scientific"
    End If
Next Cell

End Sub

我建议将最后一行的范围修改为动态(除非您只需要使用一次，否则静态范围就可以了。)

关于Excel VBA-如何用科学记数法识别单元格，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50690545/