How to loop over a multi-row/column range per column using Excel 2007 VBA?

1
votes

I'm looping to loop over a range with multiple rows and columns using Excel 2007 VBA.

I'm specifically trying to loop over a user selected range, and perform a calculation on each column in the range, and output the calculation two rows below each column.

1
vbaexcelexcel-2007

1 Answers

1
votes

You can retrieve the currently selected range by using

Application.Selection.Address

This will give you a range value (the Selection property returns a Range object) that will look something like "$B$4:$J$20".

Given you now have a range to work with, you can iterate across each column using something like:

For Each col In userSelectedRange.Columns

   ...

Next

The Columns property again returns a Range object that you can either iterate over further or perform other calculations on (your exact needs aren't too clear from your question).

To post the calculated result two rows above each column (e.g. a subtotal or similar), try using the Offset function:

Cells.Offset(-2, 0)

If you're able to provide more specifics around the sort of calculation you want, I may be able to add more detail into how you achieve it.