Value Is Not Formula?

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

We estimate construction projects. Frequently, we count,
say, all the piping or millwork in a particular room, and
later see that there are a number of rooms exactly like
it, so we wish to multiply a series of values. To
simplify this, I wrote the following macro:

Sub ModifyFormula()
' ModifyFormula Macro
' Macro programmed by Kevin G. Sprinkel 2/10/04
'
' Keyboard Shortcut: Ctrl-M
'
Dim cell As Range
Dim strInputString As String
strInputString = InputBox("Enter formula to modify current
formula(e). Parentheses will be added.", _
"Modify formula(e) in selected cell range
(s).")

For Each cell In Selection
If cell.HasFormula Then
If Left(cell.Formula, 1) = "=" Then
cell.Formula = Mid(cell.Formula, 2)
End If
cell.Formula = "=(" & cell.Formula & ")" &
strInputString
End If
Next
End Sub

It works fine EXCEPT if the user has simply entered a
value. In other words, it behaves as follows:

Contents of Cell Result of Macro
===================================
=432 Works
=8*2/6 Works
432 Does Not Work

I guess the latter is not interpreted as a formula. Does
anyone have a suggestion as to how to modify the macro?

Thanks.

Kevin Sprinkel
 
hi
but what do you expect. This macro can only change formulas. You
checked this with the line
If cell.HasFormula Then

and 432 is not a formula. What i your expecte result for this? Maybe
the following will work for you (not tested though):

Sub ModifyFormula()
' ModifyFormula Macro
' Macro programmed by Kevin G. Sprinkel 2/10/04
'
' Keyboard Shortcut: Ctrl-M
'
Dim cell As Range
Dim strInputString As String
strInputString = InputBox("Enter formula to modify current
formula(e). Parentheses will be added.", _
"Modify formula(e) in selected cell range(s).")

For Each cell In Selection
If cell.HasFormula Then
If Left(cell.Formula, 1) = "=" Then
cell.Formula = Mid(cell.Formula, 2)
End If
cell.Formula = "=(" & cell.Formula & ")" & strInputString
else
cell.formula = "=(" & cell.value & ")" & strInputString
End If
Next
End Sub
 
Thank you for your response, Frank; it led me to the
answer.

I wasn't sure what to expect, because I wasn't sure of the
definition of "formula". For example, +6 or 6*12 evaluate
to a formula, but -6 does not. It appears that Excel
inserts an "=" before something like 6*12, so there's no
need to test the first character after determining it's a
formula. The final version is:

Sub ModifyFormula()
' ModifyFormula Macro
' Macro programmed by Kevin G. Sprinkel 2/10/04
'
' Keyboard Shortcut: Ctrl-M
'
Dim cell As Range
Dim strInputString As String
strInputString = InputBox("Enter formula to modify
currentformula(e). Parentheses will be added.", _
"Modify formula(e) in selected cell range
(s).")

For Each cell In Selection
If cell.HasFormula Then
cell.Formula = "=(" & Mid(cell.Formula, 2) & ")" &
strInputString
Else
cell.Formula = "=(" & cell.Value & ")" & strInputString
End If
Next

End Sub

Thanks for your help.

Auf Wiedersehn
Kevin Sprinkel
 
Back
Top