Read and Display formula Components

Rayne
Rayne used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
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
Rayne

Author

Commented:
Thanks again Brian :)
This works GOLD. I will keep looking at it and trying to understand. Will let you know if any questions
redmondb

Commented:
Thanks,  Rayne.

Will let you know if any questions
I'll keep any eye out!
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Rayne

Author

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 :)
Rayne

Author

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. :)
redmondb

Commented:
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.
Rayne

Author

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)
redmondb

Commented:
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.
Rayne

Author

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial