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

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

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

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

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

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

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