Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers. It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.
Solved
Read and Display formula Components
Posted on 2012-12-26
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
4
10 Comments
Active 2 days ago
Hi, Rayne.
Please see the attached for a crude first-pass at my understanding of what you want. The code is...
Brian.
readAndDisplyFormulas-V2.xlsm
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
Brian.
readAndDisplyFormulas-V2.xlsm
Active 2 days ago
Thanks again Brian :)
This works GOLD. I will keep looking at it and trying to understand. Will let you know if any questions
This works GOLD. I will keep looking at it and trying to understand. Will let you know if any questions
Active 2 days ago
Hello Brian,
thanks for your Golden help....
For Each xCell In Range("F8:F10")
xCell = "'" & Replace(Replace(Replace(Re
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 :)
thanks for your Golden help....
For Each xCell In Range("F8:F10")
xCell = "'" & Replace(Replace(Replace(Re
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 :)
Active 2 days ago
Let me know if there is more of a dynamic way, then I will open a up another separate question. No worries for points. :)
Active 2 days ago
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.
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.
Active 2 days ago
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)
Actually there are 18 components that will be used in the formulas :( F8:F30)
The components stay in the same cells (they dont move)
Active 2 days ago
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.
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.
Active 2 days ago
Brian,
Here is another question if you are interested
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27980025.html
Thank you
Here is another question if you are interested
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27980025.html
Thank you
Featured Post
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month7 days, 22 hours left to enroll
610 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.