PDA

View Full Version : The Replace method - A newbie needs help!



Mister_joe
12-03-2013, 03:50 AM
Hi All,
It is my first time here. I am also just starting to learn VBA. The code snippet below was copied from the Excel Help.


Sub ChangeCellFormat() ' Set the interior of cell A1 to yellow.
Range("A1").Select
Selection.Interior.ColorIndex = 36
MsgBox "The cell format for cell A1 is a yellow interior."

' Set the CellFormat object to replace yellow with green.
With Application
.FindFormat.Interior.ColorIndex = 36
.ReplaceFormat.Interior.ColorIndex = 35
End With

' Find and replace cell A1's yellow interior with green.
ActiveCell.Replace What:="", Replacement:="", lookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByRows, SearchFormat:=True, ReplaceFormat:=True

MsgBox "The cell format for cell A1 is replaced with a green interior."

End Sub


The code does not work as expected. I do not seem to understand how the resulting CellFormat object has a connection with the Replace method. Can you clarify it for me, please.

Bob Phillips
12-03-2013, 04:00 AM
It works exactly as I expected. The cell is set to a yellow(ish) fill colour, and then changes it to a green(ish) colour using FindFormat.

It is probably not a good example of FindFormat though, because that code is more easily re-written as


Sub ChangeCellFormat()
' Set the interior of cell A1 to yellow.
Range("A1").Interior.ColorIndex = 36
MsgBox "The cell format for cell A1 is a yellow interior."

' Set the CellFormat object to replace yellow with green.
Range("A1").Interior.ColorIndex = 35
MsgBox "The cell format for cell A1 is replaced with a green interior."
End Sub

The example I just found in Excel 2010 is probably better as it uses FindFormat to inform what the format of a cell is



Sub UseFindFormat()

' Establish search criteria.
With Application.FindFormat.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With

' Notify user.
With Application.FindFormat.Font
MsgBox .Name & "-" & .FontStyle & "-" & .Size & _
" font is what the search criteria is set to."
End With
End Sub

BTW, I don't recall ever using FindFormat in my code, it is hardly critical to a VBA coder.

Mister_joe
12-03-2013, 07:10 AM
Thank you Sir. If I understood you correctly, the code executed as expected - changes cell A1 color to Yellow and then to Green. I am wondering now why it is not working that way in my computer. I have tried to step through the code. It works as expected up to the line:

"ActiveCell.Replace What:="", Replacement:="", lookAt:=xlPart, MatchCase:=False, SearchOrder:=xlByRows, SearchFormat:=True, ReplaceFormat:=True"

The above line of code does not produce the required result. Although the statement Application.ReplaceFormat.Interior.ColorIndex = 35 ha set the color to green, the Replace method is not applying the color to cell A1. I am looking for the reason why the Replace method is not replacing the format at cell A1. Is there something wrong with the syntax or does the Replace method not work with objects?

Bob Phillips
12-03-2013, 07:18 AM
You say objects, do you mean a cell object here, or something else?

Mister_joe
12-03-2013, 09:03 AM
I mean a cell. Apology if I am misusing the word "object". I hear that a cell is a Range object. Sort of confusing!