Dynamic Vlookup for automating forecast summary

[khalari808]

Hello,

I have two sheets. Source and Target sheets. Need to extract data from source sheet to match the forecast by month and PO number on the Target sheet in its respective PO and Month Row. However, there are a few conditions for the vlookup, I want to skip any row in the target sheet, if the Column C does not equal "PO Materials" or "PO Labor". After each lookup, I want to copy and paste the value in the target sheet cell to avoid too much vlookup codes that slow the file.

Here is the code I have so far.

Sub NB_Run_Forecast_Upload()

Dim rng1 As Range  'Source Sheet this will set the range in which you want this formula to appear
Dim cl1 As Range
Dim rng2 As Range  'Output Sheet
Dim cl2 As Range    'Output Sheet
Dim rng3 As Range  'Outsheet Range for If Then Statement. Col C must have either "PO Labor" or "PO Materials" to execute Vlookup otherwise skip

Dim strFormula1 as String  `string to hold the formula text
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim DataValidation As Worksheet

Set sourceSheet = Worksheets("Source")
Set outputSheet = Worksheets("Target")
Set DataValidation = Worksheets("Data Validation")

Set rng1 = sourceSheet.Range("I5:AB1339")  'Range hardcoded; need it to go to end
Set rng2 = outputSheet.Range("G1:R5000")    'Range hardcoded; need it to go to the end of rng3
Set rng3 = output.Sheet.Range("C1:C5000")  'Range for If Then statement

'nothing happens in sourceSheet.  it is basically, the area where information is stored for vlookup

On Error Resume Next
With sourceSheet  'this might be a double declaration as rng1 does declare
    SourceLastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
End With

With outputSheet


'if statement to check if Col C contains either "PO Labor" or "PO Materials"
For Each cl1 In rng2 'my translation:  for each cell in rng2 perform the below

If rng3.Value = "PO Materials" Then  'i would prefer to add OR statement to add "PO Labor" reduce redundancy

cl2.Forumla = MyLookupFormula

Else

If rng3.Value = "PO Labor" Then

c2.Forumla = MyLookupFormula

End If
Next rng2 'next col same row until after same row Col R it goes down the row in the outputsheet
End With



Function Colindexnum() As Integer  'i coded the Col number referenec for each month in the Outputsheet that corresponds to the same month in the Sourcesheet
                                    'it's similar to =vlookup(A1, A2:C2, ColIndexNum,0) ColIndexNum changes to each month, its constant in the outputsheet but changes in the sourcesheet
                                    'because every time period a month is deleted.  final range is till Dec

Colindexnum = (Application.WorksheetFunction.VLookup(outputSheet.Range("G3:R3"), DataValidation.Range("H30:I41"), 2, False))

End Function

Function MyLookupFormula() As Variant

If Not IsError(Application.WorksheetFunction.VLookup(outputSheet.Range("E:E"), rng1, Colindexnum, False)) Then
MyLookupFormula = (Application.WorksheetFunction.VLookup(outputSheetRange("E:E"), rng1, Colindexnum, False))


Else: MyLookupFormula = vbNullString
End Function

'after each lookup I want to copy and paste the cell it looked up to avoid too much coding Rng2
With outputSheet

For Each rng2 In .UsedRange
    If rng2.Formula Like "*VLOOKUP*" Then rng2.Formula = rng2.Value
    Next rng2
End With

End Sub

Any assistance will be greatly appreciated.