Adding Condition to Vlookup VBA

[helpthenewbie]

I created a Vlookup but need to run based on a condition.

Such as

If cell =1 then
run Vlookup #1

If cell = 2 then
run Vlookup #2

There are only 4 possible variables that the cell could equal, but the entries in the worksheet would be multiple. This would have to run on a loop first seeing what the cell equaled to determine what Vlookup to run

Any ideas?

 

[Abdul Matheen]

Hi,

I am not clear about your requirement, could you send me a sample workbook with the clear requirement?

Regards
Abdul Matheen

 

[helpthenewbie]

In the Report_Draft tab Style number relates to Type number in Field_Estimate worksheet. That number would determine the vlookup run on In.Added in the Report_Draft Worksheet. Type 1 would run Vlookup on Type 1 data, Type 2 would run Vlookup on Type 2 data, and so on.

 

[helpthenewbie]

Hi,

I am not clear about your requirement, could you send me a sample workbook with the clear requirement?

Regards
Abdul Matheen

Thanks for your response. I posted the document below.

 

[dan_l]

Like the answer is to all broad vba questions, "it depends"


What's the difference between the vlookups?

Easiest way might just be a select case statement

select case cell1
case 1
vlookup(a1,somerange,2,false)
case 2
vlookup(a1,somerange,3,false)
case 3
vlookup(a1,somerange2,2,false)
case 4
vlookup(b1,somerange,3,false)
end select

 

[helpthenewbie]

Thanks dan_l I will look into that. The difference between the Vlookups are the ranges they are looking at. Column A is the condition, and column B is the value the Vlookup uses. Column A can equal 1-4. Each number is associated with a different range.

This is what I am ultimately trying for

If A1 =1
Then vlookup(b1,range1,2,false)

If A2 = 2
Then vlookup(b2,range2,2,false)

If A3= 1
Then vlookup(b3,range1,2,false)

and so on.....

I want this in a loop because the number of entries in Column A and B will vary. I could populate each cell, but eventually others will be using it. It would save time to hit a button instead of worrying about each cell has the formula. I have the loop set up for the Vlookup

 

[dan_l]

so....

select case a1
case 1
set TheRange = [range1]
case 2
set TheRange = [range2]
end select

vlookup(b1,[therange],2,false)

 

[NARAYANK991]

Hi ,

Try this :

Sub Bill_report()
'  Report to help with BNSF Billing Process
    Dim i As Integer
           
    On Error Resume Next
   
    With Worksheets("Report_Draft")
        For i = 3 To 20
            .Cells(i, 5) = Application.WorksheetFunction.VLookup(.Cells(i, 4), [pt_master].Offset(, (.Cells(i, 1) - 1) * 3).Resize(, 3), 2, False)
        Next i
    End With
End Sub

Narayan