Hi,
I am trying to get the address of the following result.
I have this formula:
TextBox6.Text = Application.WorksheetFunction.VLookup(Range("M2"), Range("A2:K16"), Application.WorksheetFunction.Match(TextBox5.Text, Range("A1:K1"), 0), 0)
How can i get the cell address of the result from the above formula?
Thank you in advance
Use two match functions instead of Vlookup and Match. That way you will have the row position and column position, so you can use Range("A2:K16").cells(rowpos, colpos).Address
Rory
Hi Rorya
Thank you for the reply.
I getting errors when I tried to replace vlookup with a match. the error is "Wrong number of arguments or invalid property assignment"
Could you please help to edit the code and reply with the correct code?
Thank you so much.
Something like this:
Please Login or Register to view this content.
Hi.
That works. Thank you once again.
sorry I have another 2 questions.
1. matchrow = Application.Match(TextBox1.Text, Range("A2:A16"), 0) why do I get an error when I change the range("M2") to textbox1.Text?
2. how can I include multiple criteria? in the matchrow? i tried the following
matchrow = Application.Match(Range("M2"), Range("A2:A16"), 0) + Application.Match(Range("M3"), Range("B2:B16"), 0)
but I am getting the wrong address.
Last edited by thoufik11; 08-12-2021 at 10:35 PM.
1. What's the error?
2. Match doesn't work like that. You'd be better off with a loop if you need to match values in two columns (and that would be a separate question, IMO)
the error is "Method 'Range' of object '_Global' failed"
Try specifying the sheet for the range.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks