Working with the current selection

[Keith Wilby]

Bit of a newbie question here.

I've written a bit of VBA that I want to work over whatever column of cells
I have selected. For example, I might want to run it on A1 to A8 but on
another occasion I might want it to run on A9 to A20. I currently input the
range via input boxes, could anyone advise on the syntax I should use to use
the currently selected cells?

Many thanks.

 

[Keith Wilby]

Bit of a newbie question here.

I've written a bit of VBA that I want to work over whatever column of
cells I have selected. For example, I might want to run it on A1 to A8
but on another occasion I might want it to run on A9 to A20. I currently
input the range via input boxes, could anyone advise on the syntax I
should use to use the currently selected cells?

Many thanks.

Sorry, here's my code:

Sub libTotals()

Dim intFirstCell As Integer, intLastCell As Integer, intPeriodCode As
Integer

intFirstCell = InputBox("What's the first row number?", "Enter first row
number")
intLastCell = InputBox("What's the last row number?", "Enter last row
number")
intPeriodCode = InputBox("What's the period code?", "Enter the period code")

Dim n As Integer, lngTotal As Long
n = intFirstCell - 1

Do Until n = intLastCell
n = n + 1
Cells(n, 10).Select
If Cells(n, 10).Value = intPeriodCode Then
lngTotal = lngTotal + Cells(n, 7)
End If
Loop

MsgBox lngTotal

End Sub

 

[p45cal]

There are several ways:
1. Least change to code:
(a)Replace

Code
-------------------
intFirstCell = InputBox("What's the first row number?", "Enter first row Number "
-------------------

with

Code
-------------------
intFirstCell = Selection.Ro
-------------------

(b)replace

Code
-------------------
intLastCell = InputBox("What's the last row number?", "Enter last row Number")
-------------------

with

Code
-------------------
intLastCell = selection.row+selection.rows.count-
-------------------

Be sure to select only one area at a time.

2. Change the code
Entire code:

Code
-------------------
Sub libTotals()
Dim intPeriodCode As Long, lngTotal As Long, rw
intPeriodCode = InputBox("What's the period code?", "Enter the period code")
For Each rw In Selection.EntireRow.Rows
If rw.Cells(10).Value = intPeriodCode Then lngTotal = lngTotal + rw.Cells(7)
Next rw
MsgBox lngTotal
End Sub

-------------------

This (2) will work on the *rows* that have been selected, regardless o
which columns are included in the selection. It will even work if yo
have selected multiple areas (though be aware that if you've selecte
the same rows twice in different areas, it will process those row
twice.. keep it simple - only select one area)

 

[Keith Wilby]

There are several ways:

You sir/madam are a star, many thanks indeed