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

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

--
Message posted from http://www.ExcelForum.com

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

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


"wrkoch <" wrote:

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

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

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

Any help or direction would be greatly appreciated! Thanks!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

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

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

--
Message posted from http://www.ExcelForum.com