Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use VBA to repetitively edit sheets? (97 SR2)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use VBA to repetitively edit sheets? (97 SR2)

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

    <pre>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
    </pre>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use VBA to repetitively edit sheets? (97 SR2)

    Elegant, simple, perfect.
    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •