| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error in 2003
The code below is part of a macro that I wrote in Excel 2000.
When I run it in 2003, it errors out (Application Error) in the last row of code. Any ideas how to fix this? Could it have something to do with add-ins? Is there a way to re-write the last line to work in 2003? TIA. NumRowsTB = Range("A65536").End(xlUp).Row NumRowsMacro = ThisWorkbook.Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row NumColsMacro = ThisWorkbook.Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column ColumnID = ThisWorkbook.Sheets(2).Cells(1, NumColsMacro).Address NormalHomeRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$1" NormalColRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$B$1:" & ColumnID & "" NormalRowRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$2:$A$" & NumRowsMacro & "" Cells(1, 7) = "=IF(ISERROR(OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0))),"""",OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0)))" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Error in 2003
I suspect the either the formula is longer than 1024 characters or Excel
2003 is more demanding and wants you to use Formula on the end of Cells(1,7) -- Regards, Tom Ogilvy "Ken" wrote in message ... The code below is part of a macro that I wrote in Excel 2000. When I run it in 2003, it errors out (Application Error) in the last row of code. Any ideas how to fix this? Could it have something to do with add-ins? Is there a way to re-write the last line to work in 2003? TIA. NumRowsTB = Range("A65536").End(xlUp).Row NumRowsMacro = ThisWorkbook.Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row NumColsMacro = ThisWorkbook.Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column ColumnID = ThisWorkbook.Sheets(2).Cells(1, NumColsMacro).Address NormalHomeRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$1" NormalColRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$B$1:" & ColumnID & "" NormalRowRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$2:$A$" & NumRowsMacro & "" Cells(1, 7) = "=IF(ISERROR(OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0))),"""",OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0)))" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Error in 2003
Thanks, Tom. I saw you were "on duty" from some previous posts and hoped you
wouild respond. After more investigation I found that the macro runs okay on some machines with 2003 and not on others. Leads me to believe that 2003 was not installed the same - some machines are missing some install options? "Tom Ogilvy" wrote: I suspect the either the formula is longer than 1024 characters or Excel 2003 is more demanding and wants you to use Formula on the end of Cells(1,7) -- Regards, Tom Ogilvy "Ken" wrote in message ... The code below is part of a macro that I wrote in Excel 2000. When I run it in 2003, it errors out (Application Error) in the last row of code. Any ideas how to fix this? Could it have something to do with add-ins? Is there a way to re-write the last line to work in 2003? TIA. NumRowsTB = Range("A65536").End(xlUp).Row NumRowsMacro = ThisWorkbook.Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row NumColsMacro = ThisWorkbook.Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column ColumnID = ThisWorkbook.Sheets(2).Cells(1, NumColsMacro).Address NormalHomeRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$1" NormalColRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$B$1:" & ColumnID & "" NormalRowRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$2:$A$" & NumRowsMacro & "" Cells(1, 7) = "=IF(ISERROR(OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0))),"""",OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0)))" |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Error in 2003
You sure it doesn't have to do with differences in where the file is
installed? If the path causes the formula to be longer than 1024 characters (in R1C1 format), then it won't work. I doubt there is any install option that would cause the behavior on interpreting a formula to be different although the installation of VB is option - however, it doesn't sound like you are saying the macro won't run. -- Regards, Tom Ogilvy "Ken" wrote in message ... Thanks, Tom. I saw you were "on duty" from some previous posts and hoped you wouild respond. After more investigation I found that the macro runs okay on some machines with 2003 and not on others. Leads me to believe that 2003 was not installed the same - some machines are missing some install options? "Tom Ogilvy" wrote: I suspect the either the formula is longer than 1024 characters or Excel 2003 is more demanding and wants you to use Formula on the end of Cells(1,7) -- Regards, Tom Ogilvy "Ken" wrote in message ... The code below is part of a macro that I wrote in Excel 2000. When I run it in 2003, it errors out (Application Error) in the last row of code. Any ideas how to fix this? Could it have something to do with add-ins? Is there a way to re-write the last line to work in 2003? TIA. NumRowsTB = Range("A65536").End(xlUp).Row NumRowsMacro = ThisWorkbook.Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row NumColsMacro = ThisWorkbook.Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column ColumnID = ThisWorkbook.Sheets(2).Cells(1, NumColsMacro).Address NormalHomeRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$1" NormalColRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$B$1:" & ColumnID & "" NormalRowRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$2:$A$" & NumRowsMacro & "" Cells(1, 7) = "=IF(ISERROR(OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0))),"""",OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0)))" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Error in 2003
Ignore that. I was thinking you were using FullName rather than just name.
It would only be applicable if the workbook had different names or the sheets had different names on different computers. If the workbook might have different names, could the name have a space in it? Another thought is a setting for hiding known file exentions under folder options. As far as I know, this doesn't affect the string returned by Thisworkbook.Filename, but I don't have Excel 2003 to test with. You might check that out and see whether your formula has [MyBook1.xls] rather than [MyBook1] -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... You sure it doesn't have to do with differences in where the file is installed? If the path causes the formula to be longer than 1024 characters (in R1C1 format), then it won't work. I doubt there is any install option that would cause the behavior on interpreting a formula to be different although the installation of VB is option - however, it doesn't sound like you are saying the macro won't run. -- Regards, Tom Ogilvy "Ken" wrote in message ... Thanks, Tom. I saw you were "on duty" from some previous posts and hoped you wouild respond. After more investigation I found that the macro runs okay on some machines with 2003 and not on others. Leads me to believe that 2003 was not installed the same - some machines are missing some install options? "Tom Ogilvy" wrote: I suspect the either the formula is longer than 1024 characters or Excel 2003 is more demanding and wants you to use Formula on the end of Cells(1,7) -- Regards, Tom Ogilvy "Ken" wrote in message ... The code below is part of a macro that I wrote in Excel 2000. When I run it in 2003, it errors out (Application Error) in the last row of code. Any ideas how to fix this? Could it have something to do with add-ins? Is there a way to re-write the last line to work in 2003? TIA. NumRowsTB = Range("A65536").End(xlUp).Row NumRowsMacro = ThisWorkbook.Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row NumColsMacro = ThisWorkbook.Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column ColumnID = ThisWorkbook.Sheets(2).Cells(1, NumColsMacro).Address NormalHomeRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$1" NormalColRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$B$1:" & ColumnID & "" NormalRowRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$2:$A$" & NumRowsMacro & "" Cells(1, 7) = "=IF(ISERROR(OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0))),"""",OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0)))" |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Error in 2003
Thanks for the ideas, Tom. I will check out the file extension theory. I am
in Seattle and my problem folks are in Tampa, so it makes it a little difficult to troubleshoot since it runs okay on my machines in Seattle. The workbooks all use the same name - no spaces. Anyway, I'm at home, waiting for Santa. Best wishes for a happy holiday! If I get more info next week, should I start a new post or will you be checking back on this one? "Tom Ogilvy" wrote: Ignore that. I was thinking you were using FullName rather than just name. It would only be applicable if the workbook had different names or the sheets had different names on different computers. If the workbook might have different names, could the name have a space in it? Another thought is a setting for hiding known file exentions under folder options. As far as I know, this doesn't affect the string returned by Thisworkbook.Filename, but I don't have Excel 2003 to test with. You might check that out and see whether your formula has [MyBook1.xls] rather than [MyBook1] -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... You sure it doesn't have to do with differences in where the file is installed? If the path causes the formula to be longer than 1024 characters (in R1C1 format), then it won't work. I doubt there is any install option that would cause the behavior on interpreting a formula to be different although the installation of VB is option - however, it doesn't sound like you are saying the macro won't run. -- Regards, Tom Ogilvy "Ken" wrote in message ... Thanks, Tom. I saw you were "on duty" from some previous posts and hoped you wouild respond. After more investigation I found that the macro runs okay on some machines with 2003 and not on others. Leads me to believe that 2003 was not installed the same - some machines are missing some install options? "Tom Ogilvy" wrote: I suspect the either the formula is longer than 1024 characters or Excel 2003 is more demanding and wants you to use Formula on the end of Cells(1,7) -- Regards, Tom Ogilvy "Ken" wrote in message ... The code below is part of a macro that I wrote in Excel 2000. When I run it in 2003, it errors out (Application Error) in the last row of code. Any ideas how to fix this? Could it have something to do with add-ins? Is there a way to re-write the last line to work in 2003? TIA. NumRowsTB = Range("A65536").End(xlUp).Row NumRowsMacro = ThisWorkbook.Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row NumColsMacro = ThisWorkbook.Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column ColumnID = ThisWorkbook.Sheets(2).Cells(1, NumColsMacro).Address NormalHomeRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$1" NormalColRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$B$1:" & ColumnID & "" NormalRowRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$2:$A$" & NumRowsMacro & "" Cells(1, 7) = "=IF(ISERROR(OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0))),"""",OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0)))" |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Error in 2003
Probably best to start a new post.
Merry Christmas! -- Regard, Tom Ogilvy "Ken" wrote in message ... Thanks for the ideas, Tom. I will check out the file extension theory. I am in Seattle and my problem folks are in Tampa, so it makes it a little difficult to troubleshoot since it runs okay on my machines in Seattle. The workbooks all use the same name - no spaces. Anyway, I'm at home, waiting for Santa. Best wishes for a happy holiday! If I get more info next week, should I start a new post or will you be checking back on this one? "Tom Ogilvy" wrote: Ignore that. I was thinking you were using FullName rather than just name. It would only be applicable if the workbook had different names or the sheets had different names on different computers. If the workbook might have different names, could the name have a space in it? Another thought is a setting for hiding known file exentions under folder options. As far as I know, this doesn't affect the string returned by Thisworkbook.Filename, but I don't have Excel 2003 to test with. You might check that out and see whether your formula has [MyBook1.xls] rather than [MyBook1] -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... You sure it doesn't have to do with differences in where the file is installed? If the path causes the formula to be longer than 1024 characters (in R1C1 format), then it won't work. I doubt there is any install option that would cause the behavior on interpreting a formula to be different although the installation of VB is option - however, it doesn't sound like you are saying the macro won't run. -- Regards, Tom Ogilvy "Ken" wrote in message ... Thanks, Tom. I saw you were "on duty" from some previous posts and hoped you wouild respond. After more investigation I found that the macro runs okay on some machines with 2003 and not on others. Leads me to believe that 2003 was not installed the same - some machines are missing some install options? "Tom Ogilvy" wrote: I suspect the either the formula is longer than 1024 characters or Excel 2003 is more demanding and wants you to use Formula on the end of Cells(1,7) -- Regards, Tom Ogilvy "Ken" wrote in message ... The code below is part of a macro that I wrote in Excel 2000. When I run it in 2003, it errors out (Application Error) in the last row of code. Any ideas how to fix this? Could it have something to do with add-ins? Is there a way to re-write the last line to work in 2003? TIA. NumRowsTB = Range("A65536").End(xlUp).Row NumRowsMacro = ThisWorkbook.Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row NumColsMacro = ThisWorkbook.Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column ColumnID = ThisWorkbook.Sheets(2).Cells(1, NumColsMacro).Address NormalHomeRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$1" NormalColRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$B$1:" & ColumnID & "" NormalRowRng = "[" & ThisWorkbook.Name & "]" & ThisWorkbook.Sheets(2).Name & "!$A$2:$A$" & NumRowsMacro & "" Cells(1, 7) = "=IF(ISERROR(OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0))),"""",OFFSET(" & NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," & NormalColRng & ",0)))" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 accepting error message in macro | Excel Discussion (Misc queries) | |||
| Office Excel 2003 Macro error with .xls files | Excel Discussion (Misc queries) | |||
| 2003 macro generates compiler error on 2007 | Excel Worksheet Functions | |||
| Error in Macro 2003 | Excel Discussion (Misc queries) | |||
| Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Linear Mode
