VBA comparing sheets and cells

  • Thread starter Thread starter maurice broring
  • Start date Start date
M

maurice broring

Hi All,

Can someone please help me out on the following.

two sheet are identical. (same amount of colums with same headers)
sheet 1 has statusses of orderid's (orderid's are in column A, rest of
data in column B:AE)

I run a macro that collects data from other sheets and external files
The Data is placed in an empty "statusses" sheet (sheet 2), starting
with orderid's in column A, rest in B:AE

Now I need a code that checks in column A sheet 2, starting at row 2
and searches in sheet 1 the whole column A for a identical orderid.
If found, then it copys the entire row from sheet 2 to sheet 1
(overwrites row in sheet 1 the orderid (which is the same) with all
data in de cells behind B:AE)
When ready or if not found the same orderid it checks the next row
(column A) in sheet 2.

Thank you in advance for helping me out.

Regards,
Maurice
 
Hi Maurice

This is not the most prettiest of codes, but it works for what you want.
Someone else may have a cleaner code to use.

Sub Compare()

Dim sSht As Worksheet, tSht As Worksheet
Dim i As Integer, j As Integer

For j = 2 To 100 'Resize to suit
Set tSht = Sheets("Sheet1")

For i = 2 To 100 'Resize to suit
Set sSht = Sheets("Sheet2")

If sSht.Range("A" & i) = tSht.Range("A" & j) Then
sSht.Range("A" & i).Resize(i, 30).Copy 'This copies A to AE
tSht.Activate
tSht.Range("A" & j).Select
ActiveSheet.Paste
End If
Next i
Range("A1").Select

Next j

sSht.Activate
Application.CutCopyMode = False
Range("A1").Select

End Sub


I tried to shorten the Copy/Paste to this:

sSht.Range("A" & i).Resize(i, 30).Copy (tSht.Range("A" & j))

But it did not like it and halted on it so I stepped through it
individually as above.

HTH
Mick.
 
Back
Top