I can use this sub on Excel 2003 (11) and it runs fine:

Sub MarkSub(CurrentSub As String)
Dim rng As Range
Dim NextEmpty As Integer

' The next line is where the error occurs
Set rng = Workbooks(1).Sheets("Progress").Cells(Rows.count, 1).End(xlUp)

NextEmpty = rng.Row + 1
Workbooks(1).Worksheets("Progress").Range("A" & NextEmpty).Value =
CurrentSub

End Sub


However, when it is run on Excel 2000 (9), it gives a error at the line I
commented above. I was told it was an out of range error.

Can anyone suggest a workaround?

TIA,
Ken

If you are not using rng for anything else you could try:

Sub MarkSub(CurrentSub As String)
Dim NextEmpty As Long

NextEmpty = Workbooks(1).Sheets("Progress"). _
Cells(Rows.Count, 1).End(xlUp).Row + 1

Workbooks(1).Worksheets("Progress"). _
Range("A" & NextEmpty).Value = CurrentSub

End Sub

Hope this helps
Rowan

"Ken Loomis" wrote:

I can use this sub on Excel 2003 (11) and it runs fine:

Sub MarkSub(CurrentSub As String)
Dim rng As Range
Dim NextEmpty As Integer

' The next line is where the error occurs
Set rng = Workbooks(1).Sheets("Progress").Cells(Rows.count, 1).End(xlUp)

NextEmpty = rng.Row + 1
Workbooks(1).Worksheets("Progress").Range("A" & NextEmpty).Value =
CurrentSub

End Sub


However, when it is run on Excel 2000 (9), it gives a error at the line I
commented above. I was told it was an out of range error.

Can anyone suggest a workaround?

TIA,
Ken

Hi Ken,

Your sub ran fine for me under xl2k, providing I changed the workbook index
or used the workbook name.

When I ran you sub, Workbooks(1) was my Personal.xls which does not have a
'Progress" sheet and, therefore. produced your error.

Maybe, on your xl2k setup, Workbooks(1) is not what you expect?

---
Regards,
Norman



"Ken Loomis" wrote in message
...
I can use this sub on Excel 2003 (11) and it runs fine:

Sub MarkSub(CurrentSub As String)
Dim rng As Range
Dim NextEmpty As Integer

' The next line is where the error occurs
Set rng = Workbooks(1).Sheets("Progress").Cells(Rows.count,
1).End(xlUp)

NextEmpty = rng.Row + 1
Workbooks(1).Worksheets("Progress").Range("A" & NextEmpty).Value =
CurrentSub

End Sub


However, when it is run on Excel 2000 (9), it gives a error at the line I
commented above. I was told it was an out of range error.

Can anyone suggest a workaround?

TIA,
Ken

Norman,

I tried a couple of different ways to reference the workbook. It is not
always the only open workbook, but on my system, if I open the workbook that
contains the "Progress" worksheet first it works fine if I reference it
with:

Set rng = Workbooks(1).Sheets("Progress").Cells(Rows.count, 1).End(xlUp)

I tried to deterimine the active workbook when I first start the macro with
this:

StreetLeadWorkbookname = ThisWorkbook.Name

'StreetLeadWorkbookname' is defined as "Public" as a String

and then use this in the MarkSub routine:

Set rng =
Workbooks(StreetLeadWorkbookname ).Sheets("Progress").Cells(Rows.count,
1).End(xlUp)

But that did not work.

What do I need to do to capture the reference to the active workbook as this
macro starts so that I can reference the "Progress" worksheet when MarkSub
first runs and when it runs later after another workbook is opened?

Thanks for your help.

Ken



"Norman Jones" wrote in message
...
Hi Ken,

Your sub ran fine for me under xl2k, providing I changed the workbook
index or used the workbook name.

When I ran you sub, Workbooks(1) was my Personal.xls which does not have a
'Progress" sheet and, therefore. produced your error.

Maybe, on your xl2k setup, Workbooks(1) is not what you expect?

---
Regards,
Norman



"Ken Loomis" wrote in message
...
I can use this sub on Excel 2003 (11) and it runs fine:

Sub MarkSub(CurrentSub As String)
Dim rng As Range
Dim NextEmpty As Integer

' The next line is where the error occurs
Set rng = Workbooks(1).Sheets("Progress").Cells(Rows.count,
1).End(xlUp)

NextEmpty = rng.Row + 1
Workbooks(1).Worksheets("Progress").Range("A" & NextEmpty).Value =
CurrentSub

End Sub


However, when it is run on Excel 2000 (9), it gives a error at the line I
commented above. I was told it was an out of range error.

Can anyone suggest a workaround?

TIA,
Ken

"Ken Loomis" wrote in message
...

I can use this sub on Excel 2003 (11) and it runs fine:

Sub MarkSub(CurrentSub As String)
Dim rng As Range
Dim NextEmpty As Integer

' The next line is where the error occurs
Set rng = Workbooks(1).Sheets("Progress").Cells(Rows.count,
1).End(xlUp)

NextEmpty = rng.Row + 1
Workbooks(1).Worksheets("Progress").Range("A" & NextEmpty). _
Value =CurrentSub

End Sub


However, when it is run on Excel 2000 (9), it gives a error at the
line I commented above. I was told it was an out of range error.

Can anyone suggest a workaround?

TIA,
Ken


Hi Ken,

If Workbooks(1) is actually the workbook containing the sub, then you
could replace it with ThisWorkbook which might make it work okay:

ThisWorkbook.Worksheets("Progress").Range("A" & NextEmpty).Value =
CurrentSub


HTH,

Alan.

Hi Ken,

It is not clear what you are doing, but would simply using the workbook's
name work (changing, 'MyBook' to your workbook name):

Sub MarkSub(CurrentSub As String)
Dim rng As Range
Dim NextEmpty As Integer
With Workbooks("MyBook").Sheets("Progress")

Set rng = .Cells(Rows.Count, 1).End(xlUp)

NextEmpty = rng.Row + 1
.Range("A" & NextEmpty).Value = CurrentSub
End With

End Sub

---
Regards,
Norman



"Ken Loomis" wrote in message
...
Norman,

I tried a couple of different ways to reference the workbook. It is not
always the only open workbook, but on my system, if I open the workbook
that
contains the "Progress" worksheet first it works fine if I reference it
with:

Set rng = Workbooks(1).Sheets("Progress").Cells(Rows.count,
1).End(xlUp)

I tried to deterimine the active workbook when I first start the macro
with
this:

StreetLeadWorkbookname = ThisWorkbook.Name

'StreetLeadWorkbookname' is defined as "Public" as a String

and then use this in the MarkSub routine:

Set rng =
Workbooks(StreetLeadWorkbookname ).Sheets("Progress").Cells(Rows.count,
1).End(xlUp)

But that did not work.

What do I need to do to capture the reference to the active workbook as
this
macro starts so that I can reference the "Progress" worksheet when MarkSub
first runs and when it runs later after another workbook is opened?

Thanks for your help.

Ken



"Norman Jones" wrote in message
...
Hi Ken,

Your sub ran fine for me under xl2k, providing I changed the workbook
index or used the workbook name.

When I ran you sub, Workbooks(1) was my Personal.xls which does not have
a 'Progress" sheet and, therefore. produced your error.

Maybe, on your xl2k setup, Workbooks(1) is not what you expect?

---
Regards,
Norman



"Ken Loomis" wrote in message
...
I can use this sub on Excel 2003 (11) and it runs fine:

Sub MarkSub(CurrentSub As String)
Dim rng As Range
Dim NextEmpty As Integer

' The next line is where the error occurs
Set rng = Workbooks(1).Sheets("Progress").Cells(Rows.count,
1).End(xlUp)

NextEmpty = rng.Row + 1
Workbooks(1).Worksheets("Progress").Range("A" & NextEmpty).Value =
CurrentSub

End Sub


However, when it is run on Excel 2000 (9), it gives a error at the line
I commented above. I was told it was an out of range error.

Can anyone suggest a workaround?

TIA,
Ken

Thanks, Alan.

That did it.

I did not realize that "ThisWorkbook" referenced the workbook that contained
the running VBA.

Since I open other workbooks while this VBA code runs, I thought I needed to
capture the workbook's name when I first opened the work book.

Ken



"Alan" wrote in message
...
"Ken Loomis" wrote in message
...

I can use this sub on Excel 2003 (11) and it runs fine:

Sub MarkSub(CurrentSub As String)
Dim rng As Range
Dim NextEmpty As Integer

' The next line is where the error occurs
Set rng = Workbooks(1).Sheets("Progress").Cells(Rows.count,
1).End(xlUp)

NextEmpty = rng.Row + 1
Workbooks(1).Worksheets("Progress").Range("A" & NextEmpty). _
Value =CurrentSub

End Sub


However, when it is run on Excel 2000 (9), it gives a error at the
line I commented above. I was told it was an out of range error.

Can anyone suggest a workaround?

TIA,
Ken


Hi Ken,

If Workbooks(1) is actually the workbook containing the sub, then you
could replace it with ThisWorkbook which might make it work okay:

ThisWorkbook.Worksheets("Progress").Range("A" & NextEmpty).Value =
CurrentSub


HTH,

Alan.