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.
"XP" wrote:
> 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
Similar Threads
