ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Return formula minus = sign (https://www.excelbanter.com/excel-programming/372036-return-formula-minus-%3D-sign.html)

Patrick Simonds
Return formula minus = sign
 
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
Return formula minus = sign
 

Patrick Simonds wrote:
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
Return formula minus = sign
 
textbox93.text = mid(worksheets("Income").range("b4").formula,2)



Patrick Simonds wrote:

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

--

Dave Peterson

Joergen Bondesen
Return formula minus = sign
 
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



--
Best Regards
Joergen Bondesen


"Patrick Simonds" wrote in message
...
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

Dave Peterson
Return formula minus = sign
 
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 wrote:

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

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
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


--

Dave Peterson

Joergen Bondesen
Return formula minus = sign
 
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


--
Best regards
Joergen Bondesen


"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
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


--

Dave Peterson



Dave Peterson
Return formula minus = sign
 
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 wrote:

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

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
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


--

Dave Peterson

--

Dave Peterson

Joergen Bondesen
Return formula minus = sign
 
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


--
Best regards
Joergen Bondesen


"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
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


--

Dave Peterson

--

Dave Peterson



Dave Peterson
Return formula minus = sign
 
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 Bondesen wrote:

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

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
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


--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Joergen Bondesen
Return formula minus = sign
 
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.

--
Best Regards
Joergen Bondesen


"Dave Peterson" wrote in message
...
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 Bondesen wrote:

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

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
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


--

Dave Peterson

--

Dave Peterson

--

Dave Peterson



Dave Peterson
Return formula minus = sign
 
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 Bondesen wrote:

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.

--
Best Regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
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 Bondesen wrote:

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

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best regards
Joergen Bondesen

"Dave Peterson" wrote in message
...
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 wrote:

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

--
Best Regards
Joergen Bondesen

"Patrick Simonds" wrote in message
...
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


--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com