0

I'm trying to get my head around VBA and the intricacies / similarities to Excel Formula.

The problem I have is how to best find the row and column of a search string inside a table.

If I use the Excel formula: =MATCH(MyString, Table1[LookupColumn], 0) it works fine.

But how on earth do I do that with:

Application.WorksheetFunction.Match

I have found so many conflicting codes that I'm totally lost for VBA.

I've even tried using Range Find, but I can't get it to work. Any help would be greatly appreciated.

Bob D
  • 3
  • 2
  • 1
    `Range.Find` would be pretty straightforward.... can you share how you tried it? – BigBen Oct 07 '20 at 01:40
  • 1
    If you want to use `Match`, then you would use `Application.Match(MyString, myTable.ListColumns("LookupColumn").DataBodyRange, 0)`. Though it's not clear whether you want the row number *within* the table, or the row number of the actual cell. – BigBen Oct 07 '20 at 01:41
  • Many thanks @BigBen. I missed out on the ListColumns. I'm quite new at VBA and it is quite tricky to understand the ListObjects and how to properly define them. I tried something similar and then resorted to trying to figure out the Excel Formula Match... which is obviously not compatible. – Bob D Oct 07 '20 at 01:56
  • Haven't fully understood Range.Find as I got so confused. Any suggestions would be apprecaited as I'm still learning – Bob D Oct 07 '20 at 01:57
  • This question tries to tackle the issue of `match`. https://stackoverflow.com/questions/21839958/match-not-working-excel-error-1004-unable-to-get-the-match-property/56481450#56481450 – pgSystemTester Oct 07 '20 at 03:07

1 Answers1

1

Something like the following:

Dim myTable As ListObject
Set myTable = ActiveSheet.ListObjects("Table1")

Dim theMatch As Variant
theMatch = Application.Match(myString, myTable.ListColumns("LookupColumn").DataBodyRange, 0)
BigBen
  • 46,229
  • 7
  • 24
  • 40