Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 9
With the workbook open I get error at noted spots. I can
get rid of error at iListCount by making it equal to 129. Also what about "x" not being Dim? Thanks Rick Sub Compare2ListDeleteDupItems() Dim iListCount As Integer '????, x As Variant Dim iCtr As Integer 'Turn off screen upgrading to speedup maco Application.ScreenUpdating = False 'Get count of records to search through (list that will be deleted) iListCount = Sheets("Sheet2").Range("A2:A130").Rows.Count 'Causes Run Time Error '9' <<Subscript out of range 'Loop through the MASTER list For Each x In Sheets("Sheet1").Range("A2:A130") 'Causes Run Time Error '9'<<Subscript out of range 'Loop through all records in second list For iCtr = 1 To iListCount 'Do comparison of next record 'To specify a different column, change 1 to the column number If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then 'If match is true then delete row Sheets("Sheet2").Cells(iCtr, 1).Delete x1Shiftup 'Increment counter to account for deleted row iCtr = iCtr + 1 End If Next iCtr Next Appliication.ScreenUpdating = True MsgBox "DONE" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error 9
Do you have any sheets called Sheet1 and Sheet2?
Are you sure the correct workbook is active? Try qualifying with the workbook object. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "RickM" wrote in message ... With the workbook open I get error at noted spots. I can get rid of error at iListCount by making it equal to 129. Also what about "x" not being Dim? Thanks Rick Sub Compare2ListDeleteDupItems() Dim iListCount As Integer '????, x As Variant Dim iCtr As Integer 'Turn off screen upgrading to speedup maco Application.ScreenUpdating = False 'Get count of records to search through (list that will be deleted) iListCount = Sheets("Sheet2").Range("A2:A130").Rows.Count 'Causes Run Time Error '9' <<Subscript out of range 'Loop through the MASTER list For Each x In Sheets("Sheet1").Range("A2:A130") 'Causes Run Time Error '9'<<Subscript out of range 'Loop through all records in second list For iCtr = 1 To iListCount 'Do comparison of next record 'To specify a different column, change 1 to the column number If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then 'If match is true then delete row Sheets("Sheet2").Cells(iCtr, 1).Delete x1Shiftup 'Increment counter to account for deleted row iCtr = iCtr + 1 End If Next iCtr Next Appliication.ScreenUpdating = True MsgBox "DONE" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
Run-time error '11' & Run-time error '1004' | Excel Programming |