4

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.

Community
  • 1
  • 1
gaurav kumar
  • 859
  • 2
  • 10
  • 24

1 Answers1

3

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
tells
  • 630
  • 4
  • 11
  • many thanks for the help. I am doing it for a workbook. Dim wb As Workbook Dim wsh As Worksheet Dim formulaString As String For Each wsh In wb.Worksheets formulaString = wb.Worksheets.Range("A1").Formula formulaString = Replace(formulaString, "CBK", "ALBK") wb.Worksheets.Range("A1").Formula = formulaString Next wsh ..This code is giving error. Please help in correcting the code. – gaurav kumar Sep 23 '15 at 16:59
  • Right. So you can iterate to your worksheets in the workbook. Use `for i = 1 to wb.worksheets.count` to go through each worksheet and use `wb.sheets(i).range('your range here').Formula` - Don't forget to accept the answer if you've found this to be your solution – tells Sep 23 '15 at 17:04
  • Dear now the code is browsing all the files and giving NO Errors but my hard luck that still the code is unable to replace CBK with ALBK. – gaurav kumar Sep 23 '15 at 17:11
  • Many thanks for the answer as it started working Now. That range was A2 instead of A1. :) – gaurav kumar Sep 23 '15 at 17:23
  • ah right.. didn't give you the A2 reference in the example. – tells Sep 23 '15 at 17:27