Reputation: 1
Is there anyway to have my macro
start where I highlight or select an empty cell
instead of always putting data in the same range? I know how to edit the code itself just don't know if there is a different function I could use instead of setting a range in stone by using specific cell locations.
I want the macro to start where I highlight....whether it be a single cell or highlighting 4 or 5 and having the macro run inside those 4 or 5..
Sub macroforme() ' ' macrowork Macro '
Range("H19").Select
ActiveCell.FormulaR1C1 = "Legacy"
Range("H18").Select
ActiveCell.FormulaR1C1 = "Field Service"
Range("H17").Select
ActiveCell.FormulaR1C1 = "Supply Chain"
Range("H16").Select
ActiveCell.FormulaR1C1 = "Engineering"
Range("H15").Select
ActiveCell.FormulaR1C1 = "Sales"
Range("I15").Select
Columns("H:H").ColumnWidth = 11.71
Range("I15").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],Legacy)"
Range("I15").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],""Sales*"",C[-8])"
Range("I16").Select
ActiveCell.FormulaR1C1 = "sumif"
Range("I16").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],Engineering)"
Range("I16").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],""Engineering"",C[-8])"
Range("I17").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],""Supply Chain"",C[-8])"
Range("I18").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],""Field Service"",C[-8])"
Range("I19").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],""Legacy"",C[-8])"
Range("I20").Select
End Sub
Upvotes: 0
Views: 104
Reputation: 50283
Stick the code below in your Worksheet (In the Macro editor, double click the worksheet for which you want this code to execute and then paste it in).
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Offset(0, 0).FormulaR1C1 = "Legacy"
Target.Offset(-1, 0).FormulaR1C1 = "Field Service"
Target.Offset(-2, 0).FormulaR1C1 = "Supply Chain"
Target.Offset(-3, 0).FormulaR1C1 = "Engineering"
Target.Offset(-4, 0).FormulaR1C1 = "Sales"
Target.ColumnWidth = "11.71"
'Next Column
Target.Offset(-4, 1).FormulaR1C1 = "=SUMIF(C[-5],""Legacy*"",C[-8])"
Target.Offset(-3, 1).FormulaR1C1 = "=SUMIF(C[-5],""Engineering"",C[-8])"
Target.Offset(-2, 1).FormulaR1C1 = "=SUMIF(C[-5],""Supply Chain"",C[-8])"
Target.Offset(-1, 1).FormulaR1C1 = "=SUMIF(C[-5],""Field Service"",C[-8])"
Target.Offset(0, 1).FormulaR1C1 = "=SUMIF(C[-5],""Legacy"",C[-8])"
End Sub
This will do the same thing your hard-coded code will do, but it will execute on the double click of a cell. Target
in this example is the cell you double clicked. Offset moves how ever many Rows and Columns you specify. So in the line Target.Offset(-4, 1).FormulaR1C1 = "=SUMIF(C[-5],""Legacy*"",C[-8])"
It's saying move over 1 column and up 4 rows, then stick in the formula.
Upvotes: 1
Reputation: 3011
If you remove the select lines it would work on the currently selected cells (activecell)
so
activecell.value = "This is the current cell"
would type This is the current cell in the one you have selected.
How you deal with multiple cells selected depends on what you are trying to do as here you have a set number of actions to perform on a bunch of independent cell.
If you want to set all the cells selected to the same value you could use
selection.value = "These are the current cells"
Selection would also work with 1 cell selected for most things.
Upvotes: 0