Defining just part of the string as a constant-CodePudding

I had my constant string definied as the cell previusly, which was looking like this:

  Const LID_LIFTED As String = "A19"

Everything seemed to be fine, but as I convert batch stuff from PDF to Excel, the rows are shifted slightly therefore the value from A19 finally might fall at A18 or even A16. I came to the conclusion, that better could be to grip my constant value as the string occurrence and then think about the offsets or sth.

The problem is, that I don't know how to assign just part of the string to my Const value

I tried:

  Const LID_LIFTED As String = Like *"Lifted*"

but it wasn't correct.

I also considered the other option:

https://excelmacromastery.com/excel-vba-find/

  Dim rng1 As Range
  Set rng1 = Range("A1:A100").Find("*lifted*")

  Const LID_LIFTED As String = rng1

but in this case, I've got an error:

"Constant expression required"

Another option below:

 Dim ws As Worksheet
 Set ws = ThisWorkbook.ActiveSheet

 Dim String1 As String
 String1 = ws.Range("A1:A100").Find(what:="lifted", lookat:=xlWhole)

threw error:

"Object variable or with variable not set"

and the third option:

  Dim txt1 As Long

  txt1 = Application.WorksheetFunction.Match("*lifted*", Range("A1:A100"), 0)

didn't work either with the following error:

"Unable to get the Match property of the WorksheetFunction class"

How can I include the part of the string in my const value?

CodePudding user response：

Constants must be resolvable at compile time - they are not evaluated at run time. This means constants need to be a fixed value (or based on some other fixed value), not something which needs code to run before that value can be assigned.

Find() returns a Range object (or Nothing if no match was made), so you can't safely assign the return value to a String

Const LID_LIFTED As String = "Lifted"
Dim f As Range
'specify a worksheet, and provide all arguments to Find which might affect the outcome...
Set f = Sheets("Sheet1").Range("A1:A100").Find(What:=LID_LIFTED, _
                              Lookat:=xlPart, lookin:=xlValues)
If Not f Is Nothing then
    'do something with f
End If

Your last option should work (and does for me), though you likely should specify a specific worksheet for the range to be searched. WorksheetFunction.Match() raises a run-time error if no match is made - that's what you're seeing.