Results 1 to 5 of 5
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Working with multi-sheet selection in VBA (2000+)

    Surely I'm missing something: I have a group of identically laid out sheets by products, and within each a series of what I'll call component sets by rows. I need to add the same double multiplier to the same 32 cells in 12 of the 20 sheets. But the formula refernce in the same cell in each different sheet has a different source, so I can't edit them manually in multisheet select mode as it sets the formula references in all sheets to that of the active cell.

    This works, but why so long winded, and why the need to activate each sheet?

    Sub expandformula()
    Dim rngSheetSel As Range, rngCell As Range
    Dim wksSheet As Worksheet
    For Each wksSheet In ActiveWindow.SelectedSheets
    wksSheet.Activate
    For Each rngCell In ActiveWindow.Selection.Areas
    With rngCell
    .FormulaR1C1 = .FormulaR1C1 & "*(1+R25C+R26C)"
    End With
    Next rngCell
    Next wksSheet
    End Sub

    I tried this but it only works on the active sheet:

    With Application.Selection.Cells
    .FormulaR1C1 = .FormulaR1C1 & "*(1+R25C+R26C)"
    End With

    There must be a simpler way. Anyone?
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Working with multi-sheet selection in VBA (2000+)

    The problem is that Selection is only valid for the active sheet. Does the code below do what you want?

    <code>
    Sub expandformula()
    Dim rngSheetSel As Range, rngCell As Range, rngArea As Range
    Dim wksSheet As Worksheet
    For Each wksSheet In ActiveWindow.SelectedSheets
    For Each rngArea In ActiveWindow.Selection.Areas
    For Each rngCell In rngArea
    With wksSheet.Range(rngCell.Address)
    .FormulaR1C1 = .FormulaR1C1 & "*(1+R25C+R26C)"
    End With
    Next rngCell
    Next rngArea
    Next wksSheet
    End Sub
    </code>
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Working with multi-sheet selection in VBA (2000+)

    Legare, thanks for looking at this. Your code corrects my mishandling of .Areas. However, when I tested further, I found that it's still necessary to activate each worksheet: if the code -doesn't- activate each sheet, -only- the active sheet gets operated on, as many times as there are selected sheets, so my formulas end up like this sample:

    ='Other_Sheet'!R[-52]C[-1]*(1+R25C+R26C)*(1+R25C+R26C)*(1+R25C+R26C)*(1+R25C +R26C)*(1+R25C+R26C)*(1+R25C+R26C)*(1+R25C+R26C)*( 1+R25C+R26C)*(1+R25C+R26C)*(1+R25C+R26C)*(1+R25C+R 26C)*(1+R25C+R26C)

    So here's where I am, it works as desired, and at this point I guess it's the best I can do.

    Sub expandformula()
    ' generic formula replacer - must edit the .FormulaR1C1 line to suit
    ' select cells and sheets to be edited before running macro
    Dim rngArea As Range, rngCell As Range
    Dim wksSheet As Worksheet

    Application.ScreenUpdating = False
    For Each wksSheet In ActiveWindow.SelectedSheets
    wksSheet.Activate
    For Each rngArea In ActiveWindow.Selection.Areas
    For Each rngCell In rngArea
    With rngCell
    .FormulaR1C1 = .FormulaR1C1 & "*(1+R25C+R26C)"
    End With
    Next rngCell
    Next rngArea
    Next wksSheet
    Application.ScreenUpdating = True
    End Sub

    As a code template for multisheet multicell editing, it should save me some time in the future.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Working with multi-sheet selection in VBA (2000+)

    Change your With statement so that it looks like the one in my code, and you should not have to activate each worksheet.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Working with multi-sheet selection in VBA (2000+)

    <P ID="edit" class=small>(Edited by JohnBF on 10-Jun-07 09:41. )</P>Got it, thanks. I may run some timing tests between the two methods, see if one is significantly faster.

    Edit: OK, been there, done that, I turned of recalc to ensure that it doesn't mess up the results (though the test workbooks were identical), in my test Activating each sheet is 12.5% slower.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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