Read and Display formula Components

Hello All,

I have a VBA question :(
Not sure if this is possible or I am over thinking. Formula Cells in D8:D10 has some formulas that use components from range D15:D15. Now what I need (as I am dealing with 160 rows) is to list out the text names of the components in column F.
For example: if I loop through column D, and when my current cell is D10, I read its formula and then the corresponding F10 displays “  = (c1+c3+c4)/c4”

Is that possible?
Attached is example of the needed format…
Thank you
readAndDisplyFormulas.xlsx
RayneAsked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
Hi, Rayne.

Please see the attached for a crude first-pass at my understanding of what you want. The code is...
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCell As Range

If Intersect(Target, Union(Range("$D$8:$D$10"), Range("$C$15:$D$18"))) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each xCell In Range("F8:F10")
    xCell = "'" & Replace(Replace(Replace(Replace(xCell.Offset(0, -2).Formula, "D15", Range("C15")), "D16", Range("C16")), "D17", Range("C17")), "D18", Range("C18"))
Next

Application.EnableEvents = True

End Sub

Open in new window

Regards,
Brian.
readAndDisplyFormulas-V2.xlsm
0
 
RayneAuthor Commented:
Thanks again Brian :)
This works GOLD. I will keep looking at it and trying to understand. Will let you know if any questions
0
 
redmondbCommented:
Thanks,  Rayne.

Will let you know if any questions
I'll keep any eye out!
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
RayneAuthor Commented:
Hello Brian,
 
 thanks for your Golden help....
 
 For Each xCell In Range("F8:F10")
        xCell = "'" & Replace(Replace(Replace(Replace(xCell.Offset(0, -2).Formula, "D15", Range("C15")), "D16", Range("C16")), "D17", Range("C17")), "D18", Range("C18"))
    Next
      
      Just wanted to confirm whats happening here? Can you explain whats happening in the replace?
      
      Assuming my actual range for [xCell] is F8:F30, do I have to replicate the pieces for the 30th row in the replace string? in the for loop....

thank you :)
0
 
RayneAuthor Commented:
Let me know if there is more of a dynamic way, then I will open a up another separate question. No worries for points. :)
0
 
redmondbCommented:
Rayne,

What happens to the "Component" table - is it simply moved down or is it elsewhere? Also, are you limited to a max of 4 components?

Thanks,
Brian.
0
 
RayneAuthor Commented:
Hello Brian,

Actually there are 18 components that will be used in the formulas :( F8:F30)
The components stay in the same cells (they dont move)
0
 
redmondbCommented:
Rayne,

The "converted" formulas are in F8 to F30, so surely the actual formulas are in D8 to D30 - which would blast through the second column of the Components table. So the table must be somewhere else - but where?!

Thanks,
Brian.
0
 
RayneAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.