|
Error 1004 -Application-defined or object-defined error
Hi All,
Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
Error 1004 -Application-defined or object-defined error
Andrew,
In your Do loop, rngFirstCell is not re-assigned to any cell and is therefore always pointing to A15 and causes an infinte loop. By the time you reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps, you meant to use rngCurrentCell? i.e., Do While Not IsEmpty(rngCurrentCell) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi All, Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
Error 1004 -Application-defined or object-defined error
Thanks Vergel,
Thing is that the loop works with rngFirstCell as written. But I think I cans ee where you are coming from. rngCurrentCell won't work because some cells in this column are blank. Regards Andrew "Vergel Adriano" wrote: Andrew, In your Do loop, rngFirstCell is not re-assigned to any cell and is therefore always pointing to A15 and causes an infinte loop. By the time you reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps, you meant to use rngCurrentCell? i.e., Do While Not IsEmpty(rngCurrentCell) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi All, Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
Error 1004 -Application-defined or object-defined error
Andrew,
In that case, you can use Do While Not IsEmpty(rngCurrentCell.Offset(0,-12)) that would basically be testing the cell in Column A of the same row as rngCurrentCell... Or, you can move rngFirstCell when you move rngCurrentCell if after this line Set rngCurrentCell = rngNextCell you add something like this set rngFirstCell = rngFirstCell.Offset(1,0) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Thanks Vergel, Thing is that the loop works with rngFirstCell as written. But I think I cans ee where you are coming from. rngCurrentCell won't work because some cells in this column are blank. Regards Andrew "Vergel Adriano" wrote: Andrew, In your Do loop, rngFirstCell is not re-assigned to any cell and is therefore always pointing to A15 and causes an infinte loop. By the time you reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps, you meant to use rngCurrentCell? i.e., Do While Not IsEmpty(rngCurrentCell) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi All, Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
Error 1004 -Application-defined or object-defined error
Thanks Vergel,
I had been dabbling with the Offset phrase but with little success, but will now try you great suggestion. Thanks heaps. Andrew "Andrew Glennie" wrote: Thanks Vergel, Thing is that the loop works with rngFirstCell as written. But I think I cans ee where you are coming from. rngCurrentCell won't work because some cells in this column are blank. Regards Andrew "Vergel Adriano" wrote: Andrew, In your Do loop, rngFirstCell is not re-assigned to any cell and is therefore always pointing to A15 and causes an infinte loop. By the time you reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps, you meant to use rngCurrentCell? i.e., Do While Not IsEmpty(rngCurrentCell) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi All, Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
Error 1004 -Application-defined or object-defined error
Hi Vergel,
Mmm. Your first option created the same error, while your second managed to do nothing (apart from delete the header row of the destination sheet). The first option was so logical - it should work. Got me beat at this stage. The referencing look good to you? Regards Andrew "Vergel Adriano" wrote: Andrew, In that case, you can use Do While Not IsEmpty(rngCurrentCell.Offset(0,-12)) that would basically be testing the cell in Column A of the same row as rngCurrentCell... Or, you can move rngFirstCell when you move rngCurrentCell if after this line Set rngCurrentCell = rngNextCell you add something like this set rngFirstCell = rngFirstCell.Offset(1,0) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Thanks Vergel, Thing is that the loop works with rngFirstCell as written. But I think I cans ee where you are coming from. rngCurrentCell won't work because some cells in this column are blank. Regards Andrew "Vergel Adriano" wrote: Andrew, In your Do loop, rngFirstCell is not re-assigned to any cell and is therefore always pointing to A15 and causes an infinte loop. By the time you reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps, you meant to use rngCurrentCell? i.e., Do While Not IsEmpty(rngCurrentCell) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi All, Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
Error 1004 -Application-defined or object-defined error
Andrew,
I actually tried both suggestions and they both worked for me..I can't see any possible reason why it wouldn't work.. Maybe give the code below a try. I changed your code a bit and removed the range selections. Sub Select_Level_4() Dim rngCurrentCell As Range Dim lRow As Long With Sheets("Level 4 TBA") .Range(.Range("A2"), .Range("A2").SpecialCells(xlLastCell)).Clear lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1 End With Set rngCurrentCell = Sheets("Staff Training").Range("M15") Do While Not IsEmpty(rngCurrentCell.Offset(0, -12)) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Range("A" & lRow) lRow = lRow + 1 End If Set rngCurrentCell = rngCurrentCell.Offset(1, 0) Loop End Sub -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi Vergel, Mmm. Your first option created the same error, while your second managed to do nothing (apart from delete the header row of the destination sheet). The first option was so logical - it should work. Got me beat at this stage. The referencing look good to you? Regards Andrew "Vergel Adriano" wrote: Andrew, In that case, you can use Do While Not IsEmpty(rngCurrentCell.Offset(0,-12)) that would basically be testing the cell in Column A of the same row as rngCurrentCell... Or, you can move rngFirstCell when you move rngCurrentCell if after this line Set rngCurrentCell = rngNextCell you add something like this set rngFirstCell = rngFirstCell.Offset(1,0) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Thanks Vergel, Thing is that the loop works with rngFirstCell as written. But I think I cans ee where you are coming from. rngCurrentCell won't work because some cells in this column are blank. Regards Andrew "Vergel Adriano" wrote: Andrew, In your Do loop, rngFirstCell is not re-assigned to any cell and is therefore always pointing to A15 and causes an infinte loop. By the time you reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps, you meant to use rngCurrentCell? i.e., Do While Not IsEmpty(rngCurrentCell) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi All, Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
Error 1004 -Application-defined or object-defined error
Thanks Vergel for your time and effort. I will give your suggestions a try
and like you will keep on puzzling about why the code you provided earlier won't work. Might try it on another spreadsheet in case this one corrupted in some way. Andrew "Vergel Adriano" wrote: Andrew, I actually tried both suggestions and they both worked for me..I can't see any possible reason why it wouldn't work.. Maybe give the code below a try. I changed your code a bit and removed the range selections. Sub Select_Level_4() Dim rngCurrentCell As Range Dim lRow As Long With Sheets("Level 4 TBA") .Range(.Range("A2"), .Range("A2").SpecialCells(xlLastCell)).Clear lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1 End With Set rngCurrentCell = Sheets("Staff Training").Range("M15") Do While Not IsEmpty(rngCurrentCell.Offset(0, -12)) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Range("A" & lRow) lRow = lRow + 1 End If Set rngCurrentCell = rngCurrentCell.Offset(1, 0) Loop End Sub -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi Vergel, Mmm. Your first option created the same error, while your second managed to do nothing (apart from delete the header row of the destination sheet). The first option was so logical - it should work. Got me beat at this stage. The referencing look good to you? Regards Andrew "Vergel Adriano" wrote: Andrew, In that case, you can use Do While Not IsEmpty(rngCurrentCell.Offset(0,-12)) that would basically be testing the cell in Column A of the same row as rngCurrentCell... Or, you can move rngFirstCell when you move rngCurrentCell if after this line Set rngCurrentCell = rngNextCell you add something like this set rngFirstCell = rngFirstCell.Offset(1,0) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Thanks Vergel, Thing is that the loop works with rngFirstCell as written. But I think I cans ee where you are coming from. rngCurrentCell won't work because some cells in this column are blank. Regards Andrew "Vergel Adriano" wrote: Andrew, In your Do loop, rngFirstCell is not re-assigned to any cell and is therefore always pointing to A15 and causes an infinte loop. By the time you reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps, you meant to use rngCurrentCell? i.e., Do While Not IsEmpty(rngCurrentCell) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi All, Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
Error 1004 -Application-defined or object-defined error
Hi Vergel,
Code doesn't fall over which is good, but the selector line looking for TBA doesn't select anything. The cursor goes to the line, doesn't find the TBA letters, and goes to the next step in the loop. However, if I put in the extra variable which doesn't use Offset as per the original code I posted, the loop occurs but with the error. Wonder if part of the problem is the version of Excel we are using or the Citrix platform its running on. BTW, I took the data and your code to a new spreadsheet with no change. Time to put it aside for a bit and see what emerges from my jaded Excel memory cells. Thaks for your input. Andrew "Vergel Adriano" wrote: Andrew, I actually tried both suggestions and they both worked for me..I can't see any possible reason why it wouldn't work.. Maybe give the code below a try. I changed your code a bit and removed the range selections. Sub Select_Level_4() Dim rngCurrentCell As Range Dim lRow As Long With Sheets("Level 4 TBA") .Range(.Range("A2"), .Range("A2").SpecialCells(xlLastCell)).Clear lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1 End With Set rngCurrentCell = Sheets("Staff Training").Range("M15") Do While Not IsEmpty(rngCurrentCell.Offset(0, -12)) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Range("A" & lRow) lRow = lRow + 1 End If Set rngCurrentCell = rngCurrentCell.Offset(1, 0) Loop End Sub -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi Vergel, Mmm. Your first option created the same error, while your second managed to do nothing (apart from delete the header row of the destination sheet). The first option was so logical - it should work. Got me beat at this stage. The referencing look good to you? Regards Andrew "Vergel Adriano" wrote: Andrew, In that case, you can use Do While Not IsEmpty(rngCurrentCell.Offset(0,-12)) that would basically be testing the cell in Column A of the same row as rngCurrentCell... Or, you can move rngFirstCell when you move rngCurrentCell if after this line Set rngCurrentCell = rngNextCell you add something like this set rngFirstCell = rngFirstCell.Offset(1,0) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Thanks Vergel, Thing is that the loop works with rngFirstCell as written. But I think I cans ee where you are coming from. rngCurrentCell won't work because some cells in this column are blank. Regards Andrew "Vergel Adriano" wrote: Andrew, In your Do loop, rngFirstCell is not re-assigned to any cell and is therefore always pointing to A15 and causes an infinte loop. By the time you reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps, you meant to use rngCurrentCell? i.e., Do While Not IsEmpty(rngCurrentCell) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi All, Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
Error 1004 -Application-defined or object-defined error
Hi Vergel,
Interesting weirdness. It would appear that the Offset option does not work. Whe I change the line: Do While Not IsEmpty(rngCurrentCell.Offset(0, -12)) to Set rngCurrentCell = Sheets("Staff Training").Range("A15") the code works, and promptly falls over with the same error at Set rngCurrentCell = rngCurrentCell.Offset(1, 0) So is there a reference library that should be loaded to make this work? Andrew "Vergel Adriano" wrote: Andrew, I actually tried both suggestions and they both worked for me..I can't see any possible reason why it wouldn't work.. Maybe give the code below a try. I changed your code a bit and removed the range selections. Sub Select_Level_4() Dim rngCurrentCell As Range Dim lRow As Long With Sheets("Level 4 TBA") .Range(.Range("A2"), .Range("A2").SpecialCells(xlLastCell)).Clear lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1 End With Set rngCurrentCell = Sheets("Staff Training").Range("M15") Do While Not IsEmpty(rngCurrentCell.Offset(0, -12)) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Range("A" & lRow) lRow = lRow + 1 End If Set rngCurrentCell = rngCurrentCell.Offset(1, 0) Loop End Sub -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi Vergel, Mmm. Your first option created the same error, while your second managed to do nothing (apart from delete the header row of the destination sheet). The first option was so logical - it should work. Got me beat at this stage. The referencing look good to you? Regards Andrew "Vergel Adriano" wrote: Andrew, In that case, you can use Do While Not IsEmpty(rngCurrentCell.Offset(0,-12)) that would basically be testing the cell in Column A of the same row as rngCurrentCell... Or, you can move rngFirstCell when you move rngCurrentCell if after this line Set rngCurrentCell = rngNextCell you add something like this set rngFirstCell = rngFirstCell.Offset(1,0) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Thanks Vergel, Thing is that the loop works with rngFirstCell as written. But I think I cans ee where you are coming from. rngCurrentCell won't work because some cells in this column are blank. Regards Andrew "Vergel Adriano" wrote: Andrew, In your Do loop, rngFirstCell is not re-assigned to any cell and is therefore always pointing to A15 and causes an infinte loop. By the time you reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps, you meant to use rngCurrentCell? i.e., Do While Not IsEmpty(rngCurrentCell) -- Hope that helps. Vergel Adriano "Andrew Glennie" wrote: Hi All, Here is the code. I am referencing 2 different columns because the column which is doing the selection is sometime blank so the loop needs to reference a column with data in every row. The line the error message appears on is: Set rngNextCell = rngCurrentCell.Offset(1, 0) Sub Select_Level_4() strColumnRange = "M15" str1stColumnRange = "A15" Sheets("Level 4 TBA").Activate Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Clear Range("A1").Select Sheets("Staff Training").Activate Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange) Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange) Do While Not IsEmpty(rngFirstCell) Set rngNextCell = rngCurrentCell.Offset(1, 0) If rngCurrentCell.Value = "TBA" Then rngCurrentCell.EntireRow.Copy Sheets("Level 4 TBA").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial xlPasteValues End If Set rngCurrentCell = rngNextCell Loop End Sub TIA Andrew |
| All times are GMT +1. The time now is 01:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com