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.
COURSE of the MONTH
14 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
Active today
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 today
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 today
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.
Active today
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.
Active today
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
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Outlook for dependable use in a very small business
This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.