Thank you, Rick!
"Rick Rothstein (MVP - VB)" wrote:
> One more thing I should mention... the macro I posted physically changes the
> formulas in the cells and those changes cannot be undone... so, if this is
> not what you actually wanted, do NOT run the macro against any live formulas
> (that is, test it on a COPY of your worksheet first). If you need this code
> to output a value for inclusion in a different cell than the one that
> contains the formula, it would be very easy to convert the code to a UDF
> (user defined function)... just let me know and I rewrite it into a UDF for
> you.
>
> Rick
>
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:OEWgbKN%(E-Mail Removed)...
> >I should have mentioned, you need to select one or more cells (the macro
> >can handle contiguous as well as non-contiguous ranges of cells) and then
> >run the macro; it will then expand every 3D reference (even differing
> >multiple 3D references within a single cell's formula) in every cell within
> >the selection.
> >
> > Rick
> >
> >
> > "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> > message news:%23xDe5GN%(E-Mail Removed)...
> >> Give this macro a try; place it in a Module (Insert/Module from the VBA
> >> menu bar)...
> >>
> >> Sub Expand3dFormula()
> >> Dim RE As Object
> >> Dim C As Range
> >> Dim WS As Worksheet
> >> Dim X As Long
> >> Dim Y As Long
> >> Dim StartIndex As Long
> >> Dim EndIndex As Long
> >> Dim ShtRef As String
> >> Dim ShtStart As String
> >> Dim ShtEnd As String
> >> Dim CelRef As String
> >> Dim WSname As String
> >> Dim Reference As String
> >> Dim REsub As String
> >> Dim Parts() As String
> >> For Each C In Selection
> >> Set RE = CreateObject("VBScript.RegExp")
> >> RE.Global = True
> >> RE.Pattern = "[^:!a-zA-Z0-9=_]"
> >> REsub = RE.Replace(C.Formula & "=", " ")
> >> RE.Pattern = " "
> >> REsub = Trim(RE.Replace(REsub, " "))
> >> Set RE = Nothing
> >> Parts = Split(REsub)
> >> For X = 1 To UBound(Parts) - 1
> >> Reference = ""
> >> If Parts(X) Like "*:*!*" Then
> >> ShtRef = Left(Parts(X), InStr(Parts(X), "!") - 1)
> >> CelRef = Mid(Parts(X), InStr(Parts(X), "!") + 1)
> >> ShtStart = Left(ShtRef, InStr(ShtRef, ":") - 1)
> >> ShtEnd = Mid(ShtRef, InStr(ShtRef, ":") + 1)
> >> For Each WS In Worksheets
> >> If WS.Name = ShtStart Then StartIndex = WS.Index
> >> If WS.Name = ShtEnd Then EndIndex = WS.Index
> >> Next
> >> For Y = StartIndex To EndIndex
> >> WSname = Worksheets(Y).Name
> >> If WSname Like "*[!a-zA-Z_]*" Then WSname = "'" & WSname & "'"
> >> Reference = Reference & WSname & "!" & CelRef
> >> If Y < EndIndex Then Reference = Reference & ","
> >> Next
> >> C.Formula = Replace(C.Formula, Parts(X), Reference)
> >> End If
> >> Next
> >> Next
> >> End Sub
> >>
> >> Rick
> >>
> >>
> >>
> >> "Dima" <(E-Mail Removed)> wrote in message
> >> news:45FCFF38-0949-47A7-9D15-(E-Mail Removed)...
> >>> Thanks Jim for replying!
> >>> Yes, I mean the way to convert this:
> >>> =SUM(Sheet2:Sheet4!A1)
> >>> to
> >>> =SUM(Sheet2!A1,Sheet3!A1,Sheet4!A1)
> >>> Dmitry
> >>> "Jim Rech" wrote:
> >>>
> >>>> If you mean that you'd like a way to convert this:
> >>>>
> >>>> =SUM(Sheet2:Sheet4!A1)
> >>>>
> >>>> to
> >>>>
> >>>> =SUM(Sheet2!A1,Sheet3!A1,Sheet4!A1)
> >>>>
> >>>> I don't think there is anything in Excel that does it unfortunately.
> >>>>
> >>>> --
> >>>> Jim
> >>>> "Dima" <(E-Mail Removed)> wrote in message
> >>>> news:F153F82D-E9CF-4C84-A496-(E-Mail Removed)...
> >>>> | Hello!
> >>>> | How to convert all 3d references to normal references in a workbook?
> >>>> | I want to move the ranges to which the 3d references refer, but the
> >>>> 3d
> >>>> | references do not follow the movement of the precedents inside the 3d
> >>>> range
> >>>> | and become corrupted.
> >>>> | Regards,
> >>>> | Dmitry
> >>>>
> >>>>
> >>>>
> >>
> >
>
>
|
Similar Threads
