Logic Erroe in VBA Code Attached

[Dennis]

The following code works except for the IF clause.

The idea is to select a range of cells on the active worksheet.

Those selected cells contain formulas that are to be encapsulated by another
formula.

Using the VBA Debugger in Excel 2003, I noted that "Range" in the Dim
statement is not in Blue print as are the others like "String."

The point of my concern is that the value for myCellRange is "Nothing"
according to the debugger.

Therefore, the MsgBox and Exit occur.

If I comment out the If clause, the Macro works unless I do not select cells
with formulas in them.

My idea was to trap that error with the IF clause.

Why is the value of myCellRange always "Nothing" even when cells are selected?

How can I trap the non selection of cells error yet still have the macro work?

TIA Dennis

Sub ReplaceFormula()

Dim CellFormula As String
Dim myCellRange As Range
Dim Placeholder As String
Dim NewFormula As String
If myCellRange Is Nothing Then MsgBox "No Cells were selected!"
If myCellRange Is Nothing Then
Exit Sub
Else
NewFormula = Application.InputBox("Enter the New Formula _
with a Placeholder for the Cell Formula. _
(Start with a single quote like '=X+Y)")
Placeholder = Application.InputBox("What variable in the New _
Formula, entered in the previous Screen, is to be replaced _
with Cell Formula?")
For Each myCellRange In Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
CellFormula = Mid(myCellRange.Formula, 2, _
Len(myCellRange.Formula)) _
myCellRange.Formula = Mid(Replace(NewFormula, _
Placeholder, CellFormula), 2)
Next myCellRange
End If

End Sub

 

[Jim Cone]

Dennis,

When you "Dim" a range object, you are simply reserving room for it.
You must then "Set" the range so Excel knows what it is.
In a loop, the "setting" is automatically done by the Excel.

Input boxes come in two flavors:
Application.InputBox - can return a range object
InputBox - returns a string

If the user cancels the Input Box, you must have some way to
exit the sub.
Each line of text in an InputBox must be enclosed
with quote marks.

The following modified code should get you going.
The formula replace section may need some additional work.
'--------------------------------------------------------------
Sub ReplaceFormula()

Dim MyCell As Range
Dim CellRange As Range
Dim CellFormula As String
Dim Placeholder As String
Dim NewFormula As String

Set CellRange = Selection
If CellRange Is Nothing Then
MsgBox "No Cells were selected!"
Exit Sub
Else
NewFormula = InputBox("Enter the New Formula" & vbNewLine & _
"with a Placeholder for the Cell Formula." & vbNewLine & _
"( Start with a single quote like =X+Y) )", " Blame Dennis")
If Len(NewFormula) = 0 Then
Set CellRange = Nothing
Exit Sub
Else
Placeholder = InputBox("What variable in the New Formula," & vbNewLine &
_
"entered in the previous Screen," & vbNewLine & _
"is to be replaced with Cell Formula?", " Blame Dennis")
End If
If Len(Placeholder) = 0 Then
Set CellRange = Nothing
Exit Sub
Else
For Each MyCell In CellRange.SpecialCells(xlCellTypeFormulas)
CellFormula = Mid(MyCell.Formula, 2, Len(MyCell.Formula))
MyCell.Formula = Mid(Replace(NewFormula, Placeholder, CellFormula),
2)
Next 'MyCell
End If
End If

Set MyCell = Nothing
Set CellRange = Nothing
End Sub
'-------------------------------------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[Dennis]

Jim thanks for your time - especially in explaining the underling logic.

Dennis