Find and replace string in all Excel files workboo

Question

I am writing a VBA code to replace a particular string in multiple Excel files (workbooks) located in a particular directory. I tried searching on Stack Overflow and I find answer but that is related to replacing a string in a text files through a macro in Excel. The link for the same is find and replace string in a file.

My all workbooks has three worksheets each with Bloomberg formula in cell A2 :

=BDH("CBK IN Equity","BID","07-04-2015 09:00:00","07-04-2015 16:00:00")

The study requires a macro that will replace CBK in many such workbooks in a particular directory with ALBK. This is done in order to download the data for ALBK in place of CBK.

Answer 1

Use the formula property to get the string value of formula in a cell then reassign it after replacing the text.

Dim formulaString as String
formulaString = ThisWorkbook.Sheets(1).Range("A1").Formula
formulaString = Replace(formulaString,"CBK","ALBK")
ThisWorkbook.Sheets(1).Range("A1").Formula = formulaString

To go through each worksheet in your workbook

Dim wb as workbook
Dim i as integer
set wb = thisworkbook 'or whatever you choose to open here

for i = 1 to wb.worksheets.count
    wb.sheets(i).range("A1").Formula = Replace(wb.sheets(i).range("A1").Formula,"CBK","ALBK")
next i