Avatar of Rayne
Rayne
Flag for United States of America asked on

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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rayne

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
redmondb

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rayne

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

Thanks,  Rayne.

Will let you know if any questions
I'll keep any eye out!
Rayne

ASKER
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 :)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rayne

ASKER
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

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

ASKER
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)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rayne

ASKER
redmondb

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

ASKER
Brian,

Here is another question if you are interested
https://www.experts-exchange.com/questions/27980025/VBA-to-do-CalX.html

Thank you
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck