Border looping macro

[steve.buckley]

Howdee all.
Well, this is coming in from the Google Groups-
microsoft.public.excel.programming server-- any way to directly access
the newsgroups anymore? If so-- please explain how, or provide a
link.

Last year, I was talking with Dave Peterson about a macro to loop
based on the borders. Below is the code he'd provided. I now finally
have time to sit down and work through it.
The original post is @
http://groups.google.com/group/micr.../43be577f968cbcf8?show_docid=43be577f968cbcf8

The code is:

Dim Mycell, myRng As Range
Dim topcell, botcell As Range

Set myRng = Selection

For Each Mycell In myRng.Columns(1).Cells
If Mycell.Borders(xlEdgeTop).LineStyle = xlSolid Or
Mycell.Borders(xlEdgeTop).LineStyle = xlDouble Then
Set topcell = Mycell

Else
If Mycell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
If topcell Is Nothing Then
MsgBox "Missing topcell for: " &
Mycell.Address(0, 0)
Else
Set botcell = Mycell
Application.DisplayAlerts = False
ActiveSheet.Range(topcell, botcell).Merge
Application.DisplayAlerts = True
End If
'get ready for next pair
Set topcell = Nothing
Set botcell = Nothing
End If
End If
Next Mycell

In my working through this, it appears to stop before it accomplishes
my goal. I.e., it finds the TopCell, and then exits the macro. I need
it to start working through each cell to find the BotCell, and then
perform the merge operation.
What am I missing here?
Thank you.

 

[Dave Peterson]

The code is based on selection. So what are you selecting before you start the
code?

Visit:
http://www.eternal-september.org/

Click on the Online link (in the notes about registering) and that'll take you
directly to this page:
http://www.eternal-september.org/RegisterNewsAccount.php

Register and you'll get an email to confirm that you're human(!).

Then just use
news.eternal-september.org
as your newsserver

Be aware that the traffic on the microsoft.public.excel.* has gone down quite a bit.

MS now wants you to use its web based forums.

 

[steve.buckley]

hi Dave-- sure is nice to "hear" a familiar "voice."
Ok, for starters-- I have in fact already registered for the Eternal
September server. However, I'm still trying to figure out how to
access the discussions on it. I've never used a system like this
before now. Well, unless the old newsgroups were, but even then, I
could access it "directly" in a manner that was real straightforward,
and simple.
I'm presently using the Google Groups access point to "talk" with
you.
And yes, I'm aware that I'm probably using all the wrong terminology
here.
As to the "new" forums that MS has set up, I've posted on there too
(in fact, that was where I was directed upon going back to the
newsgroup yesterday), so if you'd please look at that post as well--
http://social.answers.microsoft.com...thread/3d001ff1-934d-4e98-91c4-4467ffb2ef30--
it would indeed help me.

Ok.... code based on selection. I did in fact "select" the first cell
of my desired cell group. The first IF appeared to recognize the
"firstcell" and then just stopped, and exited the loop. It seems to me
that I should have more in that section to operate on.
I.e., I need to iterate through a group of cells to locate the bottom
cell, and then once both the topcell and the bottomcell are located,
merge them.


At this point, I just need to work through the code to select both the
top/bottom cells.

 

[Dave Peterson]

I saved this from a previous post, so not all of it may apply to you and your
newsreader:


When you look at the list of newsgroups, do you see a list of about 10?
Including one named: eternal-september.where.are.all.the.newsgroups

If you see that then you haven't configured the name/password correctly -- or
you haven't refreshed that list.

Subscribe to this eternal-september.where.are.all.the.newsgroups newsgroup and
you'll see one message that describes how to get access to all the newsgroups.

If all this is true, then since you're using OE, you can rightclick on the
newsserver and choose properties.

Then on the Server tab, you can check the "this server requires me to log on"
and fill out the account name and password.

Then ok your way out out of this dialog.

Then rightclick on the server once more and choose Refresh list. After that
finishes, you should be able to subscribe to the excel newsgroups.

(I hope <vbg>.)

But as long as the msnews servers are still carrying the *excel* newsgroups, I'm
staying here.

===========
And yep. You'll want to select the entire range -- not just the top cell.

These lines of code:
Determine what to look through. The first column of the selected range. So if
you only select one cell, it'll stop really fast!

You'll want to select something like A1:A100 (a nice contiguous range).

hi Dave-- sure is nice to "hear" a familiar "voice."
Ok, for starters-- I have in fact already registered for the Eternal
September server. However, I'm still trying to figure out how to
access the discussions on it. I've never used a system like this
before now. Well, unless the old newsgroups were, but even then, I
could access it "directly" in a manner that was real straightforward,
and simple.
I'm presently using the Google Groups access point to "talk" with
you.
And yes, I'm aware that I'm probably using all the wrong terminology
here.
As to the "new" forums that MS has set up, I've posted on there too
(in fact, that was where I was directed upon going back to the
newsgroup yesterday), so if you'd please look at that post as well--
http://social.answers.microsoft.com...thread/3d001ff1-934d-4e98-91c4-4467ffb2ef30--
it would indeed help me.

Ok.... code based on selection. I did in fact "select" the first cell
of my desired cell group. The first IF appeared to recognize the
"firstcell" and then just stopped, and exited the loop. It seems to me
that I should have more in that section to operate on.
I.e., I need to iterate through a group of cells to locate the bottom
cell, and then once both the topcell and the bottomcell are located,
merge them.


At this point, I just need to work through the code to select both the
top/bottom cells.

 

[steve.buckley]

Hi again.

When you look at the list of newsgroups, do you see a list of about 10?
Including one named: eternal-september.where.are.all.the.newsgroups

If you see that then you haven't configured the name/password correctly -- or
you haven't refreshed that list.

Subscribe to this eternal-september.where.are.all.the.newsgroups newsgroup and
you'll see one message that describes how to get access to all the newsgroups.

where should I be looking for this? I just checked it on Google's
Group list, and did not see, nor find it, when I searched.
I've tried setting up a new RSS feed in Outlook (NOT OE), and nothing
happens.
I then did a general search on the "eternal-
september.where.are.all.the.newsgroups" on google, and nothing but
more general forum discussions come up-- none of which lead me to some
reasonably clear solution.
Again-- thank you.

 

[steve.buckley]

Hi again....
Ok, I just did something.
I looked up in Outlook's help file and found where I can access news,
accessing it through OE. Apparently Outlook doesn't have a built in
newsreader (bummer).
I've gotten set up, and only one item came in from the newsgroup, back
from February. This will take some more doing, so I'll be fidgeting
around for a while trying to figure this out more completely.