Hi, I am trying to sort through two workbooks (two sheets)
Workbook 1 contains info on dates, parts, & qty ..etc..
Workbook 2 contains the same....
I am trying to match the info to find any discrepancies with the data ex: dates are off by greater than 30 days, qty is way off (1 vs 10). I run into issues since there are repeats or some items are not found.
I first set up my formulas, then I also tried recording a macro but now I found myself in vba trying to code something to help me out. I attached my excel formula and actual code for reference:
Here I'm trying to match the sales numbers:
Excel:
=IF(B2="Received"," ",INDEX('C:\Users\ Desktop\[Backlog.xlsx]backlog1'!$J:$J,MATCH(A2,'C:\Users\ Desktop\[Backlog.xlsx]backlog1'!$W:$W,0)))
VBA:
I keep getting errors with my index/match formula, I still haven't even tried adding in the if statement lol
Any help is greatly appreciated!
Workbook 1 contains info on dates, parts, & qty ..etc..
Workbook 2 contains the same....
I am trying to match the info to find any discrepancies with the data ex: dates are off by greater than 30 days, qty is way off (1 vs 10). I run into issues since there are repeats or some items are not found.
I first set up my formulas, then I also tried recording a macro but now I found myself in vba trying to code something to help me out. I attached my excel formula and actual code for reference:
Here I'm trying to match the sales numbers:
Excel:
=IF(B2="Received"," ",INDEX('C:\Users\ Desktop\[Backlog.xlsx]backlog1'!$J:$J,MATCH(A2,'C:\Users\ Desktop\[Backlog.xlsx]backlog1'!$W:$W,0)))
VBA:
Code:
Dim Backlog As Workbook
Dim bcklog1 As Worksheet
Dim sales As Range
Dim PO As Range
Set Backlog = Workbooks.Open(Filename:="C:\Users\Desktop\Backlog.xlsx")
Set bcklog1 = Backlog.Worksheets("backlog1")
Set sales = bcklog1.Range("J:J")
Set PO = bcklog1.Range("W:W")
Dim TEST1 As Range
Dim test As Variant
Dim answer As Variant
Cells(2, 6).Name = "sales"
Cells(2, 7).Name = "PO"
match_formula = "Match(sales & PO, J:J&W:W, 0)"
result = Evaluate(match_formula)
'answer = Application.WorksheetFunction.Match(2, Worksheets(1).Range("W:W"), 0)
test = Application.WorksheetFunction.Index(Sheets("backlog1").Range("J:J"), result, 1)
Set TEST1 = test.Range("F2")
TEST1.AutoFill Destination:=xlFillDefault
I keep getting errors with my index/match formula, I still haven't even tried adding in the if statement lol
Any help is greatly appreciated!