Prev: Replacing carriage returns with a comma
Next: request for a solution on shared documents
From: Dave Peterson on 22 Apr 2010 08:05
This is a guess since you didn't say what error occurred.

xl2007 can open older versions of files in compatibility mode -- where the
number of rows is 64k (not 1 meg).

So depending on what workbook/worksheet is active, that unqualified Rows.count
could be 64k or 1Meg.

I'd qualify that range:

with Master_Wksht
lastrow = .range("N" & .rows.count).end(xlup).row
end with



Simon wrote:
>
> Hi there, I hope you can help, I am not VB literate unfortunately but I am
> trying to run a macro with the following line of code which creates an error:
>
> LastRow = Master_Wksht.Range("N" & Rows.Count).End(xlup).Row
>
> Is there a change between versions which means this line won't work any more?

--

Dave Peterson
From: Jon Peltier on 22 Apr 2010 12:20
If the code fails when run at full speed but works when stepped through,
all else being equal, it usually means Excel has some housekeeping to
do, that it hasn't finished before the command.

Inserting a one-word line

DoEvents

before the line that shows the error will often give Excel a chance to
do all that background stuff. Excel 2007 is more prone to this issue
than 2003.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/22/2010 5:25 AM, Kevryl wrote:
> Hi Simon, you've got me, being new to 2007 too. No doubt someone will soon
> give you a definitive answer soon, but until then...
>
> I had a problem running a macro that tripped on the line,
> "Windows("Accounting.xls").Activate" which was merely to switch between 2
> windows in the one instance of Excel. It tripped the first time giving an
> error message, so I went into debugging, stepping over all the commands up to
> that one and then "stepped into" from there. It found its way through without
> an issue and then worked perfectly after that. So its possible that if you do
> the same, yours too may "settle in" (OK, some Excel geek can ridicule the
> terminology, but it worked ;-). )
>
> Remember that any commands before the one that tripped will have been
> executed, so you may not want them to execute again! Turn on the "debug"
> toolbar and use the "step over" and "step into" buttons. Be aware that the
> "Continue" button (right-pointing triangle) will run from the point you're at
> right to the end.
>
> "Simon" wrote:
>
>> Hi there, I hope you can help, I am not VB literate unfortunately but I am
>> trying to run a macro with the following line of code which creates an error:
>>
>> LastRow = Master_Wksht.Range("N"& Rows.Count).End(xlup).Row
>>
>> Is there a change between versions which means this line won't work any more?
>>
>>
>>
From: Kevryl on 23 Apr 2010 00:30
G'day Mike,

I do understand your skepticism, but I've had similar where something has
fouled up the first time but then "settled in". No, I can't explain it.

I'm still using the .xls format on these 2 files (save as 97-2003) as I'm
not totally convinced yet that I want to stay with 2007. I did save an
inventory file as .xlsm because from what I've read I thought I had to to
preserve the macros, but that.s misleading because my macros work ok in .xls.

I was about to save the accounting files as a .xlsm too, but I re-thought
that quickly because it wanted me to put an undersore in front of my
range-names - a couple of hundred of them, many referred to in macros of
which I also have dozens.

My decision now is whether to (A) stay with Win 7 Office 2007 and weather
it, (hoping Office 2010 will address all the slow-down issues), or (B) roll
back to XP & Office 2000, or (C) start a tedious switch to Linux (prob
Ubuntu?) and Open Office.org and eventually become Microsoft independant.

Any thoughts on these choices would be appreciated. :-)



"Mike H" wrote:

> Kevryl,
>
> I think it highly unlikely a macro would 'settle in' and learn to live with
> an error. You note you were working in 2007 and called another workbook using
>
> Windows("Accounting.xls").Activate
>
> If it was a 2007 workbook then that line will fail because of an incorrect
> file extension which should be
>
> Windows("Accounting.xlsx").Activate
>
> or another appropriate extension for the type of 2007 workbook
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Kevryl" wrote:
>
> > Hi Simon, you've got me, being new to 2007 too. No doubt someone will soon
> > give you a definitive answer soon, but until then...
> >
> > I had a problem running a macro that tripped on the line,
> > "Windows("Accounting.xls").Activate" which was merely to switch between 2
> > windows in the one instance of Excel. It tripped the first time giving an
> > error message, so I went into debugging, stepping over all the commands up to
> > that one and then "stepped into" from there. It found its way through without
> > an issue and then worked perfectly after that. So its possible that if you do
> > the same, yours too may "settle in" (OK, some Excel geek can ridicule the
> > terminology, but it worked ;-). )
> >
> > Remember that any commands before the one that tripped will have been
> > executed, so you may not want them to execute again! Turn on the "debug"
> > toolbar and use the "step over" and "step into" buttons. Be aware that the
> > "Continue" button (right-pointing triangle) will run from the point you're at
> > right to the end.
> >
> > "Simon" wrote:
> >
> > > Hi there, I hope you can help, I am not VB literate unfortunately but I am
> > > trying to run a macro with the following line of code which creates an error:
> > >
> > > LastRow = Master_Wksht.Range("N" & Rows.Count).End(xlup).Row
> > >
> > > Is there a change between versions which means this line won't work any more?
> > >
> > >
> > >
From: Kevryl on 23 Apr 2010 00:31
Jon, thanks I'll try that next time I have a stall. :-) DoEvents.... right
got it.

"Jon Peltier" wrote:

> If the code fails when run at full speed but works when stepped through,
> all else being equal, it usually means Excel has some housekeeping to
> do, that it hasn't finished before the command.
>
> Inserting a one-word line
>
> DoEvents
>
> before the line that shows the error will often give Excel a chance to
> do all that background stuff. Excel 2007 is more prone to this issue
> than 2003.
>
> - Jon
> -------
> Jon Peltier
> Peltier Technical Services, Inc.
> http://peltiertech.com/
>
>
> On 4/22/2010 5:25 AM, Kevryl wrote:
> > Hi Simon, you've got me, being new to 2007 too. No doubt someone will soon
> > give you a definitive answer soon, but until then...
> >
> > I had a problem running a macro that tripped on the line,
> > "Windows("Accounting.xls").Activate" which was merely to switch between 2
> > windows in the one instance of Excel. It tripped the first time giving an
> > error message, so I went into debugging, stepping over all the commands up to
> > that one and then "stepped into" from there. It found its way through without
> > an issue and then worked perfectly after that. So its possible that if you do
> > the same, yours too may "settle in" (OK, some Excel geek can ridicule the
> > terminology, but it worked ;-). )
> >
> > Remember that any commands before the one that tripped will have been
> > executed, so you may not want them to execute again! Turn on the "debug"
> > toolbar and use the "step over" and "step into" buttons. Be aware that the
> > "Continue" button (right-pointing triangle) will run from the point you're at
> > right to the end.
> >
> > "Simon" wrote:
> >
> >> Hi there, I hope you can help, I am not VB literate unfortunately but I am
> >> trying to run a macro with the following line of code which creates an error:
> >>
> >> LastRow = Master_Wksht.Range("N"& Rows.Count).End(xlup).Row
> >>
> >> Is there a change between versions which means this line won't work any more?
> >>
> >>
> >>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Replacing carriage returns with a comma
Next: request for a solution on shared documents