border for if

[SteveDB1]

Hi all.
On one of my macros, I want to set it up to run through a single sheet until
it finds the last row of contents.
However, I want it to only do select cells, that are bounded by borders.
I.e., instead of my having to activate for each cell group, I'd like to

I thought something like this would do it, but I'm missing something.
dim i as integer
dim myRng as range


for i = 1 to lastcell ' where lastcell is the last row with data.

set myRng = nothing

set myRng = ?

if myRng.Border(xlEdgeBottom) <> true then

Run "MyMacro"

Else : myRng.cells(?) = offset (1,0) 'down one row, zero columns

end if

next i

I'm sure something needs to be stated differently, I just haven't figured
out what, yet.
Thank you.
Best.

 

[XP]

I'm not sure exactly what you are looking for, but the following example
would loop through the used range (filled range) of column A (one) looking at
each cell. It would call another macro if the current cell contains a bottom
border that is solid.

Does this help?

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
Run "MyMacro"
End If
Next rCell

 

[SteveDB1]

XP,
Thank you for the response.
This appears to work, but I've noticed something that caught me by surprise.
It appears to cut out, and stop at a certain point-- I'm assuming when it
finds a cell with no bottom border. Yet, when I go to check if the border
exists, it's there-- according to the page format window.

 

[XP]

Hi,

It is possible that something you are doing with the macro being called is
causing the code to interrupt and exit. Try running the code, but where you
call your other macro, temporarily replace that line with:

MsgBox rCell.Address

This will display a message box with the cell address everytime a cell
containing the border is found. If this works on its own (and it should),
then the problem lies in the program being called...in that case you may need
to post that code so it can be modified to work...

Hope this helps...

 

[SteveDB1]

XP,
sure, anything's possible in my world.
I did as you said, and it shows the row locations of the borders. If a row
does not have a border all the way across the page, it ignores it, and only
lists rows with borders across the length of the row. I.e., in this case,
A$5:L$5, A$8:L$8, A$12:L$12, A$14:L$14, A$18:L$18, A$22:L$22, A$24:L$24,
A$26:L$26.
In my first run through, L24 did not have a border, so it skipped 24, and
went from 22 to 26.
Part of what was happening with my experience was that I'd activate the
code, it'd get to my macro, and ask me to select a range, it'd run through my
ranges, and then even if I was in the middle of the page, where boundaries
exist, it'd exit.
I'm starting to think now that the code was looking at another area than
what I was looking at, and did what it was told-- exit once you get to the
end-- even though it wasn't the end I was working on. That actually makes
sense as I consider it.

 

[SteveDB1]

What would be involved in iterating through successive columns?
As I look at your code, it seems that I'd need to use an outer for-next
loop, and change the .columns(1) to .columns(i)
i.e.,

for each rcell.activesheet.usedrange.columns(i).rows

for each rcell.activesheet.usedrange.columns(1).rows
if rcell.borders(xledgebottom).linestyle = xlsolid then
run "mymacro"
end if
next rcell
next rcell

would this be correct?
Again, thank you for your help.

 

[XP]

Yeah, the code I gave you is not designed for interaction; it is designed to
just run through everything.

Another approach might be to select your range in advance and then change
the code to only run on the selected cells like so:

Dim rCell As Range
For Each rCell In Selection
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
MsgBox rCell.Row
End If
Next rCell

This way everything you select is processed...

 

[XP]

Drawing on my last example using SELECTION, I would code it as follows,
although there are several ways to do this, and someone else may post
something more elegant, but this works.

In this case, you would select your initial cells to be processed, BUT only
in the first column. The code shifts the selection to the right, in this
case, one column at a time for three columns and runs through the same
procedure. So make your initial cell selection in one column (do not select
the entire column or it will run all the way down the sheet and it will take
awhile!) and run it:

Dim rCell As Range
Dim lX As Long
Do
For Each rCell In Selection
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
MsgBox rCell.Address
End If
Next rCell
lX = lX + 1
Selection.Offset(0, 1).Select
Loop Until lX = 3

With the message box in there, you can see what it's doing and step through.
In fact, the borders can be in different cells in each column, but it finds
them.

I'm going home for today, but I will check for your post tomorrow, in case
you post back with another question.

Regards

 

[SteveDB1]

XP,
I was going to respond yesterday afternoon, but I realized that I was rather
exhausted, and couldn't state very clearly what I am seeking to accomplish.
And I do apologize for that. Looking back now I felt like I was in some kind
of fog.
I'll try again. I hope that I'm more succinct this time.

As I've understood this thus far, the single loops now runs across the
columns out to the IX_th column, all on a single row-- whatever row I happen
to be in at the time of activation.

I then replaced what you'd initially posted, as an inner for loop, and it
then runs until it counts out to the IX_th value. I'm still trying to
understand that. I.e., it allows my macro to run for IX times.

My goal is to have this macro look for some property that determines a start
point (since the borders are the most common property on our worksheets, they
seemed the best choice.), then look through each subsequent cell for a bottom
border. Once it finds the border, select the range, and call to 'MyMacro.'
I then want it to iterate through the first column, one cell grouping at a
time-- with borders at top/bottom being my boundary limitation, then step
over to the second column, etc... and go until it reaches the end at the
lower right corner of the worksheet where the last used row/column with data
is the end.

I.e.,
Look at cell a1, is there a border? No? look at cell a2, still no border, go
to cell a3, ah... there's a border at the top. store that in buffer, now look
for border at the bottom by stepping through each successive cell. Bottom
Border found at cell a6. Select cells a3 through a6. Call to MyMacro.

Now go to cell a7, border at top found (actually it's bottom border of a6,
but it shows as top border for a7 as well), look in each cell for next bottom
border. bottom border found at cell a11. Select a7 thru a11, call to MyMacro.

this repeats until it reaches the last used cell of column A, with data in
it, and then goes to the top of column B. Runs the same bit as column A,
etc.... out to the last column with data, at the last row with data.
Generally, column L is the last, but occassionally we have out to column S,
or T. Also the distance down the columns varies, which is why I can't just
set a standard stopping point, it needs to be the last used cell.


Here is the present version (based on your postings):
---------------------------------------------------------------
Dim rCell As Range
Dim rCell1 As Range
Dim lX As Long
Do

For Each rCell1 In Selection
For Each rCell In ActiveSheet.UsedRange.Columns().Rows
If rCell1.Borders(xlEdgeBottom).LineStyle = xlSolid Then
'MsgBox rCell.Address
Run "MyMacro"
End If
Next rCell
Next rCell1
lX = lX + 1
Selection.Offset(0, 1).Select
Loop Until lX = 50
---------------------------------------------------------------
I placed the 50 in there to watch what happened.

Again, thank you for your help. It really is appreciated. I hope I was
stated it clearer this time.
Best.