Macro help

[Jeremy McClung]

I need to enter a monthly dollar amount and have the amount multiply by 12
and display in the same cell. I cannot use a formula due to the circular
reference. Any help writing a macro that would do the calculation for me?

 

[Joel]

Rather than a macro us an axilary cell and the paste special value only in
orignal cell
fro example if you data is in A1
in G1 put the following
=12*A1

Then copy g1 and select cell A1. Then use pastespecial and select value

 

[Jeremy McClung]

The user is requesting to:
1.) enter in the number
2.) press enter and have that number * 12 displayed.

They can't do the copy and paste steps. There needs to be only those two
steps.

 

[FSt1]

hi
this will work...
Sub valuetest()
Dim r As Range
Set r = Range("A5")'change if needed
r = r * 12
End Sub

enter the value in the cell then run the macro. but i would use an input box.
Sub othertest()
Dim r As Range
Dim v As Long
Set r = Range("A5"))
v = InputBox("enter the value")
r.Value = v * 12
End Sub

run the macro and enter the value in the input box.

and if you input cell changed......
Sub anothertest()
Dim r As Range
Dim v As Long
Dim s As String
s = InputBox("enter the cell address")
Set r = Range(s)
v = InputBox("enter the value")
r.Value = v * 12
End Sub

regards
FSt1

 

[Joel]

Is there certain columns or ranges that this will apply to? The best way of
accomplishing this is with a worksheet change event. the other choice is to
have a button that the user presses after he enters the data. One issue is
after enter the active cell is one row below where the data was entered.

 

[FSt1]

hi
just dawned on me. dollar value.
if you use the input box, declare v as a double not a long.

sorry. not thinking fast enough.

Regards
FSt1

 

[Mike]

'You didn't say What cell / column / row this has to be in.
'I used an the inputbox's value * 12 and put the results in the
'activecell
Sub monthlyDollarAmount()
amount = InputBox("Enter monthly amonut.")
If Not IsNumeric(amount) Then Exit Sub
ActiveCell.Value = amount * 12
End Sub

 

[Jeremy McClung]

This will apply to a column. The user wants the calculation do be done as
soon as they press enter following the entry of the number. How would I use
a worksheet change event?

 

[Otto Moehrbach]

Jeremy
This little macro will do that for any entry in Column A. Change it as
necessary to work with your column. This a worksheet event macro and must
be placed in the sheet module of the pertinent sheet. To access that
module, right-click on the sheet tab, select View Code, and paste this macro
into that module. "X" out of the module to return to your sheet. Save the
file. HTH Otto

 

[Jeremy McClung]

I am not seeing the macro code.

Jeremy
This little macro will do that for any entry in Column A. Change it as
necessary to work with your column. This a worksheet event macro and must
be placed in the sheet module of the pertinent sheet. To access that
module, right-click on the sheet tab, select View Code, and paste this macro
into that module. "X" out of the module to return to your sheet. Save the
file. HTH Otto

 

[Joel]

This problem is going all over the place. Place code on a vba sheet page
(not module). If code is to run on sheet2, then right click tab on bottom of
worksheet (sheet2) and select view code. Then copy code below. If code is
needed on more than one sheet the same code has to be place in each sheet of
VBA.

Sub worksheet_change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo error_return
If Target.Column = 3 Then 'column C
If IsNumeric(Target) Then
Target = 12 * Target
End If
End If
error_return:
Application.EnableEvents = True

End Sub

 

[Jeremy McClung]

It worked. Thanks!

This problem is going all over the place. Place code on a vba sheet page
(not module). If code is to run on sheet2, then right click tab on bottom of
worksheet (sheet2) and select view code. Then copy code below. If code is
needed on more than one sheet the same code has to be place in each sheet of
VBA.

Sub worksheet_change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo error_return
If Target.Column = 3 Then 'column C
If IsNumeric(Target) Then
Target = 12 * Target
End If
End If
error_return:
Application.EnableEvents = True

End Sub

 

[Otto Moehrbach]

That's because I forgot to paste it in. Sorry. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Target.Value = 12 * Target.Value
Application.EnableEvents = True
End If
End Sub