Convert excel formula into VBA coding

[Shweta]

Hi All,

I want below mentioned formula to be converted into VBA coding.


=INDEX(Sheet2!B$2:B$22,MATCH(1,IF(Sheet2!A$2:A$22=A2,IF(Sheet2!B$2:B$22>B2,1)),0))

Can someone please help me out.

I want to code this on command button.

Thanks,
Shweta

 

[Peter_SSs]

Have you tried the macro recorder for that one?

If that doesn't work for you, then:
Have you told us everything we need to know about that formula? For example ..
- where it goes (sheet & cell)
- it looks like it may be an array formula, but you haven't mentioned that.

 

[cgcamal]

Hi Shweta,

You can try EVALUATE() function to use the same formula directly, as follow:

Sub test
Var=EVALUATE("INDEX(Sheet2!B$2:B$22,MATCH(1,IF(Sheet2!A$2:A$22=A2,IF(Sheet2!B$2:B$22>B2,1)),0))")

End Sub

Hope this helps,

Regards

 

[taurean]

Refer this thread for details,
http://www.mrexcel.com/forum/showthread.php?t=573278