Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
2 days, 14 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
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
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
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 :)
Let me know if there is more of a dynamic way, then I will open a up another separate question. No worries for points. :)
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.
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)
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.
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
Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Suggested Courses
Course of the Month2 days, 14 hours left to enroll
696 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.