Solved

Script or Macro to change IF statements on multiple Excel Spreadsheets

Posted on 2011-09-18
3
192 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
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

776 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