Problem using DirectDependents

[Kevin Beckham]

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

 

[Patrick Molloy]

you can't put = A * B into a cell, its "illegal"

 

[Kevin Beckham]

I didn't express myself clearly

I would like it to return the string "=A * B"
At present, it returns "=B1 * B2"

 

[Kevin Beckham]

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

 

[Patrick Molloy]

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]

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

 

[Jacob Skaria]

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