Solved

Script or Macro to change IF statements on multiple Excel Spreadsheets

Posted on 2011-09-18
3
194 Views
Last Modified: 2012-05-12
Hi,

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.
Thanks

Matt
0
Comment
Question by:Mattywerts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

Assisted Solution

by:Robert Schutt
Robert Schutt earned 250 total points
ID: 36559687
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
          Next
        Next
      Next
      ' uncomment the following line after you're satified with the results
      'objExcel.Workbooks(objFile.Name).Save ' maybe SaveAs?
      objExcel.Workbooks(objFile.Name).Close
    End If
  End If
Next
objExcel.Quit
Set objExcel = Nothing
Set objFolder = Nothing
Set objFSO = Nothing

Open in new window

0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 250 total points
ID: 36559804
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
    wbk.Save
    wbk.Close
    strFile = Dir$()
Loop
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:Mattywerts
ID: 36571109
Thanks helped me out heaps !!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question