Retrieve part of a calculation
 

I would like to scan a range of cells and if there is a calculation in
the cell, such as =5000 - 200, I would like to replace the value of
the cell with what is immediately to the right of the equals sign. In
this example I would like the new value of the cell to be 5000. All of
the calculations will be in the format of =n1 - n2, I need to get the
value of n1 and place it in the cell. If there is not a calculation in
the cell, I want to leave it unchanged. Can someone give me the logic
to do this?

Thanks

Retrieve part of a calculation
 

On Mar 9, 3:26*pm, slowjam4 wrote:
I would like to scan a range of cells and if there is a calculation in
the cell, such as =5000 - 200, I would like to replace the value of
the cell with what is immediately to the right of the equals sign. In
this example I would like the new value of the cell to be 5000. All of
the calculations will be in the format of =n1 - n2, I need to get the
value of n1 and place it in the cell. If there is not a calculation in
the cell, I want to leave it unchanged. Can someone give me the logic
to do this?

Thanks

Check activecell.HasFormula and change activecell.Formula!
Regards,
Stefi

Retrieve part of a calculation
 

Sub Tryme()
Set myRange = Range("A1:A100")
For Each mycell In myRange
If mycell.HasFormula Then
myForm = mycell.Formula
minusChar = InStr(myForm, "-")
myValue = CLng(Mid(myForm, 2, minusChar - 2))
mycell.Offset(columnoffset:=1) = myValue
Else
mycell.Offset(columnoffset:=1) = mycell.Value
End If
Next
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"slowjam4" wrote in message
...
I would like to scan a range of cells and if there is a calculation in
the cell, such as =5000 - 200, I would like to replace the value of
the cell with what is immediately to the right of the equals sign. In
this example I would like the new value of the cell to be 5000. All of
the calculations will be in the format of =n1 - n2, I need to get the
value of n1 and place it in the cell. If there is not a calculation in
the cell, I want to leave it unchanged. Can someone give me the logic
to do this?

Thanks

Retrieve part of a calculation
 

On Mar 9, 10:21*am, "Bernard Liengme"
wrote:
Sub Tryme()
*Set myRange = Range("A1:A100")
*For Each mycell In myRange
* If mycell.HasFormula Then
* *myForm = mycell.Formula
* *minusChar = InStr(myForm, "-")
* *myValue = CLng(Mid(myForm, 2, minusChar - 2))
* *mycell.Offset(columnoffset:=1) = myValue
* Else
* *mycell.Offset(columnoffset:=1) = mycell.Value
* End If
*Next
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme

"slowjam4" wrote in message

...



I would like to scan a range of cells and if there is a calculation in
the cell, such as =5000 - 200, I would like to replace the value of
the cell with what is immediately to the right of the equals sign. In
this example I would like the new value of the cell to be 5000. All of
the calculations will be in the format of =n1 - n2, I need to get the
value of n1 and place it in the cell. If there is not a calculation in
the cell, I want to leave it unchanged. Can someone give me the logic
to do this?

Thanks- Hide quoted text -

- Show quoted text -

This is close but not exactly what I want. Original cell values:

A1 =300 - 100 B1 =100 - 50
A2 =500 - 200 B2 =600 - 300
A3 =100 - 50 B3 =800 - 400

Result after running the macro:

A1 200 B1 300
A2 300 B2 500
A3 50 B3 100

It is leaving A1, A2, A3 unchanged and placing the values in B1, B2,
B3

I was hoping for the following after running the macro:

A1 300 B1 100
A2 500 B2 600
A3 100 B3 800

Retrieve part of a calculation
 

New version
Sub Tryme()
Set myrange = Range("A1:B10")

For Each mycell In myrange
If mycell.HasFormula Then
MsgBox mycell.Formula
myForm = mycell.Formula
minusChar = InStr(myForm, "-")
myValue = CLng(Mid(myForm, 2, minusChar - 2))
mycell.Value = myValue
MsgBox mycell
Else
mycell.Value = mycell.Value
End If
Next
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"slowjam4" wrote in message
...
On Mar 9, 10:21 am, "Bernard Liengme"
wrote:
Sub Tryme()
Set myRange = Range("A1:A100")
For Each mycell In myRange
If mycell.HasFormula Then
myForm = mycell.Formula
minusChar = InStr(myForm, "-")
myValue = CLng(Mid(myForm, 2, minusChar - 2))
mycell.Offset(columnoffset:=1) = myValue
Else
mycell.Offset(columnoffset:=1) = mycell.Value
End If
Next
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme

"slowjam4" wrote in message

...



I would like to scan a range of cells and if there is a calculation in
the cell, such as =5000 - 200, I would like to replace the value of
the cell with what is immediately to the right of the equals sign. In
this example I would like the new value of the cell to be 5000. All of
the calculations will be in the format of =n1 - n2, I need to get the
value of n1 and place it in the cell. If there is not a calculation in
the cell, I want to leave it unchanged. Can someone give me the logic
to do this?

Thanks- Hide quoted text -

- Show quoted text -

This is close but not exactly what I want. Original cell values:

A1 =300 - 100 B1 =100 - 50
A2 =500 - 200 B2 =600 - 300
A3 =100 - 50 B3 =800 - 400

Result after running the macro:

A1 200 B1 300
A2 300 B2 500
A3 50 B3 100

It is leaving A1, A2, A3 unchanged and placing the values in B1, B2,
B3

I was hoping for the following after running the macro:

A1 300 B1 100
A2 500 B2 600
A3 100 B3 800

Retrieve part of a calculation
 

On Mar 9, 5:07*pm, "Bernard Liengme"
wrote:
New version
Sub Tryme()
*Set myrange = Range("A1:B10")

*For Each mycell In myrange
* If mycell.HasFormula Then
* *MsgBox mycell.Formula
* *myForm = mycell.Formula
* *minusChar = InStr(myForm, "-")
* *myValue = CLng(Mid(myForm, 2, minusChar - 2))
* *mycell.Value = myValue
* *MsgBox mycell
* Else
* *mycell.Value = mycell.Value
* End If
*Next
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme

"slowjam4" wrote in message

...



On Mar 9, 10:21 am, "Bernard Liengme"
wrote:
Sub Tryme()
*Set myRange = Range("A1:A100")
*For Each mycell In myRange
* If mycell.HasFormula Then
* *myForm = mycell.Formula
* *minusChar = InStr(myForm, "-")
* *myValue = CLng(Mid(myForm, 2, minusChar - 2))
* *mycell.Offset(columnoffset:=1) = myValue
* Else
* *mycell.Offset(columnoffset:=1) = mycell.Value
* End If
*Next
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme

"slowjam4" wrote in message

....

I would like to scan a range of cells and if there is a calculation in
the cell, such as =5000 - 200, I would like to replace the value of
the cell with what is immediately to the right of the equals sign. In
this example I would like the new value of the cell to be 5000. All of
the calculations will be in the format of =n1 - n2, I need to get the
value of n1 and place it in the cell. If there is not a calculation in
the cell, I want to leave it unchanged. Can someone give me the logic
to do this?

Thanks- Hide quoted text -

- Show quoted text -

This is close but not exactly what I want. Original cell values:

A1 =300 - 100 * * * B1 =100 - 50
A2 =500 - 200 * * * B2 =600 - 300
A3 =100 - 50 * * * * B3 =800 - 400

Result after running the macro:

A1 200 * * *B1 300
A2 300 * * *B2 500
A3 50 * * * *B3 100

It is leaving A1, A2, A3 unchanged and placing the values in B1, B2,
B3

I was hoping for the following after running the macro:

A1 300 *B1 100
A2 500 *B2 *600
A3 100 *B3 *800- Hide quoted text -

- Show quoted text -

That worked exactly like I wanted it to. Thanks for the help.