vba - 查找以特定数字开头的每个单元格的单元格地址

Question

我正在寻找一个代码，它可以在 L 列中找到以数字“2347”开头的每个单元格。我想获取这些单元格的单元格地址并将其显示在 MessageBox 中，例如“Msgbox: Cells L3500:L3722 has以“2347”开头的值“

Sub Findrow()

Dim MyVal As Integer
Dim LastRow As Long


MyVal = LEFT(c.Value,4) = "2347" _
LastRow = Cells(Rows.Count, "L").End(xlUp).Row 

For Each c In Range("L2:L" & LastRow) 

If c.Value = Myval Then

到目前为止，这是我的代码。希望可以有人帮帮我!

Answer 1

使用数组非常快

Option Explicit

Public Sub FindIDInColL()

    Const VID = "2347"   'Value to find

    Dim ws As Worksheet, arrCol As Variant, found As Variant

    Set ws = ActiveSheet 'Or Set ws = ThisWorkbook.Worksheets("Sheet3")
    arrCol = ws.Range(ws.Cells(2, "L"), ws.Cells(ws.Rows.Count, "L").End(xlUp))
    ReDim found(1 To UBound(arrCol))

    Dim r As Long, f As Long, msg As String

    f = 1
    For r = 1 To UBound(arrCol)             'Iterate vals in col L, excluding header row
        If Not IsError(arrCol(r, 1)) Then   'Ignore errors
            If Len(arrCol(r, 1)) > 3 Then   'Check only strings longer than 3 letters
                If Left$(arrCol(r, 1), 4) = VID Then    'Check first 4 letters
                    found(f) = r + 1        'Capture rows containing value (header offset)
                    f = f + 1
                End If
            End If
        End If
    Next

    If f > 1 Then                           'If any cells found
        ReDim Preserve found(1 To f - 1)    'Drop unused array items
        msg = "Cells in col L starting with """ & VID & """" & vbNewLine & vbNewLine
        MsgBox msg & " - L" & Join(found, ", L"), , "Total Found: " & f - 1
    Else
        MsgBox "No cells starting with """ & VID & """ found in col L", , "No matches"
    End If
End Sub

使用 string versions 时甚至更快这些功能中的

Left$() Mid$() Right$() Chr$() ChrW$() UCase$() LCase$()

LTrim$() RTrim$() Trim$() Space$() String$() Format$()

Hex$() Oct$() Str$() Error$

正如 QHarr 所指出的，它们更有效(如果 Null 不是问题)