Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
find and replace issue in macro
Hello,
I have a macro in Excel 2007 where I do a copy from one spreadsheet file and special paste to another sheet in another file. This works fine and it was created by recording a macro and then doing some minor modifications. I wanted to then do a find and replace where I make certain rows bold and a different color, so I recorded another macro to see what it did so I could insert the relevant code into my original macro. I did that but the results when using the macro change the size of the text, making it smaller. If I then go into format cells and the text tab everything looks fine. I then click on the font size to another size and then immediately back to the original size it was already on and click ok and it rights itself. I am wondering though why this has to be done to begin with - i.e. why does the font size change? Here's the macro: Selection.Copy Windows("finances_2008.xlsx").Activate Sheets("Spending").Select ActiveCell.Offset(0, -1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With Application.ReplaceFormat.Font .FontStyle = "Bold" .Subscript = False .ThemeColor = 4 .TintAndShade = -0.249977111 End With Selection.Replace What:="AMEX", Replacement:="AMEX", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True ActiveCell.Offset(0, 5).Select Any help would be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
find and replace issue in macro
This is only a guess, but make sure that ShrinkToFit is set to false.
-- Gary''s Student - gsnu2007k "ben" wrote: Hello, I have a macro in Excel 2007 where I do a copy from one spreadsheet file and special paste to another sheet in another file. This works fine and it was created by recording a macro and then doing some minor modifications. I wanted to then do a find and replace where I make certain rows bold and a different color, so I recorded another macro to see what it did so I could insert the relevant code into my original macro. I did that but the results when using the macro change the size of the text, making it smaller. If I then go into format cells and the text tab everything looks fine. I then click on the font size to another size and then immediately back to the original size it was already on and click ok and it rights itself. I am wondering though why this has to be done to begin with - i.e. why does the font size change? Here's the macro: Selection.Copy Windows("finances_2008.xlsx").Activate Sheets("Spending").Select ActiveCell.Offset(0, -1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With Application.ReplaceFormat.Font .FontStyle = "Bold" .Subscript = False .ThemeColor = 4 .TintAndShade = -0.249977111 End With Selection.Replace What:="AMEX", Replacement:="AMEX", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=True ActiveCell.Offset(0, 5).Select Any help would be appreciated. Thanks. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
find and replace issue in macro
I checked after running the macro and the shrintofit box was unchecked.
When I change ReplaceFormat:=True to ReplaceFormat:=False, it doesn't happen but then I also lose the formatting. Gary''s Student wrote: This is only a guess, but make sure that ShrinkToFit is set to false. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Find & Replace | Excel Worksheet Functions | |||
Macro: Find and replace | Excel Discussion (Misc queries) | |||
Find & Replace in VB macro | Excel Discussion (Misc queries) | |||
Excel find and replace issue | Excel Discussion (Misc queries) | |||
Excel find and replace issue | Excel Discussion (Misc queries) |