View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Norman Jones Norman Jones is offline
external usenet poster
 
Posts: 5,302
Default Excel 2000 vs Excel 2003

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