Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Border looping macro
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/micro...be577f968cbcf8 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Border looping macro
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/...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. On Jun 16, 9:36*am, Dave Peterson wrote: 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/Reg...ewsAccount.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. On 06/16/2010 10:54, wrote: 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/micro...rogramming/bro... 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- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Border looping macro
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: Set myRng = Selection For Each Mycell In myRng.Columns(1).Cells 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). On 06/16/2010 11:58, wrote: 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/...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. On Jun 16, 9:36 am, Dave wrote: 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/Reg...ewsAccount.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. On 06/16/2010 10:54, wrote: 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/micro...rogramming/bro... 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- Hide quoted text - - Show quoted text - -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Border looping macro
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. On Jun 16, 12:57*pm, Dave Peterson wrote: 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: * Set myRng = Selection * * For Each Mycell In myRng.Columns(1).Cells 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). On 06/16/2010 11:58, wrote: 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/...prog/thread/3d... 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. On Jun 16, 9:36 am, Dave *wrote: 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/Reg...ewsAccount.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. On 06/16/2010 10:54, wrote: 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/micro...rogramming/bro.... 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- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Border looping macro
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. On Jun 16, 12:57*pm, Dave Peterson wrote: 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: * Set myRng = Selection * * For Each Mycell In myRng.Columns(1).Cells 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). On 06/16/2010 11:58, wrote: 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/...prog/thread/3d... 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. On Jun 16, 9:36 am, Dave *wrote: 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/Reg...ewsAccount.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. On 06/16/2010 10:54, wrote: 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/micro...rogramming/bro.... 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- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A toggling border macro | Excel Discussion (Misc queries) | |||
Macro for a Border | Excel Discussion (Misc queries) | |||
Macro to add border | Excel Programming | |||
Place a Bottom Border in a Table via a Macro | Excel Discussion (Misc queries) | |||
Changing the border of one cell s/n change the border of adjacent | Excel Discussion (Misc queries) |