Return formula minus = sign

  • Thread starter Thread starter Patrick Simonds
  • Start date Start date
P

Patrick Simonds

Is there any way to get the code below to return formula in cell B4 without
the = sign. So if the cell contains =10+10+4.50 I want only 10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text
 
Patrick said:
Is there any way to get the code below to return formula in cell B4 without
the = sign. So if the cell contains =10+10+4.50 I want only 10+10+4.50

TextBox93.Text = Worksheets("Income").Range("B4").Text
Click to expand...

try using this:

Cells(rowindex,columnindex).Replace What:="=", Replacement:=""
 
Hi Patrick

Try below please.


Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(Range("D8"))
End Sub


'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+/-'
' Note : Eg. =5+2 => '5+2' or =+B5+C3 => 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Or _
Mid(cell.Formula, 2, 1) = "-" Then

OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function
 
I can understand stripping the + sign from after the initial = sign, but I don't
think removing =- would be a good idea. That negative sign looks important to
the formula.

=-a1+b1
would return
a1+b1
And that doesn't look like it reflects what the formula really does.
 
Hi Dave.

Thanks. You are quite right.
My brain must have been disabeled for a few seconds.
Notice the way I am using mid with 2 parameters. 8-)
Hope you can approve belove.


Option Explicit

Sub test()
Dim MyString As String
MyString = OnlyString(ActiveCell)
Stop
End Sub


'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060903a
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+'
' Note : Eg. =5+2 => '5+2' or =+B5+C3 => 'B5+C3'
'----------------------------------------------------------
'
Function OnlyString(cell As Range) As String
If TypeName(cell.Value) = "String" Or _
IsEmpty(cell.Value) Then
OnlyString = "No formula."
Exit Function
End If

If cell.HasFormula Then
If Mid(cell.Formula, 2, 1) = "+" Then
OnlyString = _
Mid(cell.Formula, 3, Len(cell.Formula) - 2)
Else
OnlyString = _
Mid(cell.Formula, 2, Len(cell.Formula) - 1)
End If
End If
End Function
 
I'm not sure I'd check anything but .hasformula (drop the typename() and
isempty() checks???)

This is what I use to return the formula (including the equal sign) (just for
comparison purposes):

Option Explicit
Function GetFormula(Rng As Range)

Dim myFormula As String
GetFormula = ""
With Rng.Cells(1)
If .HasFormula Then
If Application.ReferenceStyle = xlA1 Then
myFormula = .Formula
Else
myFormula = .FormulaR1C1
End If
If .HasArray Then
GetFormula = "{=" & Mid(myFormula, 2, Len(myFormula)) & "}"
Else
GetFormula = myFormula
End If
End If
End With
End Function
 
Hi Dave.

I am very pleased for your advise, and I do hope, I do you credit with
below.
Thanks.


Sub test()
Dim MyString As String
MyString = OnlyString(ActiveCell)
Stop
End Sub


'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060904
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+'
' Note : Eg. =5+2 => '5+2' or =+B5+C3 => 'B5+C3'
' Function select always cell(1).
'----------------------------------------------------------
'
Function OnlyString(Rng As Range) As String
OnlyString = ""

With Rng.Cells(1)
If .HasFormula Then
If Mid(.Formula, 2, 1) = "+" Then
OnlyString = _
Mid(.Formula, 3, Len(.Formula) - 2)
Else
OnlyString = _
Mid(.Formula, 2, Len(.Formula) - 1)
End If
End If
End With
End Function
 
In VBA, mid() doesn't need that 3rd parameter if I want the rest of the string.
(The only thing that I can do by using it is screw it up--so I don't use it in
cases like these.)

I'd use something like:

Option Explicit
Function OnlyString(Rng As Range) As String
OnlyString = ""
Dim StartPos As Long

With Rng.Cells(1)
If .HasFormula Then
If Mid(.Formula, 2, 1) = "+" Then
StartPos = 3
Else
StartPos = 2
End If
OnlyString = Mid(.Formula, StartPos)
End If
End With
End Function

Then I only really have one expression to update if things change.


Joergen said:
Hi Dave.

I am very pleased for your advise, and I do hope, I do you credit with
below.
Thanks.

Sub test()
Dim MyString As String
MyString = OnlyString(ActiveCell)
Stop
End Sub

'----------------------------------------------------------
' Procedure : OnlyString
' Date : 20060904
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Display Formula without leading '=' and
' leading '+'
' Note : Eg. =5+2 => '5+2' or =+B5+C3 => 'B5+C3'
' Function select always cell(1).
'----------------------------------------------------------
'
Function OnlyString(Rng As Range) As String
OnlyString = ""

With Rng.Cells(1)
If .HasFormula Then
If Mid(.Formula, 2, 1) = "+" Then
OnlyString = _
Mid(.Formula, 3, Len(.Formula) - 2)
Else
OnlyString = _
Mid(.Formula, 2, Len(.Formula) - 1)
End If
End If
End With
End Function
Click to expand...
 
Hi Dave.
Then I only really have one expression to update if things change. Naturally.

In VBA, mid() doesn't need that 3rd parameter
Click to expand...
This is news for me and you are of course right.

I have rearly appreciated you effort to give me basic knowledge of VBA
programming.
Thanks.
 
Somethings are style and some are personal choices.

But if the only thing you, er, I can do is mess it up, it's usually best not to
use it.

Joergen said:
Hi Dave.
Then I only really have one expression to update if things change. Naturally.

In VBA, mid() doesn't need that 3rd parameter
Click to expand...
This is news for me and you are of course right.

I have rearly appreciated you effort to give me basic knowledge of VBA
programming.
Thanks.
Click to expand...
 
Back
Top