Use VBA to repetitively edit sheets? (97 SR2)

Topic

New Reply

We have a spreadsheet containing 56 worksheets. Each worksheet is used to define similar devices, so their structure is identical, and their data is similar. The print range is defined in every sheet and is the limit of data in the sheet.
I have been asked to make some global changes to the spreadsheet. How can I write some VBA which loops through every worksheet and, within the print range, tests for a small number of conditions and then alters cells as a result?

Something like:
for each worksheet in spreadsheet
for each row in the print range do
‘ first change
if column A = “Radio Status” and column D = “Power” then column W =”##.#”
‘ next change
if column A = “Alarm Setpoint” then column R = 0 and column S = 9999
‘ next change
‘etc

This seems simple enough, but I haven’t done much VBA in Excel (lots in Access, and a bit in Word)

thanks in advance

Reply | Quote

Replies

The code below should give you a start at what you want to do:

Public Sub MakeChanges()
Dim oSheet As Worksheet
Dim I As Long, lRows As Long
    For Each oSheet In Worksheets
        For I = 0 To Range(oSheet.Name & "!Print_Area").Rows.Count
            If oSheet.Range("A1").Offset(I, 0).Value = "Radio Status" And _
              oSheet.Range("D1").Offset(I, 0).Value = "Power" Then
                oSheet.Range("W1").Offset(I, 0).Value = "##.#"
            End If
            If oSheet.Range("A1").Offset(I, 0).Value = "Alarm Setpoint" And _
              oSheet.Range("R1").Offset(I, 0).Value = 0 Then
                oSheet.Range("S1").Offset(I, 0).Value = 0
            End If
        Next I
    Next oSheet
End Sub

Reply | Quote

Elegant, simple, perfect.
Thank you

Reply | Quote