Macro from a PC is not working on Mac

R

Roger R

I do not use a Mac but one of my associated does and I
have created a spreadsheet which contains some macros.
The macros work perfectly on MS Excel, but when it is
attempted on a Mac I get this error:

Run time Error "32809"
Application defined or Object defined error.

The debug shows me the function sheets("all
product").Select with the highlight on sheets.

The wierd thing is that this function works perfect on
other macros in the same sheet on the mac, the only
difference is the name "all product" which I changed many
time while experimenting on possible fixes.

Please Help!!
Roger
 
B

Bernard REY

Roger R wrote :
I do not use a Mac but one of my associated does and I
have created a spreadsheet which contains some macros.
The macros work perfectly on MS Excel, but when it is
attempted on a Mac I get this error:

Run time Error "32809"
Application defined or Object defined error.

The debug shows me the function sheets("all
product").Select with the highlight on sheets.

The wierd thing is that this function works perfect on
other macros in the same sheet on the mac, the only
difference is the name "all product" which I changed many
time while experimenting on possible fixes.
Click to expand...

It *should* work. The reason for the error may lie in the name of the sheet
or some other related point (are you sure the corresponding workbook is the
"active workbook" at the moment?) But it's sometimes more difficult to trap
the reason. Could you send a longer extract of your code?
 
R

Roger R

-----Original Message-----
Roger R wrote :


It *should* work. The reason for the error may lie in the name of the sheet
or some other related point (are you sure the corresponding workbook is the
"active workbook" at the moment?) But it's sometimes more difficult to trap
the reason. Could you send a longer extract of your code?


---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - Macintosh

***Here is the begining of the code, the first command is
Click to expand...
the one that seems to hanging up. I have tried differnt
names, I even used "1" to make it as simple as possible.
I've also included a sub that works using the exact same
command, I will mard it with ****. Now again this works
fine on my PC, but hangs up when I run it on a Mac.
.Sheets("All Product").Select
Click to expand...
Range("E7").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Database!
R8C1:R798C3,3,0)"
Range("E7").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
************This works fine, I also copied the begining
Click to expand...
command to the begining of the previous sub and it work
there also. Frustrating...

Sheets("OrderSummary").Select
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Database!
R08C1:R778C4,4,0)"
Range("Q6").Select
Selection.Copy
Range("Q6:Q76").Select
Selection.PasteSpecial Paste:=xlFormulas,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
 
B

Bernard REY

Well, it works fine here. I simply recreated a Workbook containing three
sheets, named them "All Product", "Database" and "OrderSummary", added a few
data inside in order to make the formulas work and copied the macro lines.
It runs without a problem...

I then cleaned them up a bit (Recorded Macros!) and here's what it looks
like now:

With Sheets("All Product").Range("E7")
.FormulaR1C1 = "=VLOOKUP(RC[-2],Database!R8C1:R798C3,3,0)"
.HorizontalAlignment = xlCenter
End With

With Sheets("OrderSummary")
.Range("Q6").FormulaR1C1 = "=VLOOKUP(RC[-15],Database!R08C1:R778C4,4,0)"
.Range("Q6").Copy
.Range("Q7:Q76").PasteSpecial Paste:=xlFormulas
End With

Too bad I still can't tell *why* it doesn't run in your case. As mentioned
earlier, just make sure that the "All Product" is in the Active Workbook
(and maybe check it's name once more)...


---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - Macintosh



Roger R wrote :
-----Original Message-----
Roger R wrote :


It *should* work. The reason for the error may lie in the
name of the sheet or some other related point (are you sure
the corresponding workbook is the "active workbook" at the
moment?) But it's sometimes more difficult to trap
the reason. Could you send a longer extract of your code?
Click to expand...
***Here is the begining of the code, the first command is
the one that seems to hanging up. I have tried differnt
names, I even used "1" to make it as simple as possible.
I've also included a sub that works using the exact same
command, I will mard it with ****. Now again this works
fine on my PC, but hangs up when I run it on a Mac.

.Sheets("All Product").Select
Range("E7").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Database!
R8C1:R798C3,3,0)"
Range("E7").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
************This works fine, I also copied the begining
command to the begining of the previous sub and it work
there also. Frustrating...

Sheets("OrderSummary").Select
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Database!
R08C1:R778C4,4,0)"
Range("Q6").Select
Selection.Copy
Range("Q6:Q76").Select
Selection.PasteSpecial Paste:=xlFormulas,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Click to expand...
 
R

RogerR

How exactly do I know if it's an "active workbook". I did
try a different command- Worksheet("All Product").Active,
but got the same result. the very first error I recieved
said something about not being able to get out of design
mode because it could not create "dblGloves"
library.. "Gloves" just happens to be the info in the
first cell of the range the I am using the vlookup comand
on. Does that ring any bell as to a possible problem?
Thanks for all of your help.
-----Original Message-----
Well, it works fine here. I simply recreated a Workbook containing three
sheets, named them "All Product", "Database"
Click to expand...
and "OrderSummary", added a few
data inside in order to make the formulas work and copied the macro lines.
It runs without a problem...

I then cleaned them up a bit (Recorded Macros!) and here's what it looks
like now:

With Sheets("All Product").Range("E7")
.FormulaR1C1 = "=VLOOKUP(RC[-2],Database! R8C1:R798C3,3,0)"
.HorizontalAlignment = xlCenter
End With

With Sheets("OrderSummary")
.Range("Q6").FormulaR1C1 = "=VLOOKUP(RC[-15],Database! R08C1:R778C4,4,0)"
.Range("Q6").Copy
.Range("Q7:Q76").PasteSpecial Paste:=xlFormulas
End With

Too bad I still can't tell *why* it doesn't run in your case. As mentioned
earlier, just make sure that the "All Product" is in the Active Workbook
(and maybe check it's name once more)...


---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - Macintosh



Roger R wrote :
-----Original Message-----
Roger R wrote :

I do not use a Mac but one of my associated does and I
have created a spreadsheet which contains some macros.
The macros work perfectly on MS Excel, but when it is
attempted on a Mac I get this error:

Run time Error "32809"
Application defined or Object defined error.

The debug shows me the function sheets("all
product").Select with the highlight on sheets.

The wierd thing is that this function works perfect on
other macros in the same sheet on the mac, the only
difference is the name "all product" which I changed
many time while experimenting on possible fixes.

It *should* work. The reason for the error may lie in the
name of the sheet or some other related point (are you sure
the corresponding workbook is the "active workbook" at the
moment?) But it's sometimes more difficult to trap
the reason. Could you send a longer extract of your code?
Click to expand...
***Here is the begining of the code, the first command is
the one that seems to hanging up. I have tried differnt
names, I even used "1" to make it as simple as possible.
I've also included a sub that works using the exact same
command, I will mard it with ****. Now again this works
fine on my PC, but hangs up when I run it on a Mac.

.Sheets("All Product").Select
Range("E7").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Database!
R8C1:R798C3,3,0)"
Range("E7").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
************This works fine, I also copied the begining
command to the begining of the previous sub and it work
there also. Frustrating...

Sheets("OrderSummary").Select
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Database!
R08C1:R778C4,4,0)"
Range("Q6").Select
Selection.Copy
Range("Q6:Q76").Select
Selection.PasteSpecial Paste:=xlFormulas,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Click to expand...

.
Click to expand...
 
B

Bernard REY

RogerR wrote :
How exactly do I know if it's an "active workbook".
Click to expand...

The "ActiveWorkbook" is the workbook that is frontmost currently.

So if you're running a macro and the "All Product" sheet isn't one of the
sheets in the frontmost workbook, it won't be found. You have to bring the
corresponding Workbook frontmost first.
I did try a different command- Worksheet("All Product").Active,
but got the same result.
Click to expand...

Have a look in the VBA Help, you'll find there is no "Active" Method
corresponding to the "Workbook" object. But you weren't that far: the
correct Method is "Activate". So, if "All Product" is in "MyWorkbook.xls"
and "OrderSummary" is in "MyOtherWorkbook.xls", you'll have to add the
corresponding lines:

Workbooks("MyWorkbook.xls").Activate
With Sheets("All Product").Range("E7")
.FormulaR1C1 = "=VLOOKUP(RC[-2],Database!R8C1:R798C3,3,0)"
.HorizontalAlignment = xlCenter
End With

Workbooks("MyOtherWorkbook.xls").Activate
With Sheets("OrderSummary")
.Range("Q6").FormulaR1C1 = "=VLOOKUP(RC[-15],Database!R08C1:R778C4,4,0)"
.Range("Q6").Copy
.Range("Q7:Q76").PasteSpecial Paste:=xlFormulas
End With

But this is just a guess: it is only true if these sheets are in two
separate Workbooks. Not if they both are part of the same Workbook.

The first thing to do would now still be to find out the reason why the
macro can't find the "All Product" sheet. I'm sure there lies the answer...
or at least a good clue.

the very first error I recieved
said something about not being able to get out of design
mode because it could not create "dblGloves"
library.. "Gloves" just happens to be the info in the
first cell of the range the I am using the vlookup comand
on. Does that ring any bell as to a possible problem?
Click to expand...

Sorry, but no. Something must have gotten mixed up somehow ;-)
 
Top