CODEWITHSUNDEEP
vbaexcel
shane
shane

Reputation: 1

how to start my macro where i click rather than have set cells

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

Answers (2)

JNevill
JNevill

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

gtwebb
gtwebb

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

Related Questions