Value Is Not Formula?

[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

 

[Frank Kabel]

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

 

[Kevin Sprinkel]

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