• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Script or Macro to change IF statements on multiple Excel Spreadsheets


We have multiple spreadsheets containing data. Each Spreadsheet is configured in the same format  i.e columns are the same. Some of the cells contain IF statements.
Upon analysis, we have found that some of these IF statements require correction. Is there anyway I can edit these if statements across multiple spreadsheets with out having to open each work book and edit it?

Any pointers would be appreciated.

2 Solutions
Robert SchuttSoftware EngineerCommented:
This should get you started:

Option Explicit
Dim objFSO, objFolder, objFile, objExcel
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(".")
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = 0
objExcel.Visible = False
For Each objFile In objFolder.Files
  Dim p
  p = InStrRev(objFile.Name, ".")
  If p > 0 Then
    If Mid(objFile.Name, p+1, 3) = "xls" Then
      WScript.Echo "Workbook: " & objFile.Name
      objExcel.Workbooks.Open Replace(WScript.ScriptFullName, WScript.ScriptName, objFile.Name), False, True ' remove this if you want to save
      Dim sh
      For Each sh In objExcel.Workbooks(objFile.Name).Sheets
        WScript.Echo "Sheet: " & sh.Name
        Dim rw
        For rw = 1 To sh.UsedRange.Rows.Count
          WScript.Echo "Row: " & rw
          Dim cl
          For cl = 1 To sh.UsedRange.Rows(rw).Columns.Count
            WScript.Echo "Cell: " & cl
            If sh.UsedRange.Cells(rw, cl).HasFormula Then
              WScript.Echo "* formula found: " & sh.UsedRange.Cells(rw, cl).Formula ' or: .FormulaR1C1
            End If
      ' uncomment the following line after you're satified with the results
      'objExcel.Workbooks(objFile.Name).Save ' maybe SaveAs?
    End If
  End If
Set objExcel = Nothing
Set objFolder = Nothing
Set objFSO = Nothing

Open in new window

This macro will open (sorry, must be done) each Workbook in a given folder, and set the formula of a particular cell on each sheet of the workbook.

Sub BatchUpdate()
Dim strFile As String
Dim strFolder As String
Dim wbk As Workbook
Dim wks As Worksheet

strFolder = "C:\MyWorkbooks"
strFile = Dir$(strFolder & "*.xls*")
Do Until strFile = ""
    Set wbk = Workbooks.Open(strFolder & strFile)
    For Each wks In wbk.Worksheets
        wks.Cells(21, 5).Formula = "IF(D21 > 100,""Over"",""OK"")"
    Next wks
    strFile = Dir$()
End Sub

Open in new window

MattywertsAuthor Commented:
Thanks helped me out heaps !!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now