Dear Guys
I really hope some of you can help me with the following problem.
I have programmed a "show formula" bottom, for both values and cell references to excel.
My problem is that if i make an absolut reference of say A1 (assume value 3), such that: $A$1,
and for example multiply it with another cell (Assume value 2), and i push the " show formula" value bottom it shows :
= $A$1*2
But instead of this, I want it to show:
= 3*2
I have worked with this in weeks now but without any result.
How can I do this? a part of my code should be below.
[VBA]
Function PatternExtract(strF As String, strPattern As String, _
Optional boolIgnoreCase As Boolean = True, Optional lngInstance As Long = 1) As Variant
Dim RegExp As Object, RegExpMatch As Object
On Error Resume Next
Set RegExp = CreateObject("vbscript.regexp")
With RegExp
.Global = True
.IgnoreCase = boolIgnoreCase
.Pattern = strPattern
End With
Set RegExpMatch = RegExp.Execute(strF)
If lngInstance > RegExpMatch.Count Then
PatternExtract = ""
Else
If lngInstance = -1 Then
For lngInstance = 1 To RegExpMatch.Count Step 1
PatternExtract = PatternExtract & " " & RegExpMatch(lngInstance - 1)
Next lngInstance
PatternExtract = Replace(PatternExtract, " ", "", 1, 1)
Else
PatternExtract = RegExpMatch(lngInstance - 1)
End If
End If
Set RegExpMatch = Nothing
End Function
Function Udregn(Cell As Range) As String
Dim vRefs As Variant, lngRef As Long
Udregn = "" & Cell.Formula
vRefs = Split(PatternExtract(Udregn, "[A-Z]{1,2}[0-9]+", , -1))
For lngRef = LBound(vRefs) To UBound(vRefs) Step 1
Udregn = Replace(Udregn, vRefs(lngRef), Round(Evaluate(vRefs(lngRef)), 3), 1, -1, vbTextCompare)
Next lngRef
End Function
[/VBA]
Reply With Quote
Originally Posted by Paul_Hossler
