Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using DirectDependents
I can't get the following function to return the direct dependents
Function ResolveFormula(ByRef rngCell As Range, iColLabels As Integer) As String Dim sCell As String Dim rngPrec As Range sCell = rngCell.Formula For Each rngPrec In rngCell.DirectPrecedents With rngPrec sCell = Replace(sCell, rngPrec.Address(False, False, xlA1), _ .Offset(0, iColLabels - .Column + 1).Value) End With Next rngPrec ResolveFormula = sCell End Function For the worksheet A 1 B 2 =B1 * B2 =ResolveFormula(B3, 1) to give =A * B TIA Kevin Beckham |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using DirectDependents
you can't put = A * B into a cell, its "illegal"
"Kevin Beckham" wrote: I can't get the following function to return the direct dependents Function ResolveFormula(ByRef rngCell As Range, iColLabels As Integer) As String Dim sCell As String Dim rngPrec As Range sCell = rngCell.Formula For Each rngPrec In rngCell.DirectPrecedents With rngPrec sCell = Replace(sCell, rngPrec.Address(False, False, xlA1), _ .Offset(0, iColLabels - .Column + 1).Value) End With Next rngPrec ResolveFormula = sCell End Function For the worksheet A 1 B 2 =B1 * B2 =ResolveFormula(B3, 1) to give =A * B TIA Kevin Beckham |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using DirectDependents
I didn't express myself clearly
I would like it to return the string "=A * B" At present, it returns "=B1 * B2" "Patrick Molloy" wrote: you can't put = A * B into a cell, its "illegal" "Kevin Beckham" wrote: I can't get the following function to return the direct dependents Function ResolveFormula(ByRef rngCell As Range, iColLabels As Integer) As String Dim sCell As String Dim rngPrec As Range sCell = rngCell.Formula For Each rngPrec In rngCell.DirectPrecedents With rngPrec sCell = Replace(sCell, rngPrec.Address(False, False, xlA1), _ .Offset(0, iColLabels - .Column + 1).Value) End With Next rngPrec ResolveFormula = sCell End Function For the worksheet A 1 B 2 =B1 * B2 =ResolveFormula(B3, 1) to give =A * B TIA Kevin Beckham |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using DirectDependents
With a leading space so it is not a true formula
" =A * B" With error-trapping, it can be called from a sub-routine, but not from a worksheet "Kevin Beckham" wrote: I didn't express myself clearly I would like it to return the string "=A * B" At present, it returns "=B1 * B2" "Patrick Molloy" wrote: you can't put = A * B into a cell, its "illegal" "Kevin Beckham" wrote: I can't get the following function to return the direct dependents Function ResolveFormula(ByRef rngCell As Range, iColLabels As Integer) As String Dim sCell As String Dim rngPrec As Range sCell = rngCell.Formula For Each rngPrec In rngCell.DirectPrecedents With rngPrec sCell = Replace(sCell, rngPrec.Address(False, False, xlA1), _ .Offset(0, iColLabels - .Column + 1).Value) End With Next rngPrec ResolveFormula = sCell End Function For the worksheet A 1 B 2 =B1 * B2 =ResolveFormula(B3, 1) to give =A * B TIA Kevin Beckham |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using DirectDependents
Option Explicit
Sub test() ResolveFormula Range("D6") End Sub Sub ResolveFormula1(rngCell As Range) Dim sCell As String Dim rngPrec As Range Dim index As Long sCell = rngCell.Formula index = 64 For Each rngPrec In rngCell.DirectPrecedents index = index + 1 sCell = Replace(sCell, rngPrec.Address(False, False), Chr(index)) Next rngcell.offaset(,1).Value= "'" & sCell End Sub "Kevin Beckham" wrote: With a leading space so it is not a true formula " =A * B" With error-trapping, it can be called from a sub-routine, but not from a worksheet "Kevin Beckham" wrote: I didn't express myself clearly I would like it to return the string "=A * B" At present, it returns "=B1 * B2" "Patrick Molloy" wrote: you can't put = A * B into a cell, its "illegal" "Kevin Beckham" wrote: I can't get the following function to return the direct dependents Function ResolveFormula(ByRef rngCell As Range, iColLabels As Integer) As String Dim sCell As String Dim rngPrec As Range sCell = rngCell.Formula For Each rngPrec In rngCell.DirectPrecedents With rngPrec sCell = Replace(sCell, rngPrec.Address(False, False, xlA1), _ .Offset(0, iColLabels - .Column + 1).Value) End With Next rngPrec ResolveFormula = sCell End Function For the worksheet A 1 B 2 =B1 * B2 =ResolveFormula(B3, 1) to give =A * B TIA Kevin Beckham |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using DirectDependents
That's similar to what I ended up with - the function cannot be called from a
worksheet though. Thanks for your input. Function ResolveFormula(ByRef rngCell As Range, iColLabels As Integer) As String 'can only be called by a subroutine Dim sCell As String Dim rngPrec As Range Dim rngPrecs As Range sCell = rngCell.Formula On Error Resume Next Set rngPrecs = rngCell.DirectPrecedents On Error GoTo 0 If Not rngPrecs Is Nothing Then For Each rngPrec In rngPrecs With rngPrec sCell = Replace(sCell, rngPrec.Address(False, False, xlA1), _ " [" & .Offset(0, iColLabels - .Column).Value & "] ") End With Next rngPrec End If ResolveFormula = " " & sCell End Function "Patrick Molloy" wrote: Option Explicit Sub test() ResolveFormula Range("D6") End Sub Sub ResolveFormula1(rngCell As Range) Dim sCell As String Dim rngPrec As Range Dim index As Long sCell = rngCell.Formula index = 64 For Each rngPrec In rngCell.DirectPrecedents index = index + 1 sCell = Replace(sCell, rngPrec.Address(False, False), Chr(index)) Next rngcell.offaset(,1).Value= "'" & sCell End Sub "Kevin Beckham" wrote: With a leading space so it is not a true formula " =A * B" With error-trapping, it can be called from a sub-routine, but not from a worksheet "Kevin Beckham" wrote: I didn't express myself clearly I would like it to return the string "=A * B" At present, it returns "=B1 * B2" "Patrick Molloy" wrote: you can't put = A * B into a cell, its "illegal" "Kevin Beckham" wrote: I can't get the following function to return the direct dependents Function ResolveFormula(ByRef rngCell As Range, iColLabels As Integer) As String Dim sCell As String Dim rngPrec As Range sCell = rngCell.Formula For Each rngPrec In rngCell.DirectPrecedents With rngPrec sCell = Replace(sCell, rngPrec.Address(False, False, xlA1), _ .Offset(0, iColLabels - .Column + 1).Value) End With Next rngPrec ResolveFormula = sCell End Function For the worksheet A 1 B 2 =B1 * B2 =ResolveFormula(B3, 1) to give =A * B TIA Kevin Beckham |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using DirectDependents
Try the below UDF and feedback
Function ResolveFormula(ByRef rngCell As Range) As String Dim blnString As Boolean ResolveFormula = rngCell.Formula For intTemp = 1 To Len(ResolveFormula) If Mid(ResolveFormula, intTemp, 1) = Chr(34) Then _ blnString = Not blnString If IsNumeric(Mid(ResolveFormula, intTemp, 1)) And _ blnString = False Then Mid(ResolveFormula, intTemp, 1) = Chr(116) Next ResolveFormula = Replace(ResolveFormula, Chr(116), "") End Function If this post helps click Yes --------------- Jacob Skaria "Kevin Beckham" wrote: I can't get the following function to return the direct dependents Function ResolveFormula(ByRef rngCell As Range, iColLabels As Integer) As String Dim sCell As String Dim rngPrec As Range sCell = rngCell.Formula For Each rngPrec In rngCell.DirectPrecedents With rngPrec sCell = Replace(sCell, rngPrec.Address(False, False, xlA1), _ .Offset(0, iColLabels - .Column + 1).Value) End With Next rngPrec ResolveFormula = sCell End Function For the worksheet A 1 B 2 =B1 * B2 =ResolveFormula(B3, 1) to give =A * B TIA Kevin Beckham |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DirectDependents not working properly. | Excel Programming | |||
What's the difference between .Dependents and .DirectDependents? | Excel Programming | |||
Finding when there are no directdependents | Excel Programming | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |