M
MJ
Hi,
I do a match on something and it returns the offset. Is there a way
to determine the exact address so that I can delete that row?
Thanks!!!
Here is the code:
Sub MatchData()
Dim MyList1Range As Range
Dim MyList2Range As Range
Dim res As Variant
ThisWorkbook.Sheets("Sheet2").Activate
Range("A2").CurrentRegion.Select
MaxRow1 = Selection.Rows.Count
Set MyList1Range = Range("a2:A" & MaxRow1)
ThisWorkbook.Sheets("Sheet1").Activate
Range("A2").CurrentRegion.Select
MaxRow2 = Selection.Rows.Count
Set MyList2Range = Range("a2:A" & MaxRow2)
Range("A2").Select
Do While ActiveCell().Value <> "" 'From Sheet1
res = Application.Match(ActiveCell().Value, MyList1Range, 0) 'match
from Sheet2
If IsError(res) Then
'there is no match - append the cell value into Sheet2 - still need
to do
Else
'there is a match - delete the row because the id was already on
Sheet2
'this returns the offset
MsgBox "Match offset into range is: " & res
End If
ActiveCell().Offset(1, 0).Select
Loop
End Sub
I do a match on something and it returns the offset. Is there a way
to determine the exact address so that I can delete that row?
Thanks!!!
Here is the code:
Sub MatchData()
Dim MyList1Range As Range
Dim MyList2Range As Range
Dim res As Variant
ThisWorkbook.Sheets("Sheet2").Activate
Range("A2").CurrentRegion.Select
MaxRow1 = Selection.Rows.Count
Set MyList1Range = Range("a2:A" & MaxRow1)
ThisWorkbook.Sheets("Sheet1").Activate
Range("A2").CurrentRegion.Select
MaxRow2 = Selection.Rows.Count
Set MyList2Range = Range("a2:A" & MaxRow2)
Range("A2").Select
Do While ActiveCell().Value <> "" 'From Sheet1
res = Application.Match(ActiveCell().Value, MyList1Range, 0) 'match
from Sheet2
If IsError(res) Then
'there is no match - append the cell value into Sheet2 - still need
to do
Else
'there is a match - delete the row because the id was already on
Sheet2
'this returns the offset
MsgBox "Match offset into range is: " & res
End If
ActiveCell().Offset(1, 0).Select
Loop
End Sub