I'm trying to set up a macro to function on a user-selected range (rather 
than hardcoding row/column references, or having a bunch of inputboxes to 
collect the reference info)

Getting the range is easy;

Dim TempRange as Range
Set TempRange = Selection
debug.print TempRange.Address(False, False)

which gives me (in my test scenario): B6:AD1463

My macro will need to process each column independently, and for each 
column, I will loop through each row and perform some actions.

I can use left/right/mid to pull apart B6:D14 into the component pieces 
(B..AD and 6..1463) but the code isn't elegant because I have to manually 
determine where the column reference ends and the row number begins. For 
example, AF2 vs A63 are the same length strings, so it requires extra logic 
to pull them apart correctly.

I'm sure there is a way to directly extract the column and row information 
(letting Excel figure out which characters are which), but I'm having trouble 
finding the correct syntax and/or applying it to a multicell range.

I appreciate your suggestions,
Keith

Lets say we have selected a single block.  This little macro will give the 
limits of the block:

Sub range_reporter2()
Dim r As Range
Dim s As String
Set r = Selection

nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)

nLastColumn = r.Columns.Count + r.Column - 1
MsgBox ("last column " & nLastColumn)

nFirstRow = r.Row
MsgBox ("first row " & nFirstRow)

nFirstColumn = r.Column
MsgBox ("first column " & nFirstColumn)
End Sub

You can setup loops to traverse the columns/rows, etc.
-- 
Gary''s Student - gsnu201003


"ker_01" wrote:

> I'm trying to set up a macro to function on a user-selected range (rather 
> than hardcoding row/column references, or having a bunch of inputboxes to 
> collect the reference info)
> 
> Getting the range is easy;
> 
> Dim TempRange as Range
> Set TempRange = Selection
> debug.print TempRange.Address(False, False)
> 
> which gives me (in my test scenario): B6:AD1463
> 
> My macro will need to process each column independently, and for each 
> column, I will loop through each row and perform some actions.
> 
> I can use left/right/mid to pull apart B6:D14 into the component pieces 
> (B..AD and 6..1463) but the code isn't elegant because I have to manually 
> determine where the column reference ends and the row number begins. For 
> example, AF2 vs A63 are the same length strings, so it requires extra logic 
> to pull them apart correctly.
> 
> I'm sure there is a way to directly extract the column and row information 
> (letting Excel figure out which characters are which), but I'm having trouble 
> finding the correct syntax and/or applying it to a multicell range.
> 
> I appreciate your suggestions,
> Keith

Excellent, thank you!

"Gary''s Student" wrote:

> Lets say we have selected a single block.  This little macro will give the 
> limits of the block:
> 
> Sub range_reporter2()
> Dim r As Range
> Dim s As String
> Set r = Selection
> 
> nLastRow = r.Rows.Count + r.Row - 1
> MsgBox ("last row " & nLastRow)
> 
> nLastColumn = r.Columns.Count + r.Column - 1
> MsgBox ("last column " & nLastColumn)
> 
> nFirstRow = r.Row
> MsgBox ("first row " & nFirstRow)
> 
> nFirstColumn = r.Column
> MsgBox ("first column " & nFirstColumn)
> End Sub
> 
> You can setup loops to traverse the columns/rows, etc.
> -- 
> Gary''s Student - gsnu201003
> 
> 
> "ker_01" wrote:
> 
> > I'm trying to set up a macro to function on a user-selected range (rather 
> > than hardcoding row/column references, or having a bunch of inputboxes to 
> > collect the reference info)
> > 
> > Getting the range is easy;
> > 
> > Dim TempRange as Range
> > Set TempRange = Selection
> > debug.print TempRange.Address(False, False)
> > 
> > which gives me (in my test scenario): B6:AD1463
> > 
> > My macro will need to process each column independently, and for each 
> > column, I will loop through each row and perform some actions.
> > 
> > I can use left/right/mid to pull apart B6:D14 into the component pieces 
> > (B..AD and 6..1463) but the code isn't elegant because I have to manually 
> > determine where the column reference ends and the row number begins. For 
> > example, AF2 vs A63 are the same length strings, so it requires extra logic 
> > to pull them apart correctly.
> > 
> > I'm sure there is a way to directly extract the column and row information 
> > (letting Excel figure out which characters are which), but I'm having trouble 
> > finding the correct syntax and/or applying it to a multicell range.
> > 
> > I appreciate your suggestions,
> > Keith

On Fri, 14 May 2010 09:53:01 -0700, ker_01
<ker01@discussions.microsoft.com> wrote:

>I'm trying to set up a macro to function on a user-selected range (rather 
>than hardcoding row/column references, or having a bunch of inputboxes to 
>collect the reference info)
>
>Getting the range is easy;
>
>Dim TempRange as Range
>Set TempRange = Selection
>debug.print TempRange.Address(False, False)
>
>which gives me (in my test scenario): B6:AD1463
>
>My macro will need to process each column independently, and for each 
>column, I will loop through each row and perform some actions.
>
>I can use left/right/mid to pull apart B6:D14 into the component pieces 
>(B..AD and 6..1463) but the code isn't elegant because I have to manually 
>determine where the column reference ends and the row number begins. For 
>example, AF2 vs A63 are the same length strings, so it requires extra logic 
>to pull them apart correctly.
>
>I'm sure there is a way to directly extract the column and row information 
>(letting Excel figure out which characters are which), but I'm having trouble 
>finding the correct syntax and/or applying it to a multicell range.
>
>I appreciate your suggestions,
>Keith

Here is an example macro that you can adapt to your needs:

Sub sum_squares_in_selection()
    ' if you want the range to be processed column by column, try this
    s = 0
    For i = 1 To Selection.Rows.Count
      For j = 1 To Selection.Columns.Count
        ' do your stuff here, summing squares is just an example
        s = s + Selection(i, j).Value ^ 2
      Next j
    Next i
    MsgBox "Sum of squares is: " & s
    
    ' if you don't mind if the range is processed row by row, try this
    s = 0
    For Each c In Selection
      ' do your stuff here, summing squares is just an example
      s = s + c.Value ^ 2
    Next
    MsgBox "Sum of squares is: " & s
End Sub

Hope this helps / Lars-�ke

Another way...

Dim myRng as range
Dim myArea as Range
dim myRow as range
dim myCell as range

set myrng = Selection

for each myArea in myRng.areas 'in case of multiple areas
    for each myrow in myarea.rows
       for each mycell in myrow.cells
          msgbox mycell.value & vblf & mycell.address
       next mycell
    next myrow
next myarea

=======
And if you don't want to rely on the selection.

Dim myRng as Range

set myrng = nothing
on error resume next
set myrng = application.inputbox(prompt:="Select a range", type:=8)
on error goto 0

if myrng is nothing then
   'user hit cancel
   exit sub '???
end if

for each myarea in myrng.areas
   ...



On 05/14/2010 11:53, ker_01 wrote:
> I'm trying to set up a macro to function on a user-selected range (rather
> than hardcoding row/column references, or having a bunch of inputboxes to
> collect the reference info)
>
> Getting the range is easy;
>
> Dim TempRange as Range
> Set TempRange = Selection
> debug.print TempRange.Address(False, False)
>
> which gives me (in my test scenario): B6:AD1463
>
> My macro will need to process each column independently, and for each
> column, I will loop through each row and perform some actions.
>
> I can use left/right/mid to pull apart B6:D14 into the component pieces
> (B..AD and 6..1463) but the code isn't elegant because I have to manually
> determine where the column reference ends and the row number begins. For
> example, AF2 vs A63 are the same length strings, so it requires extra logic
> to pull them apart correctly.
>
> I'm sure there is a way to directly extract the column and row information
> (letting Excel figure out which characters are which), but I'm having trouble
> finding the correct syntax and/or applying it to a multicell range.
>
> I appreciate your suggestions,
> Keith