Trouble Converting Match Formula to VBA

[PY_]

I am having the hardest time converting the following cell formula to use it in VBA. No matter what i try or which way i write it, i keep getting the "Unable to get the Match property of the WorksheetFunction class" error.

=MATCH("Plan Week:"&H19,MasterSheet!80:80,0)


This is my latest attempt that failed:

Please Login or Register  to view this content.

[TMS]

What does the original worksheet formula return?

Regards, TMS

[Pete_UK]

Well, you have DIMmed a variable called ColumnNumber and used that in the message box, but the MATCH function assigns its value to a variable called RowNumber.

Hope this helps.

Pete

[TMS]

Sounds like it's failing before it gets to the msgbox.

You're answering the questions in the wrong order


Regards, TMS

[PY_]

The original formula returns 15 in the cell. I have made a sample spreadsheet that is basically what my real sheet has in it.


I thought i edited that RowNumber issue but i guess it didnt save on here. Its been corrected now, thanks

[PY_]

Well crud!

Now its working... this is the code that works in my sample file:

Please Login or Register  to view this content.

[TMS]

Well, one or two differences there. Maybe it didn't like RowNumber being a variant. Maybe it wasn't finding what it was looking for.

Incidentally, you don't need brackets in the msgbox.

Regards, TMS


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.