Solved

Script or Macro to change IF statements on multiple Excel Spreadsheets

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
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…
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now