Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula auditing toolbar (office XP)

    hi everyone, i got columns of variables from A1:BA2000 or more columns. and i just wanna to figure out which variable are used by another formula and i just use the 'Trace Dependants' button of formula auditing toolbar. hmm.. since the columns might be more than 100 and with using this method is manual and a bit time consuming, is there another way of doing it, i guess maybe with auto method cause this task can be quite routine? thanks.

    jill.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula auditing toolbar (office XP)

    With XP I think this code should work (though I can not test it as I use XL97)

    <pre>Sub ShowTheDependents()
    Dim rCell As Range
    For Each rCell In Selection
    rCell.ShowDependents
    Next
    End Sub</pre>


    There is a bug in XL97 that this method can not be used so this works in XL97:
    <pre>Sub ShowTheDependents()
    Dim rng As Range
    Dim rcell As Range
    Set rng = Selection
    For Each rcell In rng
    rcell.Select
    Application.ExecuteExcel4Macro "TRACER.DISPLAY(FALSE,TRUE)"
    Next
    End Sub</pre>


    Steve

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula auditing toolbar (office XP)

    hi Steve. thanks for your code. both code works. my purpose is to delete the variables that are not used by other formulas thus to create a new one to test again. since the dependents macro for one row shows so many 'bullets' points indicate dependents by other formulas, it does blurs my eyes a bit, on the other hand, is there a way ( a code ) to trace variables that does not have dependents instead, that would be clearer to which i need to delete. Thanks again. Sorry i didn't mention this earlier. anyway, i can use your code for good by just select some portion of columns one by one and delete what not necessary.


    Edit: okay, i will sort things out. i will post more questions when there are any. thanks for your kind and helpful support.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula auditing toolbar (office XP)

    Are you looking for something like this? It colors the cells yellow for the selected cells that have no dependents.

    <pre>Option Explicit
    Sub HighlightNoneDependents()
    Dim rng As Range
    Dim rcell As Range
    Dim iDeps As Integer
    Set rng = Selection
    rng.Interior.ColorIndex = xlNone
    For Each rcell In rng
    iDeps = 0
    On Error Resume Next
    iDeps = rcell.Dependents.Count
    On Error GoTo 0
    If iDeps = 0 Then _
    rcell.Interior.Color = vbYellow
    Next
    End Sub</pre>


    Steve

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula auditing toolbar (office XP)

    Hi Steve. [img]/forums/images/smilies/smile.gif[/img]. yes, what i am looking, i have found, thanks to your code a lot. my next step is to copy the first row of 145 variables into another sheet at the same column and row as its original sheet so to Ctrl+` to display the formulas for me to see for summary as reference for me to create new variables. to maintain its original formulas, i will cut and paste each copied formulas into vertical form for easy viewing. all these i can do record macro. after this, i need to sort one group got dependents and another got no dependents. this one will enhance my viewing as well. [img]/forums/images/smilies/smile.gif[/img]. do you have idea on doing this?? Thanks.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula auditing toolbar (office XP)

    <hr>do you have idea on doing this?? <hr>

    Not at this time, since I don't seem to follow exactly what you want to do. Could you elaborate on what you want to accomplish (I don't need the steps, just the "goal" of the macro: I might not do it the way you might think about doing it

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula auditing toolbar (office XP)

    hi Steve. i understand. well, i too understand that my method is not most practical, in terms of time saving and runs simple, but i can do it thus far and will improve them all next time. for now, i guess i will make it simple with attached file with two columns needed answer. i hope you will not crack your brain to help me. Thanks.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula auditing toolbar (office XP)

    <P ID="edit" class=small>(Edited by sdckapr on 06-Nov-04 08:02. Added more comments)</P>Still not sure what you are after. To me this question has nothing to do with the original. The original dealt with cells from all over the sheet and their dependencies, this has cells in a list and they are highlighted. You could just "transfer" the dependent cells formulas and be done. (though you don't indicate enough details)

    This will do it, (though, as I said) I am not clear what you wanted at the end.

    This does convert them to text before transfering them. It is not a move so any dependencies on the original do not get carreied over.

    <pre>Option Explicit
    Sub BoatCode()
    Dim rng As Range
    Dim rcell As Range

    Set rng = Selection

    For Each rcell In rng
    rcell.Offset(0, _
    1 - 1 * (rcell.Interior.ColorIndex = xlNone)) = _
    "'" & rcell.Formula
    Next

    Set rcell = Nothing
    Set rng = Nothing
    End Sub</pre>


    It is "pretty specific" since you aren't really clear on how general you want it.
    Steve

  9. #9
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula auditing toolbar (office XP)

    hi. this will do fine enough. Thank You.


    EDIT: i would be appreciate if the sorted formulas as in continous form, i.e. no blanks in between before next formulas display. cause, i want to make it simple, no need to scroll down and down to make my summary. Thanks again.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula auditing toolbar (office XP)

    Not sure what you are asking (if there is a request).

    Could you elaborate?

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula auditing toolbar (office XP)

    Hi. i attached the file again edited. Thanks.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula auditing toolbar (office XP)

    Perhaps, this is what you are after?

    Steve
    <pre>Option Explicit
    Sub BoatCode2()
    Dim rng As Range
    Dim rCell As Range
    Dim lHiRowOS As Long
    Dim lNonRowOS As Long

    Set rng = Selection
    lHiRowOS = 0
    lNonRowOS = 0

    rng.Offset(0, 1).Resize(, 2).ClearContents
    For Each rCell In rng
    If rCell.Interior.ColorIndex = xlNone Then
    rng.Cells(1).Offset(lNonRowOS, 2) = _
    "'" & rCell.Formula
    lNonRowOS = lNonRowOS + 1
    Else
    rng.Cells(1).Offset(lHiRowOS, 1) = _
    "'" & rCell.Formula
    lHiRowOS = lHiRowOS + 1
    End If
    Next

    Set rCell = Nothing
    Set rng = Nothing
    End Sub</pre>


  13. #13
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula auditing toolbar (office XP)

    Hi. yes, yes, thank you for your quick reply and your code. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •