?
Solved

Script or Macro to change IF statements on multiple Excel Spreadsheets

Posted on 2011-09-18
3
Medium Priority
?
197 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 1000 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 1000 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

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 …
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

765 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