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:
[TABLE="width: 228"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]C[/TD]
[TD]Document[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD="align: right"]1156[/TD]
[TD="align: right"]409530[/TD]
[TD] 180,700.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]410179[/TD]
[TD] 11,723.76[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]410383[/TD]
[TD] 19,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]2332[/TD]
[TD="align: right"]410587[/TD]
[TD] 28,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]410677[/TD]
[TD] 177,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]55007640[/TD]
[TD] 75,838.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]55008189[/TD]
[TD] 146,024.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]618615[/TD]
[TD] 85,754.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]618646[/TD]
[TD] 55,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]70097980[/TD]
[TD] 13,209.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]70098015[/TD]
[TD] 12,669.48[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7062547[/TD]
[TD] 55,594.89[/TD]
[/TR]
[TR]
[TD="align: right"]5643[/TD]
[TD="align: right"]7070891[/TD]
[TD] 13,315.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7077987[/TD]
[TD] 15,161.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7078073[/TD]
[TD] 32,837.90[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7079598[/TD]
[TD] 15,378.72[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7079729[/TD]
[TD] 31,256.80[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7080490[/TD]
[TD] 32,965.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7081674[/TD]
[TD] 26,269.76[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7082086[/TD]
[TD] 13,724.30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7082615[/TD]
[TD] 14,205.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7080491[/TD]
[TD] 232,965.50[/TD]
[/TR]
</tbody>[/TABLE]



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.