Formula View-Follow Up

Posted on 2012-12-27
Last Modified: 2012-12-27
This is follow up to this previous question

How can the formula components be listed in a dynamic way, assuming there are 18 components?

Question by:Rayne
  • 3
  • 2
LVL 26

Expert Comment

ID: 38724774
Hi, Rayne.

Please let us know the address range of the 18 components - assuming that the actual formulas are in D8 to D30 and the results are in F8 to F30

LVL 26

Accepted Solution

redmondb earned 500 total points
ID: 38724929

In the attached, I've assumed that you simply pushed the components down to make room for the extra formulas. The code is...
Option Explicit

Const XFORMULAS = "D8:D30"
Const XCOMPONENTS = "C32:C49"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCell   As Range
Dim xArray  As Variant
Dim xLoaded As Boolean
Dim xHold   As String
Dim i       As Long

If Intersect(Target, Union(Range(XFORMULAS), Range(XCOMPONENTS))) Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

    For Each xCell In Range(XFORMULAS)
        xHold = xCell.Formula
        If Mid(xHold, 1, 1) = "=" Then
            If Not xLoaded Then
                xArray = WorksheetFunction.Transpose(Range(XCOMPONENTS).Value)
                xLoaded = True
            End If
            For i = 1 To UBound(xArray)
                If xArray(i) <> "" Then xHold = Replace(xHold, Replace(Range("D31").Offset(i, 0).Address, "$", ""), xArray(i))
            xCell.Offset(0, 2) = "'" & xHold
            xCell.Offset(0, 2) = ""
        End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Open in new window

Edit: Minor change to blank out a row's result if it has no formula.


Author Comment

ID: 38725491
Hello Brian,

Simply speaking –You Rock.  ALL Hats off to you!!
Thanks for the effort you put in here.

Thank you :)

Author Closing Comment

ID: 38725492
Perfect Solution!!
LVL 26

Expert Comment

ID: 38725553
Thanks, Rayne. Glad you're sorted!

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now