Last Column number in duplicate column headers?

[tanmayagrawal]

I have 2 tabs.
Tab 1 has 5 column headers - price ,stock ,stock ,price ,stock

tab 2 has price. Now I want to return 4 in front of price as in tab 1, last price has column number 4

So, I tried using match function but it gave me 1 instead of 4 as it gave me the first price location and not the last.

Please help

[Olly]

Use:

=SUMPRODUCT(MAX(COLUMN(Sheet1!A1:E1)*(Sheet1!A1:E1=Sheet2!A1)))

[tanmayagrawal]

Use:

=SUMPRODUCT(MAX(COLUMN(Sheet1!A1:E1)*(Sheet1!A1:E1=Sheet2!A1)))

Thanks a lot for the formula. This is not working on VBA. Can you please tell me what changes are required to be made in this to make it use in vba ?

[Olly]

If you need a VBA solution, you could use something like:

Function LastColumnMatch(ByVal FindString As String, ByRef InRange As Range)
    LastColumnMatch = InRange.Find(What:=FindString, SearchDirection:=xlPrevious).Column
End Function

[tanmayagrawal]

If you need a VBA solution, you could use something like:

Function LastColumnMatch(ByVal FindString As String, ByRef InRange As Range)
    LastColumnMatch = InRange.Find(What:=FindString, SearchDirection:=xlPrevious).Column
End Function

sir, how do I use it?
Suppose I have tab2 price(lookup value) in a1, range (tab1) in a1 to a10 and lets say I want my result in tab2 b1 range. Please suggest as to how to use it?

[Olly]

If you don't know how to apply that simple function, then why do you want a VBA solution instead of a formula?

[tanmayagrawal]

I figured it out and its working in VBA. Thanks a lot for the help.

[tanmayagrawal]

Olly, I am facing a strange problem. Today I bought one online windows server and their I used this function but this error is coming
run time error 91 - Object variable or with block variable not set

This code is working perfectly fine on my system but not working on server.
What can be the problem according to you ?

[Olly]

Post your code - or better still, attach your workbook.