VBA code with IF and Vlookup formula's

[clau818]

Hello all!

First I'd like to thank everyone in advance who helps me out with this! I don't know where I'd be in life without this forum, you guys are all absolute geniuses!

So now to my questions.

I am trying to create some vba code and I'm fairly new to this. I am trying to create a code that uses a vlookup formula based on a certain condition (IF statement).

There are two parts to my code.

First, I want the code to scan all of my data and if the word "Freight" appears in column F, I want a Vlookup to be performed in the same row but in column H. In the rows where the word "Freight" does not appear, I don't want anything done because there will already be data in those cells.

Here is what the formula would look like if not done in VBA. This causes a circular reference that I would prefer not to have.

=IF($F1="Freight",VLOOKUP($E1,'Summary Invoicing'!$F:$P,11,FALSE),$H1)

In the second part of my code I want it to once again scan all of my data and if the word "Freight" appears in column F, I want a Vlookup to be performed in the same row but in column I. If the word "Freight" does not appear in column F, I want it to remain blank.

Here is what the formula would look like if not done in VBA.

=IF($F1="Freight",VLOOKUP($E1,'HJ Tracking Numbers'!$B:$F,5,FALSE)," ")


Thanks so much!

 

[James006]

Hello,

Do not hesitate to post your current code ...

Cheers

 

[Michael M]

Don't have Excel at the moment, so UNTESTED..

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = 1 To lr
    If Range("F" & r).Value = "Freight" Then
        Range("H" & r).Formula = "=VLOOKUP($E" & r & ",'Summary Invoicing'!$F:$P,11,FALSE)"
        Range("I" & r).Formula = "=VLOOKUP($E" & r & ",'HJ Tracking Numbers'!$B:$F,5,FALSE)"
    End If
Next r
End Sub

 

[clau818]

This worked perfectly! Thanks so much!

 

[clau818]

Don't have Excel at the moment, so UNTESTED..

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = 1 To lr
    If Range("F" & r).Value = "Freight" Then
        Range("H" & r).Formula = "=VLOOKUP($E" & r & ",'Summary Invoicing'!$F:$P,11,FALSE)"
        Range("I" & r).Formula = "=VLOOKUP($E" & r & ",'HJ Tracking Numbers'!$B:$F,5,FALSE)"
    End If
Next r
End Sub

Hi Michael,

Like I already said, this worked perfectly! I am trying to learn more and more so that someday I could do this all on my own so, I was wondering if you or anyone else on here can explain to me exactly what every command in this code is actually doing. Thanks again!

 

[Michael M]

Do the comments next to each line help ??
If you paste this in your module, it will still work, but also provides comments in green text

Sub MM1()
Dim lr As Long, r As Long '**********declare your variables
lr = Cells(Rows.Count, "B").End(xlUp).Row ' ************this finds the last row in column "B"
For r = 1 To lr ' **********this loops through each row from 1 to the lastrow
    If Range("F" & r).Value = "Freight" Then '*************look in column "F" on each row in the loop to see if it contains "Freight"...If it does then process the next 2 lines
        Range("H" & r).Formula = "=VLOOKUP($E" & r & ",'Summary Invoicing'!$F:$P,11,FALSE)" '**********place the formula on each row in column "H"
        Range("I" & r).Formula = "=VLOOKUP($E" & r & ",'HJ Tracking Numbers'!$B:$F,5,FALSE)" '***********place the formula on each row in column "I"
    End If '**********we have finished that if statement..move to the next line
Next r '*****************tells us to go back to the start of the loop and do the next row
End Sub

 

[clau818]

Do the comments next to each line help ??
If you paste this in your module, it will still work, but also provides comments in green text

Sub MM1()
Dim lr As Long, r As Long '**********declare your variables
lr = Cells(Rows.Count, "B").End(xlUp).Row ' ************this finds the last row in column "B"
For r = 1 To lr ' **********this loops through each row from 1 to the lastrow
    If Range("F" & r).Value = "Freight" Then '*************look in column "F" on each row in the loop to see if it contains "Freight"...If it does then process the next 2 lines
        Range("H" & r).Formula = "=VLOOKUP($E" & r & ",'Summary Invoicing'!$F:$P,11,FALSE)" '**********place the formula on each row in column "H"
        Range("I" & r).Formula = "=VLOOKUP($E" & r & ",'HJ Tracking Numbers'!$B:$F,5,FALSE)" '***********place the formula on each row in column "I"
    End If '**********we have finished that if statement..move to the next line
Next r '*****************tells us to go back to the start of the loop and do the next row
End Sub

This is excellent! Thank you so much Michael!