Whats wrong with my code?

[KateA]

Hi,
I'm compiling a master spreadsheet that pulls values from about 75 other sheets.
I made a mistake and dont want to fix it cell by cell, so ive been trying to learn how to write a macro (trying and failing). Here's what is in the cells of the range concerned;

=SUM(IF((SOURCESHEETX!D20:D40="VAT Gtee")*(SOURCESHEETX!H20:H40="EUR"),SOURCESHEETX!L20:L40,0))+SUM(IF((SOURCESHEETX!D20:D40="VRT Gtee")*(SOURCESHEETX!H20:H40="EUR"),SOURCESHEETX!L20:L40,0))+SUM(IF((SOURCESHEETX!D20:D40="Performance Gtee")*(SOURCESHEETX!H20:H40="EUR"),SOURCESHEETX!L20:L40,0))+SUM(IF((SOURCESHEETX!D20:D40="Gtee")*(SOURCESHEETX!H20:H40="EUR"),SOURCESHEETX!L20:L40,0))+SUM(IF((SOURCESHEETX!D20:D40="Performance Guarantee")*(SOURCESHEETX!H20:H40="EUR"),SOURCESHEETX!L20:L40,0))+SUM(IF((SOURCESHEETX!D20:D40="Letter of Credit")*(SOURCESHEETX!H20:H40="EUR"),SOURCESHEETX!L20:L40,0))

The value for SourceSheetX changes for each cell in the column.
Ive been trying to replace "Gtee" with "C&E Gtee", but the macro I write just keeps deleting everything in the range...(help!)

Here it is

Sub FixMistake ()
OriginalText = MyCell.Value<o></o>
CorrectedText = Replace(OriginalText, "Gtee", "C&E Gtee", 310, 1)<o></o>
For Each MyCell In Range("R27:R80")<o></o>
MyCell.Value = CorrectedText<o></o>
Next MyCell<o></o>
End Sub

Whats wrong? Any help would be great. Thanks a mill!

 

[Norie]

Instead of Value try using Formula.

Sub FixMistake ()
    For Each MyCell In Range("R27:R80")
        OriginalText = MyCell.Formula
        CorrectedText = Replace(OriginalText, "Gtee", "C&E Gtee", 310, 1)
        MyCell.Formula = CorrectedText
    Next MyCell
End Sub

 

[KateA]

Instead of Value try using Formula.

Sub FixMistake ()
    For Each MyCell In Range("R27:R80")
        OriginalText = MyCell.Formula
        CorrectedText = Replace(OriginalText, "Gtee", "C&E Gtee", 310, 1)
        MyCell.Formula = CorrectedText
    Next MyCell
End Sub

A message box flashed up, displaying Run Time Error '1004': Application Defined or Object Defined error.
I forgot to mention that the formula I'm using needs to be entered as an array. Does that change anything?
Thank you so much for your time!

 

[JackDanIce]

Try using

MyCell.FormulaArray

, the code is unable to tell if the formula you want to enter into a cell is an array one or not.

 

[KateA]

Now the message box is telling me I'm 'unable to set the FormulaArray property of the range class. Any solutions?

Again thanks a million!