Error 1004 -Application-defined or object-defined error

[Andrew Glennie]

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

 

[Vergel Adriano]

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)

 

[Andrew Glennie]

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]

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)

 

[Andrew Glennie]

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]

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]

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

 

[Andrew Glennie]

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

 

[Andrew Glennie]

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

 

[Andrew Glennie]

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