Return formula minus = sign

[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

 

[titus]

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

try using this:

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

 

[Dave Peterson]

textbox93.text = mid(worksheets("Income").range("b4").formula,2)

 

[Joergen Bondesen]

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

 

[Dave Peterson]

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.

 

[Joergen Bondesen]

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.
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

 

[Dave Peterson]

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

 

[Joergen Bondesen]

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

 

[Dave Peterson]

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.

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

 

[Joergen Bondesen]

Hi Dave.

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

In VBA, mid() doesn't need that 3rd parameter

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.

 

[Dave Peterson]

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.

Hi Dave.

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

In VBA, mid() doesn't need that 3rd parameter

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.