Excel VBA - Best way to indent one column based on another

W

wrkoch

FYI -- I have done a fair amount of VBA but this is my first foray int
excel.

I have a workbook with N sheets (1-5). Each sheet has a differen
number of rows. Column A has a number. I want to toggle the inden
level of Column B on all rows on all sheets to the value found i
Column A or to 0. I assume I can do this in a subprogram fired from
macro -- maybe use a global variable to hold the indent on/off state s
the subprogram knows what to do.

What's the best way to loop over the data containing rows on each shee
and set this value? Setting the value is obviously easy but I haven'
been able to figure out loop parameters, etc. to make this go.

Any help or direction would be greatly appreciated! Thanks
 
D

Dave Peterson

Maybe:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myIndentLevel As Long

For Each wks In ActiveWorkbook.Worksheets
With wks
FirstRow = 2 'header row in 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRow
myIndentLevel = 0
With .Cells(iRow, "A")
If IsNumeric(.Value) Then
If .Value < 16 _
And .Value > 0 Then
myIndentLevel = CLng(.Value)
End If
End If
.Offset(0, 1).IndentLevel = myIndentLevel
End With
Next iRow
End With
Next wks
End Sub
 
W

wrkoch

Thanks -- this will help a lot. The cell and range notation was no
very clear to me. Seeing your code explains a lot. And I was unawar
the there is a limit on the indent level
 
Top