problem with change from excel 97 to xp
 

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

problem with change from excel 97 to xp
 

Try

For i = 1 To UBound(myAssoc,1)

rather than

For i = 1 To UBound(myAssoc)

Otherwise, monitor the value of i and see what is causing it to go out of
bounds.

--
Regards,
Tom Ogilvy


"Charles" wrote in message
...
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

problem with change from excel 97 to xp
 

I have tried that but still get the error I have checked
out the help file and everything looks as it should so I
am at a loss
-----Original Message-----
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

.

problem with change from excel 97 to xp
 

I missed the second dimension problem.

Use

ReDim myAssoc(1 To Range("A2", Range("A2").End(xlDown)).Rows.Count, 1 To 2)

rather than

ReDim myAssoc(Range("A2", Range("A2").End(xlDown)).Rows.Count, 1)


This worked for me.

--
Regards,
Tom Ogilvy


wrote in message
...
I have tried that but still get the error I have checked
out the help file and everything looks as it should so I
am at a loss
-----Original Message-----
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

.