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!
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


CorrectedText = Replace(OriginalText, "Gtee", "C&E Gtee", 310, 1)<o


For Each MyCell In Range("R27:R80")<o


MyCell.Value = CorrectedText<o


Next MyCell<o


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