CODEWITHSUNDEEP
excelvba
user5836742
user5836742

Reputation: 43

IF statement including VLOOKUP

Looking for a way to do an IF cell says (this) then VLOOKUP here, IF cell says (thiselse) then VLOOKUP different area.

Might be a super obvious way to do this, so far have this:

Pretty simple but not working

Sub categoryVLOOKUP()

'IF col D says STAR then enter VLOOKUP formula into column K
'IF col D says SUN then enter other VLOOKUP formula into column K

Dim lRow As Long, lCol As Long
Dim lRow2 As Long
Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("STARSUN")
For lRow = 2 To LastRow
    If sht.Cells(lRow, 4) = "SUN" Then
        sht.Cells(lRow, 10).Formula = _
            "=VLOOKUP(A3&G3,OF_MOON!A:D, 4,0)"
    Else

    End If

    If sht.Cells(lRow, 4) = "STAR" Then
        sht.Cells(lRow, 10).Formula = _
            "=VLOOKUP(A3&G3,OFWORLD!A:D, 4,0)"
    Else

    End If
Next lRow
End Sub

Upvotes: 0

Views: 199

Answers (3)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Another way to get the result as below

Sub categoryVLOOKUP()
    Dim lRow As Long, lCol As Long
    Dim lRow2 As Long
    Dim sht As Worksheet
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
    Set sht = ThisWorkbook.Worksheets("STARSUN")
    For lRow = 2 To LastRow
        If sht.Cells(lRow, 4) = "SUN" Then
            Range("K" & lRow).Value = Application.WorksheetFunction.VLookup(Range("A" & lRow) & Range("G" & lRow), Worksheets("OF_MOON").Range("A:D"), 4, 0)
        ElseIf sht.Cells(lRow, 4) = "STAR" Then
            Range("K" & lRow).Value = Application.WorksheetFunction.VLookup(Range("A" & lRow) & Range("G" & lRow), Worksheets("OF_MOON").Range("A:D"), 4, 0)
        End If
    Next lRow
End Sub

Upvotes: 0

IIJHFII
IIJHFII

Reputation: 600

If it is getting the formula for multiple cells that is the struggle, I would recommend R1C1 formatting:

Sub categoryVLOOKUP()

'IF col D says STAR then enter VLOOKUP formula into column K
'IF col D says SUN then enter other VLOOKUP formula into column K

Dim lRow As Long, lCol As Long
Dim lRow2 As Long
Dim sht As Worksheet
Dim LastRow as long

LastRow = Cells(Rows.Count, "D").End(xlUp).Row

Set sht = ThisWorkbook.Worksheets("STARSUN")
For lRow = 2 To LastRow
    If sht.Cells(lRow, 4) = "SUN" Then
    sht.Cells(lRow, 10).FormulaR1C1 = _
    "=VLOOKUP(R[1]C[-8]&R[1]C[-1],OF_MOON!RC:RC[3], 4,0)"

     ElseIf
     If sht.Cells(lRow, 4) = "STAR" Then
     sht.Cells(lRow, 10).FormulaR1C1 = _
     "=VLOOKUP(R[1]C[-8]&R[1]C[-1],OFWORLD!RC:RC[3], 4,0)" 

End If

Next lRow
End Sub

I think this train of thought should get you started. Remember that R1C1 has to be done in reference to the active cell that the formula will go in. I may need to check the rules for referring to new sheets but again, this should get you on the right line :) hope it helps

EDIT : Also, I believe you do need to set LastRow I have added to the code

Dim LastRow as long

and

LastRow = Cells(Rows.Count, "D").End(xlUp).Row

Upvotes: 1

zaptask
zaptask

Reputation: 707

Looks like you are missing definition and value of LastRow.

Use option explicit at the beginning of your modules to enforce variable declaration. Or simply Tools -> Options -> check Require Variable Declaration. It will be done automatically.

Also I do not understand why you would even use VBA for this. Can't you just use formula

=IF(cell="SUN",1st vlookup, if(cell="STAR", 2nd vlookup,NA())

Also I suggest using INDEX + MATCH instead of VLOOKUP.

And 3rd "also": you are hardcoding the key you will be looking up for: A3&G3. Thus You will get max of 3 values from your actions: Whatever is associated with A3&G3 in OF_MOON sheet or in OFWORLD sheet or #N/A.

Upvotes: 0

Related Questions