EXCEL FORMULA TO VB

[soz1967]

Hi
i have a formula which works fine in excel but i cannot get written in VB.
Presume in my limited knowledge of VB that it should be if / else if etc. The
excel formula is:-


=IF(B1995=1,VLOOKUP(A1995,'Pay
Rates'!$A$2:$H$5000,8,FALSE),IF(B1995=2,VLOOKUP(A1995,'Pay
Rates'!$A$2:$H$5000,7,FALSE),IF(B1995=3,VLOOKUP(A1995,'Pay
Rates'!$A$2:$H$5000,6,FALSE),IF(B1995=4,VLOOKUP(A1995,'Pay
Rates'!$A$2:$H$5000,5,FALSE)))))

any help would be much appreciated.

Thanks
Sarah

 

[JE McGimpsey]

Not sure why you'd want to do this in VB - easier, and FAR more
efficient to use:

=VLOOKUP(A1995,'Pay Rates'!$A$2:$H$5000,9-B1995,FALSE)

or, if error checking for B1995 is required:

=IF(AND(B1995>=1,B1995<=4),VLOOKUP(A1995,'Pay
Rates'!$A$2:$H$5000,9-B1995,FALSE))


However, "written in VB" is pretty ambiguous. One way:

Dim vResult As Variant
Dim vTarget As Variant
Dim vCol As Variant
Dim rLookup As Range

vTarget = ActiveSheet.Range("A1995").Value
vCol = ActiveSheet.Range("B1995").Value
Set rLookup = Worksheets("Pay Rates").Range("A2:H5000")
Select Case vCol
Case 1 To 4
vResult = Application.VLookup(vTarget, rLookup, _
9 - vCol, False)
Case Else
vResult = "Error"
End Select
MsgBox vResult