Add additional check

[pastorkc]

I have a VBA code that looks at spreadsheet 1 and compares to spreadsheet 2. If it finds a match it then grabs the check# from the next cell down. How do I get it to look at the cell directly below the check# to see if the check was voided and then grab the next check below that? I have attached the code I already have.

' Assuming worksheet 1 is the first worksheet and worksheet 2 is the second worksheet. Comparing data between sheets.
    Set ws1 = ThisWorkbook.Worksheets(1)
    Set ws2 = ThisWorkbook.Worksheets(2)
    
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow1 ' Assuming data starts from row 2
        For j = 2 To lastRow2 ' Assuming data starts from row 2
            If ws1.Cells(i, "A").Value = ws2.Cells(j, "A").Value And _
               ws1.Cells(i, "F").Value = ws2.Cells(j, "C").Value Then
               ws1.Cells(i, "J").Value = ws2.Cells(j + 1, "E").Value ' Value from worksheet 2 column E, down one cell
            Exit For ' No need to continue checking if a match is found
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

 

[Joe4]

Please post samples of your two data sheets, and show us a situation in which you are describing (I think it may be clearer to us if we can see what your data looks like).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[pastorkc]

Transit Invoice Sheet.xlsm
	A	B	C	D
156	21802	WAKM AM-950	Marketing and Advertising	350.00
GL Transit Report

Transit Invoice Sheet.xlsm
	A	B	C	D	E	F	G
495	WAKM	WAKM AM-950	137-00002-0217	Invoice	137-00002-0217	7/31/23	700.00
496	WAKM	WAKM AM-950		Payment	21802	8/7/23	-700.00
497	WAKM	WAKM AM-950		Payment	21802V	8/7/23	700.00
498	WAKM	WAKM AM-950		Payment	21803	8/7/23	-350.00
499	WAKM	WAKM AM-950		Payment	0896	8/7/23	-350.00
500							0.00
501	WAKM						0.00
Vendor Transaction History

If you notice the top is sheet 1 and it found the matching Invoice on sheet 2 and grabbed the check number, but the check was then voided and replaced but it didn't see that and just placed the first check number. I hope this helps.

 

[Joe4]

Your images do not seem to match your code.

You seem to be comparing column A from Sheet1 to column A of Sheet2
and column F from Sheet1 to column C of Sheet2.

But in your images, your column A values do not match at all, so I don't see how that would work.
Also, you are not showing column F from Sheet1 in your image, so we have no idea of what is in there.

 

[pastorkc]

Oh yes, because later in the code I have to rearrange the columns to match the format the end person wants. Let me upload the version before that part of the code hits.

 

[pastorkc]

Transit Invoice Sheet.xlsm
	A	B	C	D	E	F	G
766
767	WAKM	WAKM AM-950	137-00002-0217	Invoice	137-00002-0217	7/31/23	700.00
768	WAKM	WAKM AM-950		Payment	21802	8/7/23	-700.00
769	WAKM	WAKM AM-950		Payment	21802V	8/7/23	700.00
770	WAKM	WAKM AM-950		Payment	21803	8/7/23	-350.00
771	WAKM	WAKM AM-950		Payment	0896	8/7/23	-350.00
Vendor Transaction History

Transit Invoice Sheet.xlsm
	A	B	C	D	E	F	G
468	WAKM	WAKM AM-950	7/31/23	5090.14.T20.T.G	Radio Advertising/Web	137-00002-0217	350.00
469				5090.14.T20.T.G	Radio Advertising/Web		350.00
GL Transit Report

Here it is in raw form before any of the macro has ran.

 

[Joe4]

See if this works. It seems to worked on the sample you posted:

' Assuming worksheet 1 is the first worksheet and worksheet 2 is the second worksheet. Comparing data between sheets.
    Set ws1 = ThisWorkbook.Worksheets(1)
    Set ws2 = ThisWorkbook.Worksheets(2)
   
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
   
    For i = 2 To lastRow1 ' Assuming data starts from row 2
        For j = 2 To lastRow2 ' Assuming data starts from row 2
            If ws1.Cells(i, "A").Value = ws2.Cells(j, "A").Value And _
               ws1.Cells(i, "F").Value = ws2.Cells(j, "C").Value Then
'              Check to see if 2 rows below contains a "V" for void
               If Right(ws2.Cells(j + 2, "E"), 1) = "V" Then
'                  Pull value from three rows down
                   ws1.Cells(i, "J").Value = ws2.Cells(j + 3, "E").Value
               Else
'                  Pull value from one row down
                   ws1.Cells(i, "J").Value = ws2.Cells(j + 1, "E").Value ' Value from worksheet 2 column E, down one cell
               End If
            Exit For ' No need to continue checking if a match is found
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

 

[pastorkc]

See if this works. It seems to worked on the sample you posted:

' Assuming worksheet 1 is the first worksheet and worksheet 2 is the second worksheet. Comparing data between sheets.
    Set ws1 = ThisWorkbook.Worksheets(1)
    Set ws2 = ThisWorkbook.Worksheets(2)
  
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
  
    For i = 2 To lastRow1 ' Assuming data starts from row 2
        For j = 2 To lastRow2 ' Assuming data starts from row 2
            If ws1.Cells(i, "A").Value = ws2.Cells(j, "A").Value And _
               ws1.Cells(i, "F").Value = ws2.Cells(j, "C").Value Then
'              Check to see if 2 rows below contains a "V" for void
               If Right(ws2.Cells(j + 2, "E"), 1) = "V" Then
'                  Pull value from three rows down
                   ws1.Cells(i, "J").Value = ws2.Cells(j + 3, "E").Value
               Else
'                  Pull value from one row down
                   ws1.Cells(i, "J").Value = ws2.Cells(j + 1, "E").Value ' Value from worksheet 2 column E, down one cell
               End If
            Exit For ' No need to continue checking if a match is found
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

Worked perfect, thank you

 

[Joe4]

You are welcome.
Glad I was able to help!