Macro Error in 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)))"
 
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)
 
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 said:
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 said:
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)))"
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
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 & "]" &
Click to expand...
ThisWorkbook.Sheets(2).Name
&
"!$B$1:" & ColumnID & ""

NormalRowRng = "[" & ThisWorkbook.Name & "]" &
Click to expand...
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)))"
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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)))"
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?


:

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

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)))"
Click to expand...
Click to expand...
Click to expand...
 
Probably best to start a new post.

Merry Christmas!
--
Regard,
Tom Ogilvy

Ken said:
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 said:
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 said:
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



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?


:

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

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 &
Click to expand...
",0))),"""",OFFSET("
&
NormalHomeRng & ",MATCH(C1," & NormalRowRng & ",0),MATCH(A1," &
NormalColRng
& ",0)))"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax for Range Reference 1
Loop through cells to find dependents 1
Combining 2 Text Strings in Body of E-Mail Q 2
macro hangs if no data is found 6
Find a value in a sheet and replace it with another value if it matches 8
Error on sh.Paste sh.Range = ("A1") 4
VBA: Paste in a range 6
Snag in invoice generator. How to change the "series" 5

Back
Top