Results 1 to 9 of 9
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    fixing formulas that are off by 4 rows

    Hi

    I have an Excel2010 file with master data sheet "Stock" (headings in row1, 20 data columns, ~3000 rows)
    The file has ~1000 worksheets, each of which has many formulas that 'point' to the "Stock" sheet.
    These formulas are pointing to the correct columns on the "Stock" sheet, but the rows are off by 4.
    So, for example, on one of the sheets I might have
    =Stock!D841
    ..but it should actually be
    =Stock!D845
    and
    =Stock!B888 should actually be =Stock!B892 etc etc etc.

    Is there an easy way to process and update each sheet to 'fix' these formulas?

    zeddy

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    But I suspect you were looking for a way outside of VBA

    Code:
    Public Sub CorrectFormula()
    '---------------------------
    'DECLARE AND SET VARIABLES
    Dim cf As String, A As Integer
    Dim rng As Range, cell As Range
    '---------------------------
    'CYCLE THROUG SHEETS EXCEPT SHEET 1 (STOCK)
    For I = 2 To Worksheets.Count
        Set rng = Worksheets(I).UsedRange
    '---------------------------
    'CYCLE THROUGH CELLS WITH FORMULAS
        For Each cell In rng
            If cell.HasFormula Then
                cf = cell.Formula
                s = Split(cf, "!")
                If s(0) = "=Stock" Then
    '---------------------------
    'FIND ROW NUMBER IN FORMULA AND INCREMENT BY 4
                    For J = 1 To Len(s(1))
                        If IsNumeric(Mid(s(1), J, 1)) Then Exit For
                    Next J
                    Str1 = Mid(s(1), 1, J - 1)
                    Str2 = Val(Mid(s(1), J, Len(s(1)) - Len(Str1))) + 4
                    x = s(0) & "!" & Str1 & Str2
                End If
            End If
            cell.Formula = x
        Next cell
    Next I
    End Sub
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2016-05-14)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Easy...not likely AFAICT!

    However, here's a start on a macro. As written this will only work on simple cell references but it does demonstrate the process. I also made it only work on the selection just to limit things that of course can be easily be changed to the active range and to loop through the worksheets in the workbook. 1000 Sheets REALLY!

    The hard work of course, yet to be done and I'm sure you're up to the task, is tearing apart formulas and adjusting each reference!

    Reference Sheet (Your Stock Sheet)
    zeddyReference.PNG

    One of your 1000
    zeddybefore.PNG

    The code:
    Code:
    Option Explicit
    
    Sub AdjRefs()
    
       Dim Cell     As Range
       Dim zFormula As String
       Dim vParts   As Variant
       Dim zAddr    As String
       
       For Each Cell In Selection
          zFormula = Cell.Formula
          vParts = Split(zFormula, "!")
          zAddr = Range(vParts(1)).Offset(4, 0).Address(False, False, xlA1)
          Cell.Formula = vParts(0) & "!" & zAddr
       Next Cell
       
    End Sub
    Result:
    zeddyafter.PNG

    Test File: ZeddyChgFormulas-RG1.xlsm

    HTH
    Last edited by RetiredGeek; 2016-05-14 at 11:00.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2016-05-14)

  6. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Ah...using .Offset. Brilliant!!!

  7. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Just had another thought...WOW two in one day!

    You could insert a HelperSheet which pushes everything down by 4 rows then do a global search & replace for Stock! to HelperSheet!.

    Then every time Stock updates just copy it to HelperSheet, via Macro of course. Or you could set HelperSheet to just be references to Stock starting in HelperSheet!A5 with formula =Stock!A1 copy across then down.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2016-05-15)

  9. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    I'm on a roll!

    Here's a new version that will handle more than one reference in a formula and handle ranges like in SUMs. Not sure this will handle everything but it's a step in the right direction.

    Code:
    Option Explicit
    
    Sub AdjRefs()
    
       Dim Cell     As Range
       Dim zFormula As String
       Dim vParts   As Variant
       Dim zAddr    As String
       
       For Each Cell In Selection
          zFormula = Cell.Formula
          vParts = Split(zFormula, "!")
          zAddr = Range(vParts(1)).Offset(4, 0).Address(False, False, xlA1)
          Cell.Formula = vParts(0) & "!" & zAddr
       Next Cell
       
    End Sub
    
    
    
    Sub MultiRefs()
    
       Dim iStart   As Integer
       Dim iEnd     As Integer
       Dim zFormula As String
       Dim lNewRef  As Long
       Dim Cell     As Range
       Dim lRowOffs As Long
    
       lRowOffs = 4    'Number of rows to offset formulas
       
       For Each Cell In Selection
       
          iStart = 1
          iEnd = 0
          zFormula = Cell.FormulaR1C1
          iStart = InStr(iStart, zFormula, "Sheet1!R[")
          
          If (iStart = 0) Then  '*** If no reference move to next cell ***
            GoTo NothingToProcess
          Else
            iStart = iStart + Len("Sheet1!R[")
          End If
          
     Debug.Print "Start: " & zFormula
    
          Do While iStart > iEnd
            iEnd = InStr(iStart, zFormula, "]")
            lNewRef = Val(Mid(zFormula, iStart, iEnd - iStart))
            lNewRef = lNewRef + IIf(lNewRef < 0, lRowOffs, lRowOffs * -1)
            zFormula = Left(zFormula, iStart - 1) & lNewRef & Right(zFormula, Len(zFormula) - (iEnd - 1))
            iStart = InStr(iStart, zFormula, "Sheet1!R[") + Len("Sheet1!R[")
          Loop
       
     '*** The following code handles the second term in ranges!
     
          iStart = 1
          iEnd = 0
          iStart = InStr(iStart, zFormula, ":R[")
          
          If (iStart > 0) Then   '*** ONLY process if range found ***
            iStart = iStart + Len(":R[")
    
            Do While iStart > iEnd
              iEnd = InStr(iStart, zFormula, "]")
              lNewRef = Val(Mid(zFormula, iStart, iEnd - iStart))
              lNewRef = lNewRef + IIf(lNewRef < 0, lRowOffs, lRowOffs * -1)
              zFormula = Left(zFormula, iStart - 1) & lNewRef & Right(zFormula, Len(zFormula) - (iEnd - 1))
              iStart = InStr(iStart, zFormula, ":R[") + Len(":R[")
            Loop
            
          End If
          
     Debug.Print "Ending:" & zFormula
       
          Cell.FormulaR1C1 = zFormula
          
    NothingToProcess:
    
       Next Cell
       
    End Sub   'MultiRefs()
    Columns added for range test:
    ColumnsAdded.PNG
    Before:
    zeddybefore.PNG

    After:
    zeddyafter.PNG

    Changes made:
    Code:
    Start: =CONCATENATE(Sheet1!R[-6]C,Sheet1!R[-6]C[3])
    Ending:=CONCATENATE(Sheet1!R[-2]C,Sheet1!R[-2]C[3])
    Start: =CONCATENATE(Sheet1!R[-6]C,Sheet1!R[-6]C[3])
    Ending:=CONCATENATE(Sheet1!R[-2]C,Sheet1!R[-2]C[3])
    Start: =CONCATENATE(Sheet1!R[-6]C,Sheet1!R[-6]C[3])
    Ending:=CONCATENATE(Sheet1!R[-2]C,Sheet1!R[-2]C[3])
    Start: =CONCATENATE(Sheet1!R[-6]C,Sheet1!R[-6]C[3])
    Ending:=CONCATENATE(Sheet1!R[-2]C,Sheet1!R[-2]C[3])
    Start: =CONCATENATE(Sheet1!R[-6]C,Sheet1!R[-6]C[3])
    Ending:=CONCATENATE(Sheet1!R[-2]C,Sheet1!R[-2]C[3])
    Start: =SUM(Sheet1!R[-7]C[8]:R[-7]C[12])
    Ending:=SUM(Sheet1!R[-3]C[8]:R[-3]C[12])
    Start: =SUM(Sheet1!R[-7]C[8]:R[-7]C[12])
    Ending:=SUM(Sheet1!R[-3]C[8]:R[-3]C[12])
    Start: =SUM(Sheet1!R[-7]C[8]:R[-7]C[12])
    Ending:=SUM(Sheet1!R[-3]C[8]:R[-3]C[12])
    Test File: ZeddyChgFormulas-RG2.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    I missed your initial post completely!
    HeadBang.gif

    In my defense you posted while I was coding and then I just posted.
    And then of course I didn't look backward when reposting...
    HomerDOH.png

    Sure wish I'd seen it sooner would have saved me several hours!

    Oh well, I learned something in the process so not all is lost!

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2016-05-14)

  12. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud and RG

    Thanks very much to both of you.
    I am currently digesting.
    And after I've eaten, I'm watching a film.
    I will report back tomorrow with what I did.

    (1000+ sheets? Yup, not one of MY files)

    zeddy

  13. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG & Maud

    Easy...not likely AFAICT!
    It was RG who reminded me of the best method, which I had forgotten.
    (and a well deserved extra Thank You for that, see above)
    The fix took almost 2 seconds to achieve, without any vba coding.

    1. Insert a new sheet and name as zzzz (time taken: 1 second)
    2. Use Ctrl-H to do a workbook global replace of Stock! for zzzz! (time taken: almost immediate)
    3. On sheet [zzzz], insert 4 rows at top of the sheet. (time taken: almost immediate)
    4. Use Ctrl-H to do a workbook global replace of zzzz! for Stock! (time taken: almost immediate)
    5. Delete sheet [zzzz]

    All formulas on all sheets have now been automatically adjusted to the correct references (including formulas with multiple references).

    It's a long time since I had to fix 'structure' defects like this.
    The method could also be used to sort out column references that are 'out of sync' etc.

    Many thanks for the coding examples too. Always good to look at other techniques and methods.

    zeddy
    Last edited by zeddy; 2016-05-15 at 05:46.

Posting Permissions

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