Start Cell "A2"

[josros60]

Hi,
have the code below but when executed the values are put at the bottom starting at cell "A71" how can make to start at the top starting on cell "A2".


here is the code:

Sub FIND_DIFFERENCES()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim C As Range, f As Range, mydiffs As Long
  Dim r1 As Range, r2 As Range, lr As Long
  
  Set sh1 = Sheets("NCL_INVOICES")
  Set sh2 = Sheets("VENDOR_INVOICES")
  Set sh3 = Sheets("DIFFERENCES")
  
  Set r1 = sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
  Set r2 = sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
 
  r1.Interior.Color = vbWhite
  r2.Interior.Color = vbWhite
  
  For Each C In r1
    Set f = r2.Find(C, , xlValues, xlWhole)
    If f Is Nothing Then
      C.Interior.Color = vbRed
      mydiffs = mydiffs + 1
      lr = sh3.Range("A" & Rows.Count).End(xlUp).Row + 1
      sh3.Range("A" & lr).Resize(1, 6).Value = C.Resize(1, 6).Value
      sh3.Range("A" & lr).Offset(, 6).Value = sh1.Name
     
      
      
      
    End If
  Next
  For Each C In r2
    Set f = r1.Find(C, , xlValues, xlWhole)
    If f Is Nothing Then
      C.Interior.Color = vbRed
      mydiffs = mydiffs + 1
      lr = sh3.Range("A" & Rows.Count).End(xlUp).Row + 1
      sh3.Range("A" & lr).Resize(1, 6).Value = C.Resize(1, 6).Value
      sh3.Range("A" & lr).Offset(, 6).Value = sh2.Name
      
    End If
  Next
  MsgBox mydiffs & " differences found"
End Sub

thank you

 

[Fluff]

Should it always start in A2 even if there is data already there?

 

[josros60]

There's no data there, always clear cause is template.

should Always start A2.

thank you

 

[Fluff]

If that was the case then your code would start putting values in A2 & not A71.
I would check that those cells are indeed empty.

 

[josros60]

here a sample top empty and start at 21:

VENDOR_RECONCILIATION_TEMPALTE.xltm
	A	B	C	D	E	F	G	H
1	INVOICE #	DATE	ENTRY	NCL_AMT	REFERENCE	VEND_AMT	TABS	PAID
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21	56721	11/9/2021	1362-16	$467.51			NCL_INVOICES
22	57565	1/13/2022	1467-7	$249.38			NCL_INVOICES
23	57791	2/1/2022	1498-7	$374.06			NCL_INVOICES
24	57812	4/6/2022	1604-2	$149.63	MOVED FROM SAGEID 1003		NCL_INVOICES
25	58193	3/1/2022		$4,310.89			VENDOR_INVOICES
26	58194	3/1/2022		$2,324.07			VENDOR_INVOICES
27	58594	3/1/2022		$157.50			VENDOR_INVOICES
28	58782	4/7/2022		$462.26			VENDOR_INVOICES
29	58856	4/13/2022		$289.26			VENDOR_INVOICES
30	58857	4/13/2022		$778.84			VENDOR_INVOICES
DIFFERENCES

thank you

 

[Fluff]

Is that a structured table or just a normal range?

 

[josros60]

it's a table.

 

[Fluff]

So you have a table with loads of totally blank rows? Why, it defeats the whole point of a table.

 

[josros60]

Yes, that's was the problem converted back to range and it worked.

thank you so much for the input.

 

[Fluff]

You're welcome & thanks for the feedback.