offset and address?
 

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

offset and address?
 

I think MyListRange.Rows(res).Row


On 29 Oct 2004 12:09:53 -0700, (MJ) wrote:

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