CODEWITHSUNDEEP
excelvbareplace
In777
In777

Reputation: 181

Convert the cell to the formula with replace

I have an Excel file with several sheets in it. In each "A1"-cell I saved my formulas as a text, in the format:

%=TR('Sheet 1'!C1;'Sheet 1'!$F$1:$F$5;"Frq=D SDate=#1 EDate=#2 Curn=EUR CH=Fd";$B$1;'Sheet 1'!$D$1;'Sheet 1'!$E$1)

I saved them in such format on purpose, since the formulas allow to connect via Add-In (xlam) to the database (Thomson Reuters Eikon) and are used to retrieve the data from this database. If I have them all in the file without %-character, they are exercised at once and file crashes. So I want to write VBA which deletes the character (%) in each sheet one by one, hence waits some 1 or 2 minutes until the formula in the previous sheet has retrieved the data. I tried several ways but none of them worked.

First, I tried to replace the character with this code:

Dim SearchValue As String
Dim NewValue As String

SearchValue = "%"
NewValue = ""

Selection.Replace What:=SearchValue, Replacement:=NewValue,      
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

and this code:

Range("B1").Replace "%", ""

But it only goes if I call the find-and-replace box and press the button manually.

Application.Dialogs(xlDialogFormulaReplace).Show "%", ""

How can exercise find-and-replace box with a macro?

Besides, I tried to generate formula with adding "=" to TR('Sheet 1'!C1;'Sheet 1'!$F$1:$F$5;"Frq=D SDate=#1 EDate=#2 Curn=EUR CH=Fd";$B$1;'Sheet 1'!$D$1;'Sheet 1'!$E$1) like this:

myformula = Cells(1, 1).Value
Range("B1").Select
ActiveCell.Formula = "=" & myformula

I failed either...

Is there some other possibility to replace the % character?

Upvotes: 1

Views: 408

Answers (1)

user4039065
user4039065

Reputation:

Write the replacement into the Range.Formula property.

with selection.cells(1)
    .formula = replace(.value, chr(37), vbnullstring)
end with

With Sheets("Sheet1").Cells(1, 1)
    .Formula = Replace(.Value, Chr(37), vbNullString)
End With

'for non-US-EN systems you need to use comma list separators with .Formula
With Sheets("Sheet1").Cells(1, 1)
    .Formula = Replace(Replace(.Value, Chr(37), vbNullString), Chr(59), Chr(44))
End With

'for non-US-EN systems you can also use .FormulaLocal
With Sheets("Sheet1").Cells(1, 1)
    .FormulaLocal = Replace(.Value, Chr(37), vbNullString)
End With

VBA formulas setting is very US-EN centric; it expects that you are using a formula on a EN-US system. You can change the formula list separators from semi-colons to commas or use the Range.FormulaLocal property to accomadate the semi-colon list separators.

Upvotes: 1

Related Questions