Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
skip cells with zero values in chart (cells not empty) | Charts and Charting in Excel | |||
I want the autofill/drag function to skip empty cells | Excel Discussion (Misc queries) | |||
paste special / skip empty cells doesn't work!? | Excel Worksheet Functions | |||
Formula returns empty; chart plots zero; I want to skip | Charts and Charting in Excel | |||
How to skip cells with text strings. | Excel Discussion (Misc queries) |