VBA Compare Two Columns

[MikoSDS]

Hi All,

there are many similar questions,
sorry


I'm looking for a VBA code that would:


see if any numbers on column B of source sheet are missing from Column A of Destination sheet,


then to copy the missing numbers with their values to the first available blank cell on Column A of Destination Sheet, but the copied numbers must have their values greater than 100,000.00, which are in Column C in source sheet & in Column A in source sheet the cells must be empty. If the two criteria will be met, then copy as values only the numbers from Column B & C to the Destination Sheet to the column A & B.


Currently I have the code, but I have no idea how to get the only numbers which have values greater than 100,000.00 on the right and empty cells on the left.

Code:
Sub compareCORRECT()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, nr As Long
Set sh1 = Sheet1
Set sh2 = Sheet2
lr = sh1.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh1.Range("B2:B" & lr)
For Each c In rng
If Application.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
sh2.Cells(Rows.Count, 1).End(xlUp)(2).Resize(1, 2) = c.Resize(1, 2).Value
nr = nr + 1
End If
Next
If nr > 0 Then
Beep
MsgBox "There were " & nr & " values imported"
Else
Beep
MsgBox "There were no values to import"
End If
End Sub

Source Sheet:

Column A: Column B: Column C:

C	Document	Total
1156	409530	180,700.00
	410179	11,723.76
	410383	19,000.00
2332	410587	28,000.00
	410677	177,000.00
	55007640	75,838.00
	55008189	146,024.00
	618615	85,754.00
	618646	55,000.00
	70097980	13,209.00
	70098015	12,669.48
	7062547	55,594.89
5643	7070891	13,315.00
	7077987	15,161.50
	7078073	32,837.90
	7079598	15,378.72
	7079729	31,256.80
	7080490	32,965.50
	7081674	26,269.76
	7082086	13,724.30
	7082615	14,205.00
	7080491	232,965.50

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thank you all in advance.

 

[KennyGreens]

Hi there, you could use an if statement like this:

If IsEmpty(c.Offset(0, -1)) = True And c.Offset(0, 1).Value > 100000 Then
'Insert code here
End if

 

[MikoSDS]

Hello,

Thank you! It's working now, you are great!

 

[KennyGreens]

No problem, happy I could help.

 

[MikoSDS]

Hi again,

Sorry to bother you about my question, but I can't figure it out. So the problem belongs to the difference between pivot table and normal data, I mean when I have the cells starting from A2 which are not in pivot, the code works fine. But when the data is in pivot table the code doesn't copy first cell (A2), rest like from A3 to the end of rows works good. Do you know why? Thank you in advance guys.

 

[MikoSDS]

Okay, I found solution, pivot table shows (blank) for empty values in Column A, so my statement "IsEmpty(c.Offset(0, -1)) = True" didn't allow this to copy.