Macro Help

  • Thread starter Thread starter johnfli
  • Start date Start date
J

johnfli

I turn to you guru's for help with a macro question.

I have to worksheets in my workbook.

Both have a coulmn that has a 6 digit number in each cell (an invoice
number)

What I need to do is have a macro that starts with the first invoice # in
sheet 1, searches for it in sheet 2.
If it finds it, delete that row from Sheet 2.
Then it will look at the 2nd invoice number on sheet 1, Search from it in
Sheet 2
If it finds it, delete that row from Sheet 2.

etc, etc, etc

so, my question to you is.....

How?
 
Modify this to suit.

Sub Deletematches()
On Error Resume Next
For Each cel In [c14:c15]
With Worksheets("sheet5").Cells
Set c = .Find(cel, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Delete
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub
 
Be aware that Don's macro will delete every instance of the invoice number
from the second sheet. You stated that you wanted to delete just the first
instance of it and then move on to the second number in the first sheet.
However, I think you want exactly what Don's macro does. HTH Otto
Don Guillett said:
Modify this to suit.

Sub Deletematches()
On Error Resume Next
For Each cel In [c14:c15]
With Worksheets("sheet5").Cells
Set c = .Find(cel, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Delete
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub


johnfli said:
I turn to you guru's for help with a macro question.

I have to worksheets in my workbook.

Both have a coulmn that has a 6 digit number in each cell (an invoice
number)

What I need to do is have a macro that starts with the first invoice # in
sheet 1, searches for it in sheet 2.
If it finds it, delete that row from Sheet 2.
Then it will look at the 2nd invoice number on sheet 1, Search from it in
Sheet 2
If it finds it, delete that row from Sheet 2.

etc, etc, etc

so, my question to you is.....

How?
Click to expand...
Click to expand...
 
Isn't it unlikely that there would be more than one invoice with the same
number?

Otto Moehrbach said:
Be aware that Don's macro will delete every instance of the invoice number
from the second sheet. You stated that you wanted to delete just the first
instance of it and then move on to the second number in the first sheet.
However, I think you want exactly what Don's macro does. HTH Otto
Don Guillett said:
Modify this to suit.

Sub Deletematches()
On Error Resume Next
For Each cel In [c14:c15]
With Worksheets("sheet5").Cells
Set c = .Find(cel, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Delete
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub


johnfli said:
I turn to you guru's for help with a macro question.

I have to worksheets in my workbook.

Both have a coulmn that has a 6 digit number in each cell (an invoice
number)

What I need to do is have a macro that starts with the first invoice # in
sheet 1, searches for it in sheet 2.
If it finds it, delete that row from Sheet 2.
Then it will look at the 2nd invoice number on sheet 1, Search from it in
Sheet 2
If it finds it, delete that row from Sheet 2.

etc, etc, etc

so, my question to you is.....

How?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top