Can I change this macro to only run on the selected call or cells?

[tonywatsonhelp]

Hi Everyone,

I have this macro that does some calculation for me, the problem is it run one line at a time on every visible row.

I was hoping we could change this to only run on the select cell or cells rows. even better if we could get it to trigger itself after a had added data and after I drag that data down the column.

Heres the code I'll add the parts I need edited into the code area.

Thanks

Tony​

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Miles()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim arResult() As Variant
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim cellMileSelect As Range
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim mColS As Integer
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim mColT As Integer
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim mMileSelect As Double
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim numOfCircuit As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]With s_Data[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    numOfCircuit =.Range("cellnum")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#b00000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    lRow =Cells(Rows.Count, "c").End(xlUp).Row[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#b00000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Set cellValue =Range("l5", Range("zz5")).Find("Value",LookAt:=xlWhole)
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Set cellMileSelect= Range("l5", Range("zz5")).Find("MileageSelected", LookAt:=xlWhole)
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    mColS =cellMileSelect.Column[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    n = lRow - 5[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#b00000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'ReDim PreservearResult(1 To 2, 1 To n)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    x = 0[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        For i = 1 To n [/COLOR][COLOR=#ff0000]  ''' NOW, this is the part I need changing as I should equal selection number of rows. any idea how I change this? and if I can get it to auto run.''''''[/COLOR][COLOR=#000000][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            If.Cells(i + 5, mColS).EntireRow.Hidden = False Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                mType= .Cells(i + 5, mColS - 2)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]            [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#b00000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                SetcellType = Range(Cells(5, "a"), Cells(5, "zz")).Find(mType,LookAt:=xlWhole)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                mColT= cellType.Column[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       [/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                mMiles= .Cells(i + 5, mColT)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]                mValue= .Cells(i + 5, mColS - 1)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                mTtlMile = .Cells(i + 5, "j")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               mMileSelect = mMiles * mValue[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                'arResult(1, i) = mMileSelect[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                mPct =mMileSelect / mTtlMile[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               'arResult(2, i) = mPct[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Application.EnableEvents = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                   .Cells(i + 5, mColS) = mMileSelect[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                   .Cells(i + 5, mColS + 1) = mPct[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                    x= x + 1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Application.EnableEvents = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]             End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Next i[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

 

[Joe4]

Take a look at the code at the bottom of this post that shows you how to loop through all rows in the selected range: https://www.ozgrid.com/forum/forum/help-forums/excel-general/70951-loop-through-selected-rows

If you want something to happen automatically against a range that you have selected, perhaps you can use a Worksheet_SelectionChange event procedure in VBA to do that.
See: http://www.globaliconnect.com/excel...on-change-event-excel-vba&catid=79&Itemid=475