Suppose the user has selected several non-contiguous groups of cells, and
then he pushes a button which calls the VBA routine "Demo_Routine" What
would the code of "Demo_routine" look like if it were to put, say, to put
the word "Hello" into every cell in each of the selected groups?

Thanks

--
-regards

selection = "Hello"

"GeorgeJ" wrote:


Suppose the user has selected several non-contiguous groups of cells, and
then he pushes a button which calls the VBA routine "Demo_Routine" What
would the code of "Demo_routine" look like if it were to put, say, to put
the word "Hello" into every cell in each of the selected groups?

Thanks

--
-regards

I selected several non-contiguous cells, then recorded the following
macro by typing "Hello" and doing CTRL-Enter:

Sub Test_Hello()
' Macro recorded 28/08/2007 by Pete Ashurst
Selection.FormulaR1C1 = "Hello"
End Sub

Hope this helps.

Pete

On Aug 27, 10:36 pm, GeorgeJ
wrote:
Suppose the user has selected several non-contiguous groups of cells, and
then he pushes a button which calls the VBA routine "Demo_Routine" What
would the code of "Demo_routine" look like if it were to put, say, to put
the word "Hello" into every cell in each of the selected groups?

Thanks

--
-regards

I'd like to thank both of you for helping me. Yes, what you suggest would do
what I request but my example badly stated what I was really after. What I
would like to know is, how would VBA know how many non-contiguous sections
were contained in the selection, and for each section, what are the top and
bottom rows and the left and right columns? Perhaps this question would have
been presented better if I had asked to have each cell in the selection have
the following information put into it ... "This cell in in row " & Row_ & "
and column " & Col_ & " of section number " & section & "."

My question is, how would I set up my loops to have the correct values of
Row_, Col_, and Section_ ?

Thank you

This is probably a dumb idea, and sketchy, but here's one approach. Use the
worksheet selection change event. When it fires, grab the currently active
cell and write that cell address to some "safe" location in your workbook -
sort of a permanent array of sorts - you're just building a list. You'll have
to use a LastUsedRow function so you don't overwrite the previous entry.

You could embed some conditional logic, so that before writing the value to
the list, it checks to see how that cell address value compares to the
previous one... and if the column value is not the same or the row address <
the last row address + one then you've got a new section... and you could
write that value to a new list location... etc.

When the user is done, they click some button to trigger another piece of
code that reads the lists you've built, looking for the top and bottom
rows... and you can use that to write whatever data you want back to the
original worksheet.

As I said, kind'a dopey, and others will certainly come up w/ more complete
and elegant solutions...

gt




"GeorgeJ" wrote:

I'd like to thank both of you for helping me. Yes, what you suggest would do
what I request but my example badly stated what I was really after. What I
would like to know is, how would VBA know how many non-contiguous sections
were contained in the selection, and for each section, what are the top and
bottom rows and the left and right columns? Perhaps this question would have
been presented better if I had asked to have each cell in the selection have
the following information put into it ... "This cell in in row " & Row_ & "
and column " & Col_ & " of section number " & section & "."

My question is, how would I set up my loops to have the correct values of
Row_, Col_, and Section_ ?

Thank you

GeorgeJ,

Here's a piece of code that might help you deal with multiple area selections. The columns
work the same way as the rows.

Sub Areas()
Dim AreaCount As Integer
AreaCount = 1
Dim MyArea As Range
For Each MyArea In Selection.Areas
MsgBox "Area " & AreaCount & ", top row is " & MyArea.Row & ". Bottom row is " &
MyArea.Row + MyArea.Rows.Count - 1 & "."
AreaCount = AreaCount + 1
Next MyArea
End Sub
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"GeorgeJ" wrote in message
...
I'd like to thank both of you for helping me. Yes, what you suggest would do
what I request but my example badly stated what I was really after. What I
would like to know is, how would VBA know how many non-contiguous sections
were contained in the selection, and for each section, what are the top and
bottom rows and the left and right columns? Perhaps this question would have
been presented better if I had asked to have each cell in the selection have
the following information put into it ... "This cell in in row " & Row_ & "
and column " & Col_ & " of section number " & section & "."

My question is, how would I set up my loops to have the correct values of
Row_, Col_, and Section_ ?

Thank you

Thank you Earl. That was very helpful - I didn't know about Selection.Areas.
After reading your post I knew where to look in the help file. Here's what
I finally came up with:

Sub test1()
Dim NumAreas As Integer, I As Integer, S As String
Dim FirstRow As Integer, FirstCol As Integer
Dim LastRow As Integer, LastCol As Integer
Dim J As Integer, K As Integer
NumAreas = Selection.Areas.Count
S = "There are " & NumAreas & " areas : "
For I = 1 To NumAreas
With Selection.Areas(I)
FirstRow = .Row
FirstCol = .Column
LastRow = FirstRow + .Rows.Count - 1
LastCol = FirstCol + .Columns.Count - 1
For J = 1 To .Rows.Count
For K = 1 To .Columns.Count
.Cells(J, K) = "Row " & J & ", Column " & K & ", in area " & I & "."
Next K
Next J
End With 'Selection.Areas(I)
S = S & " (" & Chr(FirstCol + 64) & FirstRow _
& ":" & Chr(LastCol + 64) & LastRow & ")"
Next I
MsgBox S
End Sub


One final question regarding something you put at the end of your post.
What is meant by top-posting and bottom-posting ?

Thanks