Find matching cell values in Workbook 1 (Column A) and Workbook 2 (Column A); Paste corresponding data

Question

I am trying to make a macro that takes a dollar amount and percentage (2 separate columns) for a certain month's report, and add it to a historical workbook showing all of a project's dollar values/percents in the past. The code below appears to work, but actually is just pasting the next row value from the monthly report (wb1) into the next row in the historical workbook's next empty column (wb2). I need it to actually match the project names in wb1 with the project names in wb2, so that the new values actually are coming from the right project. I know it isn't working because I took out a project name to see what would happen, and the macro still posted the missing project name's information in wb2, cutting off the last value at the end of the list of projects when there weren't any more occupied cells. So, if there are 10 projects and I take out project 5, the data is posted for projects 1-9.

I also will need to add a new row if the project name in wb1 doesn't appear in wb2's column A. The new row would be for the missing project name, and would paste the dollar amount for that month. Or, at the very least, tell the user that a particular project name isn't present in wb2. I'm not sure exactly how I'll go about doing this, but I at least need the code below to add project values accurately.

Any help would be greatly appreciated!


Workbooks.Open ("T:\ADMINISTRATION\Marie Presley\HistoricalFees.xlsx")

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim y As Integer
Dim sht As Worksheet

'=============================================
Dim w1 As Worksheet, w2 As Worksheet
 Dim i As Long, j As Long, n As Integer
Dim NextEmptyCol As Long

 Set w1 = Workbooks("Forecast Summary Report Generator.xlsm").Worksheets("Forecast Summary")
 Set w2 = Workbooks("HistoricalFees.xlsx").Worksheets("Sheet1")

NextEmptyCol = w2.Cells(1, Columns.Count).End(xlToLeft).Column + 1

 n = 0
 For i = 1 To w2.Cells(Rows.Count, 1).End(xlUp).Row       'for each used cell in w2.colA
   For j = 1 To w1.Cells(Rows.Count, 3).End(xlUp).Row + n 'for each used cell in w1.colC

    'Find the text from w1.colC (current w1 row), within cell in w2.colA (current w2 row)
     If InStr(1, w2.Cells(i, 1).Value, w1.Cells(j, 3).Value) > 0 Then

      'If found then copy cell in w2.colB into cell in w2.colE (current w2 row)
       w1.Cells(i, 8).Copy (w2.Cells(i, NextEmptyCol))
       w1.Cells(i, 9).Copy (w2.Cells(i, (NextEmptyCol + 1)))

       Exit For    'this exits the inner For loop

       n = n + 1   'this would jump over the next cell(s) in w1, but never executes
     End If
   Next j
 Next i

End Sub

What's your question, can you clarify? Also, if you step through the code with F8, it should help troubleshoot why n = n + 1 never fies. I'm assuming it's because it's always exiting the For loop? Also, could this possibly be a formula instead? — BruceWayne
I agree with @BruceWayne, try moving n = n +1 before your Exit For and then step through the code using F8 to make sure that the value of n is increasing by 1. — Zack E
I guess my question ultimately is, why is this code not actually searching for a matching cell value? It's just pasting data in the next empty row whether it's the right project name or not. I'm pretty new to using vba and I used this written code and modified it to work for me, I'm not actually sure what the comment for n=n+1 means. I don't know if this could be a formula instead... maybe with vlookups. I could play around with that too. — Marie Presley
If you use F8 to step through the code, it'll go line-by-line. Then you can see when/where it exits a loop, and what values are...for instance after the for i = 1 ... line fires, hover your mouse over the i, and you'll see a tooltip that shows 1. — BruceWayne

Peyter Peyter · Accepted Answer · 2020-01-13T17:53:57

I know it sounds bad, but taking Zac's suggestion, see if you can make this algorithm first with VLookup and Find worksheet functions. Even if you need to use helper sheets to handle multiple steps. It would be better if you actually knew and then saw what you wanted to do, working, before embarking on an adaption of someone else's code.

Then next try using Application.worksheetfunction to replicate the working visual sheet formulas.

After you've got this working you should be in a better place to understand the flow, and you can simplify it down into ranges and sheet objects. But until you know what the actual algorithm is or understand it, you're going to be struggling to understand what is going wrong.

Find matching cell values in Workbook 1 (Column A) and Workbook 2 (Column A); Paste corresponding data

2 Answers