Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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. "Joel" wrote: 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" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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 "Jeremy McClung" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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. "Jeremy McClung" wrote: 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. "Joel" wrote: 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" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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 "FSt1" wrote: 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 "Jeremy McClung" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
'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" wrote: 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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? "Joel" wrote: 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. "Jeremy McClung" wrote: 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. "Joel" wrote: 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" wrote: 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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" wrote in message ... 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? "Joel" wrote: 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. "Jeremy McClung" wrote: 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. "Joel" wrote: 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" wrote: 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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
I am not seeing the macro code.
"Otto Moehrbach" wrote: 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" wrote in message ... 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? "Joel" wrote: 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. "Jeremy McClung" wrote: 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. "Joel" wrote: 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" wrote: 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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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" wrote: 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? "Joel" wrote: 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. "Jeremy McClung" wrote: 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. "Joel" wrote: 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" wrote: 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? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
It worked. Thanks!
"Joel" wrote: 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" wrote: 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? "Joel" wrote: 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. "Jeremy McClung" wrote: 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. "Joel" wrote: 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" wrote: 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? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
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 "Jeremy McClung" wrote in message ... I am not seeing the macro code. "Otto Moehrbach" wrote: 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" wrote in message ... 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? "Joel" wrote: 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. "Jeremy McClung" wrote: 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. "Joel" wrote: 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" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |