J
Jim
Can I record a macro and have the same macro run in cells
I select?
Thanks,
Jim
I select?
Thanks,
Jim
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Don Guillett
Probably would need to modify the range shown on the recording to selection
or use an input box, etc.
or use an input box, etc.
J
Jim
Thanks for responding Don. How do I modify the range
to "selection"? I know how to edit the macro but I'm not
sure of the correct command. I know just enough to be
dangerous.
Thanks
to "selection"? I know how to edit the macro but I'm not
sure of the correct command. I know just enough to be
dangerous.
Thanks
O
Otto Moehrbach
Jim
You'll have to post the macro so we'll know what you are trying to do.
Post just the macro, not the file. HTH Otto
You'll have to post the macro so we'll know what you are trying to do.
Post just the macro, not the file. HTH Otto
J
Jim
I have 11 separate plans that I wan't to run in different
locations in my spreadsheet depending on the needs of my
client. I need to design so that all 11 macros can be run
in any selected cell. The ideal would be to tie the macro
to a drop down list that would run any one of the selected
options in any of 5 columns. The sample macro for one plan
option follows:
Appreciate your help.
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/29/2003 by
'
'
Range("G29").Select
ActiveCell.FormulaR1C1 = "C-1"
Range("G31").Select
ActiveCell.FormulaR1C1 = "HEALTHLINK"
Range("G33").Select
ActiveCell.FormulaR1C1 = "5000000"
Range("G34").Select
ActiveCell.FormulaR1C1 = "5000000"
Range("G36").Select
ActiveCell.FormulaR1C1 = "250"
Range("G37").Select
ActiveCell.FormulaR1C1 = "750"
Range("G38").Select
ActiveCell.FormulaR1C1 = "3"
Range("G40").Select
ActiveCell.FormulaR1C1 = "90%"
Range("G41").Select
ActiveCell.FormulaR1C1 = "70%"
Range("G44").Select
ActiveCell.FormulaR1C1 = "10000"
Range("G45").Select
ActiveCell.FormulaR1C1 = "10000"
Range("G46").Select
ActiveCell.FormulaR1C1 = "C-1"
Range("G48").Select
ActiveCell.FormulaR1C1 = "1250"
Range("G49").Select
ActiveCell.FormulaR1C1 = "3750"
Range("G51").Select
ActiveCell.FormulaR1C1 = "2500"
Range("G52").Select
ActiveCell.FormulaR1C1 = "7500"
Range("G53").Select
ActiveCell.FormulaR1C1 = "15"
Range("G54").Select
ActiveCell.FormulaR1C1 = "15"
Range("G55").Select
ActiveCell.FormulaR1C1 = "$15/25/40"
Range("G56").Select
ActiveCell.FormulaR1C1 = "15000"
Range("G57").Select
ActiveCell.FormulaR1C1 = "0"
Range("G58").Select
ActiveCell.FormulaR1C1 = "$10 Co-Pay"
Range("G59").Select
ActiveCell.FormulaR1C1 = "YES"
Range("G60").Select
ActiveCell.FormulaR1C1 = "OPTIONAL"
Range("G61").Select
End Sub
locations in my spreadsheet depending on the needs of my
client. I need to design so that all 11 macros can be run
in any selected cell. The ideal would be to tie the macro
to a drop down list that would run any one of the selected
options in any of 5 columns. The sample macro for one plan
option follows:
Appreciate your help.
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/29/2003 by
'
'
Range("G29").Select
ActiveCell.FormulaR1C1 = "C-1"
Range("G31").Select
ActiveCell.FormulaR1C1 = "HEALTHLINK"
Range("G33").Select
ActiveCell.FormulaR1C1 = "5000000"
Range("G34").Select
ActiveCell.FormulaR1C1 = "5000000"
Range("G36").Select
ActiveCell.FormulaR1C1 = "250"
Range("G37").Select
ActiveCell.FormulaR1C1 = "750"
Range("G38").Select
ActiveCell.FormulaR1C1 = "3"
Range("G40").Select
ActiveCell.FormulaR1C1 = "90%"
Range("G41").Select
ActiveCell.FormulaR1C1 = "70%"
Range("G44").Select
ActiveCell.FormulaR1C1 = "10000"
Range("G45").Select
ActiveCell.FormulaR1C1 = "10000"
Range("G46").Select
ActiveCell.FormulaR1C1 = "C-1"
Range("G48").Select
ActiveCell.FormulaR1C1 = "1250"
Range("G49").Select
ActiveCell.FormulaR1C1 = "3750"
Range("G51").Select
ActiveCell.FormulaR1C1 = "2500"
Range("G52").Select
ActiveCell.FormulaR1C1 = "7500"
Range("G53").Select
ActiveCell.FormulaR1C1 = "15"
Range("G54").Select
ActiveCell.FormulaR1C1 = "15"
Range("G55").Select
ActiveCell.FormulaR1C1 = "$15/25/40"
Range("G56").Select
ActiveCell.FormulaR1C1 = "15000"
Range("G57").Select
ActiveCell.FormulaR1C1 = "0"
Range("G58").Select
ActiveCell.FormulaR1C1 = "$10 Co-Pay"
Range("G59").Select
ActiveCell.FormulaR1C1 = "YES"
Range("G60").Select
ActiveCell.FormulaR1C1 = "OPTIONAL"
Range("G61").Select
End Sub
D
Don Guillett
On the worksheet in a cell somewhere with calculation set to automatic
=now()
that will force the worksheet to calculate
right click>view code>insert this>modify to suit>SAVE
As written, if data validation in cell B1 then x will be the column desired.
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
On Error GoTo quitit
'=====
x = [b1] 'column number desired
Cells(4, x) = 1 'row 4, column x if 3 the col C
Cells(5, x) = 2
Cells(6, x) = 3
'========
quitit:
Application.EnableEvents = True
End Sub
BTW As you had it, you don't need the selections.
Range("G29").Formula = "C-1"
=now()
that will force the worksheet to calculate
right click>view code>insert this>modify to suit>SAVE
As written, if data validation in cell B1 then x will be the column desired.
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
On Error GoTo quitit
'=====
x = [b1] 'column number desired
Cells(4, x) = 1 'row 4, column x if 3 the col C
Cells(5, x) = 2
Cells(6, x) = 3
'========
quitit:
Application.EnableEvents = True
End Sub
BTW As you had it, you don't need the selections.
can be changed toRange("G29").Select
ActiveCell.FormulaR1C1 = "C-1"
Range("G29").Formula = "C-1"
O
Otto Moehrbach
Jim
Is it that you have some set pattern of cells referenced to some one
cell? And that one cell can change to another cell and then you want to
repeat the macro to work on the same pattern but referenced to this other
cell? Is that what you want to do?
It appears that you recorded this macro with absolute references, and
from what you say you need to record the macro in relative reference.
Also, the macro recorder unfortunately records exactly what you do.
This produces a macro that works but the macro is very awkward. Let me give
you some alternate ways of writing the code you want.
Let's take the two lines:
Range("G29").Select
ActiveCell.FormulaR1C1 = "C-1"
What you want to happen is that "C-1" gets put into cell G29. Here are two
ways of doing the same thing:
Range("G29") = "C-1"
Or
[G29] = "C-1"
Notice that cell G29 is never selected. In VBA you don't have to select a
cell to do something with it. When you do it manually you do have to select
it. And that's what you did and that's what the macro recorder recorded.
The macro recorder recorded some 40 lines of code. You can do the same
thing in 20 lines.
Now if you have some pattern of cells with one reference cell, say G29,
then you can write the code like this:
[G29].Offset(2,0) = "HEALTHLINK"
The "[G29].Offset(2,0)" is cell G31.
"[G29].Offset(2,2)" is cell I31.
"[G29].Offset(-2,-2)" is cell E27.
What you say about the 11 plans, the drop-down list, and the five columns is
fairly easy to do. I would need to see the 11 plans and how they relate to
each other (same pattern - different values?) to see if a single looping
macro referencing different value tables can be set up.
Is the pattern of cells the same for each plan?
If you wish, send me a file showing all 11 plans and the cell pattern and
values for each plan. Also tell me how the 5 columns play into this. Send
the file direct to me. Don't post a file attachment in these newsgroups.
Remove "hello" from my email address or it will go nowhere. HTH Otto
Is it that you have some set pattern of cells referenced to some one
cell? And that one cell can change to another cell and then you want to
repeat the macro to work on the same pattern but referenced to this other
cell? Is that what you want to do?
It appears that you recorded this macro with absolute references, and
from what you say you need to record the macro in relative reference.
Also, the macro recorder unfortunately records exactly what you do.
This produces a macro that works but the macro is very awkward. Let me give
you some alternate ways of writing the code you want.
Let's take the two lines:
Range("G29").Select
ActiveCell.FormulaR1C1 = "C-1"
What you want to happen is that "C-1" gets put into cell G29. Here are two
ways of doing the same thing:
Range("G29") = "C-1"
Or
[G29] = "C-1"
Notice that cell G29 is never selected. In VBA you don't have to select a
cell to do something with it. When you do it manually you do have to select
it. And that's what you did and that's what the macro recorder recorded.
The macro recorder recorded some 40 lines of code. You can do the same
thing in 20 lines.
Now if you have some pattern of cells with one reference cell, say G29,
then you can write the code like this:
[G29].Offset(2,0) = "HEALTHLINK"
The "[G29].Offset(2,0)" is cell G31.
"[G29].Offset(2,2)" is cell I31.
"[G29].Offset(-2,-2)" is cell E27.
What you say about the 11 plans, the drop-down list, and the five columns is
fairly easy to do. I would need to see the 11 plans and how they relate to
each other (same pattern - different values?) to see if a single looping
macro referencing different value tables can be set up.
Is the pattern of cells the same for each plan?
If you wish, send me a file showing all 11 plans and the cell pattern and
values for each plan. Also tell me how the 5 columns play into this. Send
the file direct to me. Don't post a file attachment in these newsgroups.
Remove "hello" from my email address or it will go nowhere. HTH Otto
S
sheryl
try making the selection BEFORE you record the macro.
later, when you want to run the macro on a different
selection, simply highlight the new selection and then re-
run the macro.
it should work.
later, when you want to run the macro on a different
selection, simply highlight the new selection and then re-
run the macro.
it should work.