Retrieve part of a calculation

[slowjam4]

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

 

[Stefi]

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

 

[Bernard Liengme]

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

 

[slowjam4]

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







- 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

 

[Bernard Liengme]

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

 

[slowjam4]

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













- Show quoted text -

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