If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
running a Macro in 2007 which was created in 2003
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? |
#2
|
|||
|
|||
running a Macro in 2007 which was created in 2003
Simon,
That line is fine in E2007 provided you have 'SET' Master_Wksht but if you hadn't done that it wouldn't have run in earlier versions of Excel Set Master_Wksht = Sheets("Sheet1") What have you dimmed LastRow as it should be long. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "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? |
#3
|
|||
|
|||
running a Macro in 2007 which was created in 2003
Hmm,
I forgot the obvious question. What is the error message? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "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? |
#4
|
|||
|
|||
running a Macro in 2007 which was created in 2003
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? |
#5
|
|||
|
|||
running a Macro in 2007 which was created in 2003
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? |
#6
|
|||
|
|||
running a Macro in 2007 which was created in 2003
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 |
#7
|
|||
|
|||
running a Macro in 2007 which was created in 2003
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? |
#8
|
|||
|
|||
running a Macro in 2007 which was created in 2003
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? |
#9
|
|||
|
|||
running a Macro in 2007 which was created in 2003
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? . |
Thread Tools | |
Display Modes | |
|
|