visible entire formula

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi,
I wonder, if is possible follow automatic action on cells:
Multiplicate per index=1,5 each cell in range.
Result operation -> in each cells visible formula:
=1,5*value_before_multiplicate.

Can anyone assist ?
Regards
Mark
 
One way:

If you only want the values (for instance, if

A1: 2

and you want

A1: 3

Put 1.5 in a cell. Copy the cell. Select your range. Choose Paste
Special, selecting the Values and Multiply radio buttons.


If instead you have

A1: =B1+2

and you want

A1: =(B1 + 2)*1.5

Put 1.5 in a cell. Copy the cell. Select your range. Choose Paste
Special, selecting the Formulas and Multiply radio buttons.
 
In that case, put this in the worksheet code module (right-click on
the worksheet tab, choose View Code, paste the code in the window
that opens, then click the XL icon on the toolbar to return to XL):


Private Sub Worksheet_Change(byVal Target As Excel.Range)
If Not Intersect(Target, Range("A1:J100")) Is Nothing Then
Application.EnableEvents = False
Target.Value = Target.Value * 1.5
Application.EnableEvents = True
End If
End Sub

Adjust the input range ("A1:J100", here) to suit.
 
I couldn't tell from your response which version you wanted. If you
want to change the formula itself, try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1:J100")) Is Nothing Then
Application.EnableEvents = False
With Target
If .HasFormula Then
.Formula = "=(" & Mid(.Formula, 2) & ") * 1.5"
ElseIf IsNumeric(.Value) Then
.Formula = "=" & .Value & " * 1.5"
End If
End With
Application.EnableEvents = True
End If
End Sub

Note that this assumes all formulae are numeric or boolean.
 
Sincere thanks
That's the point!!
-----Original Message-----
I couldn't tell from your response which version you wanted. If you
want to change the formula itself, try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1:J100")) Is Nothing Then
Application.EnableEvents = False
With Target
If .HasFormula Then
.Formula = "=(" & Mid(.Formula, 2) & ") * 1.5"
ElseIf IsNumeric(.Value) Then
.Formula = "=" & .Value & " * 1.5"
End If
End With
Application.EnableEvents = True
End If
End Sub

Note that this assumes all formulae are numeric or boolean.


.
Click to expand...
 
Back
Top