0

I had my constant string defined as the cell, 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 something.

I don't know how to assign part of the string to my Const value

I tried:

  Const LID_LIFTED As String = Like *"Lifted*"

I also considered:

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

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

Const LID_LIFTED As String = rng1

I 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 with the following error:

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

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

Community
  • 1
  • 1
Geographos
  • 827
  • 2
  • 23
  • 57

1 Answers1

3

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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • The code seems to be fine, but my last line still throws error: atSource.Range(f).Copy datTarget.Range("H" & lrow1).PasteSpecial xlPasteValuesAndNumberFormats Method 'Range of object'_Worksheet failed – Geographos Dec 20 '21 at 16:56
  • Just `f.Copy` since `f` is already a `Range` object – Tim Williams Dec 20 '21 at 16:57
  • f.Copy throws error "Object variable of with variable not set". It looks like something is still missing here. – Geographos Dec 21 '21 at 12:46
  • `If Not f Is Nothing then` should prevent that error, so something else is wrong. Can you edit your question and add your current code? – Tim Williams Dec 21 '21 at 17:09
  • You can see my further steps here: https://stackoverflow.com/questions/70437456/copy-specific-data-from-all-ms-excel-files-in-the-directory-in-correct-order?noredirect=1#comment124512197_70437456 – Geographos Dec 21 '21 at 17:12