Solved

Read and Display formula Components

Posted on 2012-12-26
10
457 Views
Last Modified: 2012-12-28
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
0
Comment
Question by:Rayne
  • 6
  • 4
10 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38721035
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
0
 

Author Closing Comment

by:Rayne
ID: 38721639
Thanks again Brian :)
This works GOLD. I will keep looking at it and trying to understand. Will let you know if any questions
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38722179
Thanks,  Rayne.

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

Author Comment

by:Rayne
ID: 38722413
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 :)
0
 

Author Comment

by:Rayne
ID: 38722425
Let me know if there is more of a dynamic way, then I will open a up another separate question. No worries for points. :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 26

Expert Comment

by:redmondb
ID: 38723630
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.
0
 

Author Comment

by:Rayne
ID: 38724213
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)
0
 

Author Comment

by:Rayne
ID: 38724232
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38724236
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.
0
 

Author Comment

by:Rayne
ID: 38728384
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
Excel Formula 4 28
Access Excel export not behaving 2 25
Most Consistent Performer 4 21
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now