Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro change problem | Excel Discussion (Misc queries) | |||
CHANGE MARCO PROBLEM | Excel Discussion (Misc queries) | |||
Name change save problem | Excel Discussion (Misc queries) | |||
Worksheet Change by Value problem | Excel Discussion (Misc queries) | |||
Worksheet Change Sub problem | Excel Programming |