Indent VBA Code debug error

[gheyman]

Run0time error 1004 : Unable to set Indentlevel property of the range class

Someone kindly gave me this code and I am trying to use it in a new spreadsheet. it does indent the rows but them it debugs with the message above. Anyone know why?

Sub Indent()
'
    Dim Ind As Range
'
    'this is where the Level is listed
        For Each Ind In Range("C9", Range("C" & Rows.Count).End(xlUp))
    'these are which rows to indent
        Union(Ind.Offset(, -1), Ind.Offset(, 2)).IndentLevel = Ind.Value
    '
    Next Ind
'
End Sub

 

[Peter_SSs]

Run0time error 1004 : Unable to set Indentlevel property of the range class

Someone kindly gave me this code and I am trying to use it in a new spreadsheet. it does indent the rows but them it debugs with the message above. Anyone know why?

Sub Indent()
'
    Dim Ind As Range
'
    'this is where the Level is listed
        For Each Ind In Range("C9", Range("C" & Rows.Count).End(xlUp))
    'these are which rows to indent
        Union(Ind.Offset(, -1), Ind.Offset(, 2)).IndentLevel = [COLOR="#FF0000"][B]Ind.Value[/B][/COLOR]
    '
    Next Ind
'
End Sub

One reason would be a non-numerical value (eg text or error) in column C. When you get the error, click Debug then hover your cursor over the red text above and see what value is reported.

 

[gheyman]

Ind.Value = "" which would make sense. There is a formula in column C that actual determines the indent level and will result in a "" value depending on conditions.

 

[Peter_SSs]

Ind.Value = "" which would make sense. There is a formula in column C that actual determines the indent level and will result in a "" value depending on conditions.

Yep, that would cause the error.
I presume that a value of "" in column C means no indent, so could you just alter that formula to return 0 instead of "" for those conditions?

 

[gheyman]

No, unfortunately it needs to be Null and not a value. If there is a way to stop the code when it hits " "" " that would be ideal. Not sure that's possible

 

[Peter_SSs]

No, unfortunately it needs to be Null and not a value. If there is a way to stop the code when it hits " "" " that would be ideal. Not sure that's possible

This should skip those rows with "" in column C

If IsNumeric(Ind.Value) Then Union(Ind.Offset(, -1), Ind.Offset(, 2)).IndentLevel = Ind.Value

 

[gheyman]

Wow, thanks! That worked. Very much appreciated

 

[Peter_SSs]

Wow, thanks! That worked. Very much appreciated

You're welcome.