IF Then Vlookup Multiple Sheet

[khalari808]

Hi, Need help.

I found this great code that works; however, I need to modify it a little to fit my needs.
How can I include an IF then condition. If target sheet Column C contains a text string ("Labor" or "Materials") then it performs the Vlookup otherwise skips to the next cell.

Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet

'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("Sheet2")

'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet

    'Determine last row in col P
  OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
  .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With
End Sub

 

[Luke M]

Looks like you can change the formula line to be:

.Range("Q2:Q" & OutputLastRow).Formula = _
        "=IF(ISTEXT(C2),"",VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0))"

 

[khalari808]

Thanks Luke,

Alternative is.

Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim X As Long

'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("Sheet2")


'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet

    'Determine last row in col C
   OutputLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
   
    For X = 2 To OutputLastRow
    If InStr(1, .Range("C" & X), "PO Materials") + InStr(1, .Range("C" & X), "PO Labor") > 0 Then
        'Apply  formula
   .Range("Q" & X).Formula = _
    "=VLOOKUP(E" & X & ",'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
    End If
    Next
End With
End Sub

Luke, would you possibly know if its possible to make the Range of Column Q to go from Q till AB. In addition, the vlookup formula Col_Index_Num will increase by plus one. So for Q its set for 2, for R it should be 3.

 

[Luke M]

Sure thing. We can add in the COLUMNS function to increment the count.

.Range("Q" & x & ":AB" & x).Formula = _
    "=VLOOKUP(E" & x & ",'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",COLUMNS($A$1:B$1),0)"

 

[khalari808]

Nice. Col E needs to be static so changed to the following plus changed COLUMN Function to MATCH function.

.Range("Q" & X & ":AB" & X).Formula = _
    "=VLOOKUP($E" & X & ",'" & sourceSheet.Name & "'!$A$2:$L$" & SourceLastRow & ",Match(Q$1,'" & sourceSheet.Name & "'!$A$1:$AD$1,0),0)"

So I put the $in front of E. Added Match function instead of Column function to Match Cols between Source and Output.

Now to put the final nail in the coffin. How can I modify the execution of the vlookup based upon a specific value. See file attached. I want it to execute the Vlookup only if the output sheet (sheet 2) cell (Q2 to AB2) contains "Forecast" otherwise skip column if labeled "Actual" in the relative cell.

Finally I want to copy and paste any cells in the Column Q to AB that contain the vlookup forumla. I believe this can be accomplished using the String function.

 

[khalari808]

Got the columns to work. only thing left is to copy and paste so that there are no vlookup that bog the sheet. Any chance that after each cell is vlookedup I can copy and paste the value and move to the next cell in the range to vlookup.

Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim X As Long
Dim Z As Long

'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("Sheet2")


'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet

    'Determine last row in col C
  OutputLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Y = 17 To 28 'Q to AB
  For X = 2 To OutputLastRow
  If InStr(1, .Range("C" & X), "PO Materials") + InStr(1, .Range("C" & X), "PO Labor") > 0 And Cells(2, Y) = "Forecast" Then
  'Apply  formula
  .Cells(X, Y).Formula = _
  "=VLOOKUP($E" & X & ",'" & sourceSheet.Name & "'!$A$2:$L$" & SourceLastRow & ",Match(" & Cells(1, Y).Address & ",'" & sourceSheet.Name & "'!$A$1:$AD$1,0),0)"
  End If
  Next
Next

End With
End Sub

 

[jeffreyweir]

Why use a macro at all? WHy not just use a VLOOKUP with an IF statement:
=IF(OR(NOT(ISERROR(FIND("Materials",C3))),NOT(ISERROR(FIND("Labor",C3)))), SomeVLOOKUPFormula,"")

Or why not just use a PivotTable, and filter it on the terms 'Materials' and 'Labor'?

 

[khalari808]

Cant vlookup for 10,000+ cells because it causes excel to crash and becomes unmanageable. Pivots are good alternative but they accomplish the subsequent tasks that i need to run after completing this macro.

 

[Luke M]

Jeff makes a good point, but if you have to stick with the macro, we can skip the step of putting in formula, and use the macro to evaluate directly:

.Cells(x, y).Value = _
  Evaluate("=VLOOKUP($E" & x & ",'" & sourceSheet.Name & "'!$A$2:$L$" & SourceLastRow & ",Match(" & Cells(1, y).Address & ",'" & sourceSheet.Name & "'!$A$1:$AD$1,0),0)")

 

[khalari808]

Kick Ass. Thanks Luke