Declare const variable using wildcard-CodePudding

I wanted to know if there's a possibility in declaring a const variable in Excel VBA using a wildcard, like in this example:

const myVariable As String Like "UP field*"

I need it because I have a cell field that can have several values but it always contains the substring "UP field", but sometimes it is like "UP field 5" or "UP field 3" or like that.

I just need Excel to find the first row of one table and that variable is the name of the cell that starts this row. It would be like my A1 cell but it can be anywhere in the Excel sheet so I use a while loop to find that cell:

i = 1 
While ActiveSheet.Cells(i, 1) <> myVariable 'Looking for the first row     
  i = i   1 
Wend

CodePudding user response：

You don't need a constant for this. Range.Find is a quick way to find the first of something and passing xlPart for the LookIn argument will satisfy the wildcard requirement. For example:

Public Sub FindFirst()
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A:A").Find(What:="UP Field", LookAt:=xlPart)
    
    If Not rng Is Nothing Then
        MsgBox rng.Address
    End If
End Sub

A while loop could've worked if you coupled it with InStr. I wouldn't recommend it, but I'm adding here for completeness:

Public Sub FindFirst2()
    Dim str As String, i As Long, rng As Range
    str = "UP field"
    i = 1
    Do
        Set rng = ActiveSheet.Cells(i, 1)
        i = i   1
        If i > ActiveSheet.Rows.Count Then Exit Sub
    Loop While InStr(rng.Value, str) = 0

    MsgBox rng.Address
End Sub

CodePudding user response：

I think this might work for you:

Const myVariable As String = "UP field"

i = 1
Do Until ActiveSheet.Cells(i, 1) Like myVariable & "*" 'Looking for the first row
  i = i   1
Loop

This will quit the loop at first cell starting with text UP field.