Prev: Run Time Error 13- Type mismatch
Next: Excel macro security
From: soz1967 on 21 Jul 2008 09:49
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
From: JE McGimpsey on 21 Jul 2008 10:39
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



In article <62B79BD0-78B7-4C1F-A2C4-17E24F450E16(a)microsoft.com>,
soz1967 <soz1967(a)discussions.microsoft.com> wrote:

> 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
 | 
Pages: 1
Prev: Run Time Error 13- Type mismatch
Next: Excel macro security