Excel Macros

[Merritt Sakata]

what is equivalent VBA to old macro keystrokes:{edit}{home}({end})*0{enter}
I want to simply enclose the contents (number or formula) of current/active
cell and multipy by zero. the keystroke recorder only will allow recording of
the actual contents as permanent text string or cell address thus does not
function.
thanks!

 

[Norman Jones]

Hi Merit,

I do not understand the purpose of multiplying
the cell content by 0,but, assuming a valid reason,
try something like:

With ActiveCell
.Value = ,Value * 0
End With

 

[Merritt Sakata]

Aloha Norman:

Mahalo (thanks). I tried the suggestion but it does not function as I had
hoped. I got an entry error when construction the macro which highlighted the
intial comma after the equal sign. The resulting cell condition on execution
for BOTH value or formula is simply a "0" in the cell. I tried it again with
".formula = formula *0" which also yields a "0" in the activecell.

The valid reason for the method is that I have an old estimating spreadsheet
and its is necessary to be able to KEEP the original contents whether formula
or value but to multiply by zero (or alternatively a referenced constant in
another cell). Its purpose is to result in a "zero value" calculated by
retaining the original expression in the cell, enclosed by a parenthesis and
multiplied by zero for "what if" iterations, yet retaining the original entry
for recovery later if necessary.

I believe that the suggested method is on the right track and had tried to
do something similar which did not work.

I had thought about a longer macro that first converts the original
expression to a string and then does an edit that deletes the apostrophe and
adjusts the equal signs if it necessary, but my command of the VBA is
insfficient.

I suppose that if there is no elegant solution, two different macros may be
needed to address the different "value" and "formula" conditions.

If this makes sense and you have a suggestion, I would appeciate it.

Mahalo from Hawaii!

Thanks!

allow the

 

[Rick Rothstein \(MVP - VB\)]

Its purpose is to result in a "zero value" calculated by

retaining the original expression in the cell, enclosed by
a parenthesis and multiplied by zero

What about something like this (works on all cells within a selection)....

Sub PreserveSelectedCells()
Dim CellInSelection As Range
For Each CellInSelection In Selection
With CellInSelection
If Len(.Formula) > 0 Then
If Left(.Formula, 1) = "=" Then
.Formula = Replace(.Formula, "=", "=N(", , 1) & ")*0"
Else
.Formula = "=N(""@" & .Formula & """)*0"
End If
End If
End With
Next
End Sub

Note the use of the N function so that the cell's evaluated content can be
numeric or text. In the case of a cell containing either a numeric constant
or text constant (that is, without a leading equal sign), an equal sign,
followed by the N function "housing" followed by a quote mark followed by
and @ symbol are prefixed to it and a quote mark followed by the N
function's closing parenthesis and then followed by the multiplication by
zero characters. This is done so the following subroutine will be able to
recognize the non-formula entry and take appropriate steps to be able to
reverse it. The down side to the above is a pure text entry ends up as a
numeric zero while it is "preserved".

The following subroutine can be used to restore the cell to its original
content and/or formula...

Sub RestoreSelectedCells()
Dim CellInSelection As Range
For Each CellInSelection In Selection
With CellInSelection
If .Formula Like "=N(""@*"")*0" Then
.Formula = Mid(.Formula, 6, Len(.Formula) - 9)
ElseIf .Formula Like "=N(*)*0" Then
.Formula = Replace("=" & Mid(.Formula, 4, _
Len(.Formula) - 6), "=#@", "")
End If
End With
Next
End Sub


Rick

 

[Merritt Sakata]

What about something like this (works on all cells within a selection)....

Sub PreserveSelectedCells()
Dim CellInSelection As Range
For Each CellInSelection In Selection
With CellInSelection
If Len(.Formula) > 0 Then
If Left(.Formula, 1) = "=" Then
.Formula = Replace(.Formula, "=", "=N(", , 1) & ")*0"
Else
.Formula = "=N(""@" & .Formula & """)*0"
End If
End If
End With
Next
End Sub

Note the use of the N function so that the cell's evaluated content can be
numeric or text. In the case of a cell containing either a numeric constant
or text constant (that is, without a leading equal sign), an equal sign,
followed by the N function "housing" followed by a quote mark followed by
and @ symbol are prefixed to it and a quote mark followed by the N
function's closing parenthesis and then followed by the multiplication by
zero characters. This is done so the following subroutine will be able to
recognize the non-formula entry and take appropriate steps to be able to
reverse it. The down side to the above is a pure text entry ends up as a
numeric zero while it is "preserved".

The following subroutine can be used to restore the cell to its original
content and/or formula...

Sub RestoreSelectedCells()
Dim CellInSelection As Range
For Each CellInSelection In Selection
With CellInSelection
If .Formula Like "=N(""@*"")*0" Then
.Formula = Mid(.Formula, 6, Len(.Formula) - 9)
ElseIf .Formula Like "=N(*)*0" Then
.Formula = Replace("=" & Mid(.Formula, 4, _
Len(.Formula) - 6), "=#@", "")
End If
End With
Next
End Sub


Rick

THANKS RICK!

I tried your method and as you described it inserted and retained the "N"
entry and quotation marks converting all to strings.

Since I would only have plain numbers or forumlas, I tried removing the
extra text
as noted below and it seemed to work as I had intended.

I really appreciate your suggestion.

thanks!

Sub PreserveSelectedCells()
'
' PreserveSelectedCells Macro
'

'
Dim CellInSelection As Range
For Each CellInSelection In Selection
With CellInSelection
If Len(.Formula) > 0 Then
If Left(.Formula, 1) = "=" Then
.Formula = Replace(.Formula, "=", "=(", , 1) & ")*0"
Else
.Formula = "=(" & .Formula & ")*0"
End If
End If
End With
Next
End Sub

 

[Merritt Sakata]

What about something like this (works on all cells within a selection)....

Sub PreserveSelectedCells()
Dim CellInSelection As Range
For Each CellInSelection In Selection
With CellInSelection
If Len(.Formula) > 0 Then
If Left(.Formula, 1) = "=" Then
.Formula = Replace(.Formula, "=", "=N(", , 1) & ")*0"
Else
.Formula = "=N(""@" & .Formula & """)*0"
End If
End If
End With
Next
End Sub

Note the use of the N function so that the cell's evaluated content can be
numeric or text. In the case of a cell containing either a numeric constant
or text constant (that is, without a leading equal sign), an equal sign,
followed by the N function "housing" followed by a quote mark followed by
and @ symbol are prefixed to it and a quote mark followed by the N
function's closing parenthesis and then followed by the multiplication by
zero characters. This is done so the following subroutine will be able to
recognize the non-formula entry and take appropriate steps to be able to
reverse it. The down side to the above is a pure text entry ends up as a
numeric zero while it is "preserved".

The following subroutine can be used to restore the cell to its original
content and/or formula...

Sub RestoreSelectedCells()
Dim CellInSelection As Range
For Each CellInSelection In Selection
With CellInSelection
If .Formula Like "=N(""@*"")*0" Then
.Formula = Mid(.Formula, 6, Len(.Formula) - 9)
ElseIf .Formula Like "=N(*)*0" Then
.Formula = Replace("=" & Mid(.Formula, 4, _
Len(.Formula) - 6), "=#@", "")
End If
End With
Next
End Sub


Rick

THANKS RICK!

I tried your method and as you described it inserted and retained the "N"
entry and quotation marks converting all to strings.

Since I would only have plain numbers or forumlas, I tried removing the
extra text
as noted below and it seemed to work as I had intended.

I really appreciate your suggestion.

thanks!

Sub PreserveSelectedCells()
'
' PreserveSelectedCells Macro
'

'
Dim CellInSelection As Range
For Each CellInSelection In Selection
With CellInSelection
If Len(.Formula) > 0 Then
If Left(.Formula, 1) = "=" Then
.Formula = Replace(.Formula, "=", "=(", , 1) & ")*0"
Else
.Formula = "=(" & .Formula & ")*0"
End If
End If
End With
Next
End Sub