S
SteveDB1
hi all.
I have a macro that I want to work through a worksheet, with specific
criteria.
At this point, borders seem to be the common property.
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 have a macro that I want to work through a worksheet, with specific
criteria.
At this point, borders seem to be the common property.
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