Excel Macros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Merritt Sakata - 24 May 2008 07:21 GMT

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!

Reply to this Message

Norman Jones - 24 May 2008 09:45 GMT

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

---
Regards.
Norman

> what is equivalent VBA to old macro
> keystrokes:{edit}{home}({end})*0{enter}
[quoted text clipped - 5 lines]
> function.
> thanks!

Reply to this Message

Merritt Sakata - 25 May 2008 00:09 GMT

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

> Hi Merit,
>
[quoted text clipped - 19 lines]
> > function.
> > thanks!

Reply to this Message

Rick Rothstein (MVP - VB) - 25 May 2008 19:04 GMT

> 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

Reply to this Message

Merritt Sakata - 26 May 2008 01:41 GMT

> > Its purpose is to result in a "zero value" calculated by
> > retaining the original expression in the cell, enclosed by
[quoted text clipped - 46 lines]
>
> 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

Reply to this Message

Merritt Sakata - 26 May 2008 01:41 GMT

> > Its purpose is to result in a "zero value" calculated by
> > retaining the original expression in the cell, enclosed by
[quoted text clipped - 46 lines]
>
> Rick

Reply to this Message