"Find next" problem in macros

[sramsay]

Hi,

I have a problem using "find next" in macros that I create on my mac.
The problem is that opening the find dialog and choosing "Search by
columns" before selecting "find next" does not retain the columns
option in the actual macro. (I know the Windoze version doesn't have
this problem). The problem really comes up when you re-open a
workbook: if you don't remember to open up the find dialog and manually
choose "Search by columns" the macro will search by rows.

This is the code that is created on my Mac:

Range("J7").Select
Cells.FindNext(After:=ActiveCell).Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

and this is a similar example from Windoze:

Range("J10").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

Both of these are intended to do exacty the same thing: find the next
empty cell down a column in a table and paste in a value. The second
example works fine on the Mac, so I don't understand why the code comes
out differently on the two platforms when I go through exactly the same
keystrokes when creating the macro.

Is there any way around this problem without going into the macro
editor and manually entering the extra conditions in the program
itself?

Thanks in advance.

Scott Ramsay

 

[Bob Greenblatt]

Hi,

I have a problem using "find next" in macros that I create on my mac.
The problem is that opening the find dialog and choosing "Search by
columns" before selecting "find next" does not retain the columns
option in the actual macro. (I know the Windoze version doesn't have
this problem). The problem really comes up when you re-open a
workbook: if you don't remember to open up the find dialog and manually
choose "Search by columns" the macro will search by rows.

This is the code that is created on my Mac:

Range("J7").Select
Cells.FindNext(After:=ActiveCell).Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

and this is a similar example from Windoze:

Range("J10").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

Both of these are intended to do exacty the same thing: find the next
empty cell down a column in a table and paste in a value. The second
example works fine on the Mac, so I don't understand why the code comes
out differently on the two platforms when I go through exactly the same
keystrokes when creating the macro.

Is there any way around this problem without going into the macro
editor and manually entering the extra conditions in the program
itself?

Thanks in advance.

Scott Ramsay

The short answer is no. You should rely on the recorder only to provide a
"guide" for the actual code. It is ALWAYS best to specify all parameters in
the macro code. The Macintosh will record this correctly if you select the
by columns dropdown.

Among the many differences between platforms the assumed defaults in some of
the dialog boxes are some of the most insidious. That's why it's best to
specify the parameters.