compare two sheets, with VBA


Board Regular
May 20, 2012
hi, i am having two sheets in my excel, first sheet named sales, the other one named sent,
in sheet sales i have all my data, column "B" is the Sku, and column "H" is qty.
in sheet sent, there is a report what i get every morning from Qty that got changed, So i have there in column "A" the Sku, and column "D" is the new Qty,
now i need that my sheet sales should get updated automatically from sheet sent.
i tried already alot of ways, spending weeks without success, maybe someone can help me out here, i will give some ways what i tried and where i get stuck.
Sub MyIfCode()

Dim I As Range
Dim r As Range

Dim MyUpdate As Range
Dim OldList As Range

Dim Sales As Object
Dim Sent As Object

Set Sales = Worksheets("Sales")
Set Sent = Worksheets("Sent")

Set MyUpdate = Sent.Range("A:A").UsedRange
Set OldList = Sales.Range("B:B").UsedRange

For Each I In MyUpdate
   For Each r In OldList
        If I = r Then
           r.Offset(0, 6) = I.Offset(0, 3)
        End If

     Next r

Next I

End Sub
in the above code, it get stuck here> Set MyUpdate = Sent.Range("A:A").UsedRange
Set OldList = Sales.Range("B:B").UsedRange

here is another code i tried, actuelly with the help of a mrexcel thread

here is my code,
Sub test()
Dim Rng1 As Range, Rng2 As Range, C As Range
Dim Z

With Sheets("Sent")
    Set Rng2 = Intersect(.UsedRange, .Range("A:A"))
    For Each C In Rng2
        With Sheets("Sales")
            Set Rng1 = Intersect(.UsedRange, .Range("B:B"))
            Z = Application.Match(C, Rng1, 0)
            If C = Rng1 Then
            Rng1.Offset(0, 6) = C.Offset(0, 3)
            End If
        End With
    Next C
End With
MsgBox "Done!"

End Sub
by this code i get stuck here> If C = Rng1 Then "error Type Mismatch"

can anyone help me out?
i am really sick of working on that for weeks.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...