Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL FORMULA TO VB
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(A1 995,'Pay Rates'!$A$2:$H$5000,7,FALSE),IF(B1995=3,VLOOKUP(A1 995,'Pay Rates'!$A$2:$H$5000,6,FALSE),IF(B1995=4,VLOOKUP(A1 995,'Pay Rates'!$A$2:$H$5000,5,FALSE))))) any help would be much appreciated. Thanks Sarah |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL FORMULA TO VB
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 , soz1967 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(A1 995,'Pay Rates'!$A$2:$H$5000,7,FALSE),IF(B1995=3,VLOOKUP(A1 995,'Pay Rates'!$A$2:$H$5000,6,FALSE),IF(B1995=4,VLOOKUP(A1 995,'Pay Rates'!$A$2:$H$5000,5,FALSE))))) any help would be much appreciated. Thanks Sarah |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) |