0

Problem: I'm trying to run a match function based on a range that has variables.

Scope: The goal is to find the first instance of the word "awesome" in column A between row x and the last row; and return the row number

Case:

LastRow = Sheets("demo").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To LastRow
Dim rng as Range
Set rng = "A" & x & ":A" & LastRow

Attempts:

"=Match("awesome","A" & x & ":A" & LastRow,0)"
"=Match("awesome",range(""A"" & x & "":A"" & LastRow),0)"
"=Match("awesome",rng,0)"
"=Match("awesome",range(rng),0)"

Nothing Works!

Yosef
  • 1
  • 1
  • `=match("awesome", A:A, 0)` will do. Otherwise you probably want to see [How do I put double quotes in a string in vba?](https://stackoverflow.com/q/9024724/11683) and [Passing Range Variable into formula in Excel Macro](https://stackoverflow.com/q/10811719/11683). – GSerg Sep 18 '17 at 14:24
  • Thank you for the response, however, I need to make the range based on a variable.I understand the double quotes part of it in general. I'm just having a hard time working that into the range of a Match function. – Yosef Sep 18 '17 at 16:18

1 Answers1

0

This code will return the row number. The lookat:=xlWhole matchs the entire cell value, lookat:=xlPart, will match any part of the cell value

firstrowmatch = Worksheets("Demo").Range("A" & x & ":A" & LRow).Find(what:="awesome", lookat:=xlWhole).Row
mooseman
  • 1,997
  • 2
  • 17
  • 29