Skip empty and text cells
 

I have a range of cells that contain formulas with an
apostrophe in front. For example: '=SUM(A1:B1). I need to
evaluate the formulas. My code works with cells containing
examples like this, but returns errors if the cells are
empty or contain other text strings like "eeeee". I just
need to skip these. TIA. Jason
-----------------------------

Sub BringMyFormulasBack()

Dim cell As Range
Dim oldfrmla As String

For Each cell In Selection
oldfrmla = cell.Text
If Left(oldfrmla, 1) = Chr(39) Then
oldfrmla = Right(oldfrmla, Len(oldfrmla) - 1)
End If
cell.Formula = Evaluate(oldfrmla)
Next cell

End Sub

Skip empty and text cells
 

Hi Jason:

Can't you just use a test that checks to see if the first character of the
string is "="?

If Left(oldfrmla, 1) = "=" Then cell.Formula = Evaluate(oldfrmla)

Regards,

Vasant.

"Jason Morin" wrote in message
...
I have a range of cells that contain formulas with an
apostrophe in front. For example: '=SUM(A1:B1). I need to
evaluate the formulas. My code works with cells containing
examples like this, but returns errors if the cells are
empty or contain other text strings like "eeeee". I just
need to skip these. TIA. Jason
-----------------------------

Sub BringMyFormulasBack()

Dim cell As Range
Dim oldfrmla As String

For Each cell In Selection
oldfrmla = cell.Text
If Left(oldfrmla, 1) = Chr(39) Then
oldfrmla = Right(oldfrmla, Len(oldfrmla) - 1)
End If
cell.Formula = Evaluate(oldfrmla)
Next cell

End Sub

Skip empty and text cells
 

Jason,

If you move the line "cell.Formula = Evaluate(oldfrmla)" into the if block
(see my change),
the evaluation will only be attempted if the first character is and
apostrophe:

For Each cell In Selection
oldfrmla = cell.Text
If Left(oldfrmla, 1) = Chr(39) Then
oldfrmla = Right(oldfrmla, Len(oldfrmla) - 1)
cell.Formula = Evaluate(oldfrmla)
End If
Next cell

Jeff


"Jason Morin" wrote in message
...
I have a range of cells that contain formulas with an
apostrophe in front. For example: '=SUM(A1:B1). I need to
evaluate the formulas. My code works with cells containing
examples like this, but returns errors if the cells are
empty or contain other text strings like "eeeee". I just
need to skip these. TIA. Jason
-----------------------------

Sub BringMyFormulasBack()

Dim cell As Range
Dim oldfrmla As String

For Each cell In Selection
oldfrmla = cell.Text
If Left(oldfrmla, 1) = Chr(39) Then
oldfrmla = Right(oldfrmla, Len(oldfrmla) - 1)
End If
cell.Formula = Evaluate(oldfrmla)
Next cell

End Sub

Skip empty and text cells
 

Hi Jason,

What's up? Trying to hone those VBA skills to the same level as your
worksheet skills?

Here is one way

Dim cell As Range
Dim oldfrmla As String

For Each cell In Selection
If cell.HasFormula Then
oldfrmla = cell.Text
If Left(oldfrmla, 1) = Chr(39) Then
oldfrmla = Right(oldfrmla, Len(oldfrmla) - 1)
End If
cell.Formula = Evaluate(oldfrmla)
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jason Morin" wrote in message
...
I have a range of cells that contain formulas with an
apostrophe in front. For example: '=SUM(A1:B1). I need to
evaluate the formulas. My code works with cells containing
examples like this, but returns errors if the cells are
empty or contain other text strings like "eeeee". I just
need to skip these. TIA. Jason
-----------------------------

Sub BringMyFormulasBack()

Dim cell As Range
Dim oldfrmla As String

For Each cell In Selection
oldfrmla = cell.Text
If Left(oldfrmla, 1) = Chr(39) Then
oldfrmla = Right(oldfrmla, Len(oldfrmla) - 1)
End If
cell.Formula = Evaluate(oldfrmla)
Next cell

End Sub