Trying to work with User-selected non-contiguous ranges

[Tristan]

Evening all,

As always what I thought would be a nice bit of mental exercise has
evolved into some monster that delights in my frustration and on-coming
headache .

What my little bit of code is trying to do is as follows:

1. The user selects the cells (using the standard Ctrl button and
mouse-click method) which are to be printed in a pre-constructed labels
document in Word

2. The code executes when a control button is activated and registers
the selected cells as an array using the selection object(?) and then
cycles through the array members adding each to its own label in the
document.

Does that make any sense?
Basically I'm trying to turn selected cells into an array and process
them that way.

Well, to my delight the code worked perfectly until I tried
selecting just one cell (i.e. one record) to print.
It seems that VBA won't recognise one-selected cell as an array .
Further testing proved that a non-contiguous selection of cells also
won't be recognised as an array .

I'm at a bit of a loss now and its not a facility I can do without.
Any help will be greatly appreciated.

Thanks Tris

Selection of code below:

Private Sub CommandButton1_Click()

Dim Outputs As Variant

Dim i As Integer

Record_a = Selection.Value

Dim Word As Word.Application
Set Word = New Word.Application

With Word
..ScreenUpdating = False
..Documents.Open Filename:="C:\Documents and
Settings\Label1.doc"
..Visible = True

For i = LBound(Record_a) To UBound(Record_a)
With .Selection
..TypeText Text:=Record_a(i, 1)
..MoveRight unit:=wdCell, Count:=1
End With
Next i

..ScreenUpdating = True

End With

Set Word = Nothing
End Sub

 

[Peter T]

Hi Tristan,

No idea what your macro is doing but see if this makes sense

Sub test()
Dim Record_a As Variant
Dim rng As Range
Dim ar As Range
Dim rw As Long, cl As Long

Set rng = Selection
For Each ar In rng.Areas
If ar.Count = 1 Then
ReDim Record_a(1 To 1, 1 To 1)
Record_a(1, 1) = ar(1).Value
Else
Record_a = ar.Value
End If

For rw = 1 To UBound(Record_a)
For cl = 1 To UBound(Record_a, 2)
Debug.Print Record_a(rw, cl)
Next
Next
Next
End Sub

Regards,
Peter T

 

[Tristan]

Hi Peter

I've used your code and it does exactely what I was trying to do thuogh
I freely admit I don't quite know why.

I haven't come across using the areas collection before and I'm still
can't work out how your macro cycles through the single cells.

What is "ar" exactly? It's dimmed as a range but is never assigned a
range as far as I can make out. It has a count property...is it a
collection within the rng.areas collection??? hmmm. Maybe ar stands for
array.

I'm going to have to go and sit in a darkened room to think about it.

Huge thanks however, I was never going to be able to work this one by
myself.

Ta, Tris

 

[Peter T]

Hi Triston

An 'area' is a single block of one or more cells in the selection. If the
selection is a single cell it also has one area and the area contains the
single cell.

can't work out how your macro cycles through the single cells.

It doesn't, it loops through areas, which may or may not be a single cell.
'ar' is an area, an area is a range.

The other problem you had was assigning values in a range to a variant. If
the range is a single cell the variant holds the value, it doesn't become an
array. But if you assign to a block of cells the variant becomes an array of
values same size as the block.

To ensure the variant is also an array when dealing with a single cell first
make it an array 1x1 array (with base 1), that's what the ReDim does. Then
assign the only element in the array with cell value. The only reason for
doing that is for consistency when it comes to processing later on (though I
probably wouldn't do it that way myself).

I'm sure if you sit in the darkened room long enough the light will shine!

Regards,
Peter T


'ar' is a range which gets assigned to each area (block of one or more
cells)