C
Charles
Hi I am having a problem with the following code at the
point of Set f = .Find(myAssoc(i, 1), LookIn:=xlValue), in
excel 97 everything worked fine now I get Subsrcipt out of
range in xp.
any help will much appreciated.
Charles
Sub compareListsOut()
'
dim myAssoc,ws1,ws2,c,f
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
ws1.Activate
ReDim myAssoc(Range("A2", Range("A2").End
(xlDown)).Rows.Count, 1)
i = 1
For Each c In Range("A2", Range("A2").End(xlDown))
myAssoc(i, 1) = c.Value
'myAssoc(i, 2) = c.Offset(0, 8).Value
i = i + 1
Next c
ws2.Activate
With ws2.Range(ws2.Range("A2", Range("A2").End
(xlDown)).Address)
For i = 1 To UBound(myAssoc)
Set f = .Find(myAssoc(i, 1), LookIn:=xlValue)
If Not f Is Nothing Then
ws1.Cells(i + 1, 11) = f.Offset(0, 8).Value
If Not ws1.Cells(i + 1, 9) = f.Offset(0,
8).Value Then ws1.Cells(i + 1, 11).Interior.ColorIndex = 6
ws1.Cells(i + 1, 12) = f.Value
Else
With ws1.Cells(i + 1, 10)
.Value = "Out"
.Interior.ColorIndex = 6
End With
End If
Next i
End With
compareListsIn
End Sub
point of Set f = .Find(myAssoc(i, 1), LookIn:=xlValue), in
excel 97 everything worked fine now I get Subsrcipt out of
range in xp.
any help will much appreciated.
Charles
Sub compareListsOut()
'
dim myAssoc,ws1,ws2,c,f
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
ws1.Activate
ReDim myAssoc(Range("A2", Range("A2").End
(xlDown)).Rows.Count, 1)
i = 1
For Each c In Range("A2", Range("A2").End(xlDown))
myAssoc(i, 1) = c.Value
'myAssoc(i, 2) = c.Offset(0, 8).Value
i = i + 1
Next c
ws2.Activate
With ws2.Range(ws2.Range("A2", Range("A2").End
(xlDown)).Address)
For i = 1 To UBound(myAssoc)
Set f = .Find(myAssoc(i, 1), LookIn:=xlValue)
If Not f Is Nothing Then
ws1.Cells(i + 1, 11) = f.Offset(0, 8).Value
If Not ws1.Cells(i + 1, 9) = f.Offset(0,
8).Value Then ws1.Cells(i + 1, 11).Interior.ColorIndex = 6
ws1.Cells(i + 1, 12) = f.Value
Else
With ws1.Cells(i + 1, 10)
.Value = "Out"
.Interior.ColorIndex = 6
End With
End If
Next i
End With
compareListsIn
End Sub