Help Troubleshoot this...please

[ectoid]

I am new as you will quickly learn. In the following code I ask the macro to evaluate column E for two conditions and then change the cell formatting of cells D, J, K, and M. However when I run it only K (offset (0,4)) is getting the cell format change, but none of the others.
Any Idea why?

'Set range variable to first Unit in Item list
Set c = Sheets("Estimate").Range("E7")

Do While c.Value <> ""

'Evalute Unit value to set number format and format cell of same row but offset column
If c.Value = "LS" Or c.Value = "FPLS" Then

Set fmtc = c.Offset(0, -1)
Selection.NumberFormat = "0.00%"

Set fmtc = c.Offset(0, 3)
Selection.NumberFormat = "0.00%"

Set fmtc = c.Offset(0, 4)
Selection.NumberFormat = "0.00%"

Set fmtc = c.Offset(0, 6)
Selection.NumberFormat = "0.00%"

Else
End If

'Move variable range to next Unit in column
Set c = c.Offset(1, 0)


Loop

Thanks a bunch...

 

[Tazguy37]

Original:

'Set range variable to first Unit in Item list
Set c = Sheets("Estimate").Range("E7")

Do While c.Value <> ""

'Evalute Unit value to set number format and format cell of same row but offset column
If c.Value = "LS" Or c.Value = "FPLS" Then
'*Since you're selecting, and then doing something to the selection, these groups can be shortened to 1 line.
Set fmtc = c.Offset(0, -1)
Selection.NumberFormat = "0.00%"

Set fmtc = c.Offset(0, 3)
Selection.NumberFormat = "0.00%"

Set fmtc = c.Offset(0, 4)
Selection.NumberFormat = "0.00%"

Set fmtc = c.Offset(0, 6)
Selection.NumberFormat = "0.00%"
'** up to here
Else '*No need for an else
End If

'Move variable range to next Unit in column
Set c = c.Offset(1, 0)


Loop



This would shorten what you have to:

Sub test()
Dim c As Range, fmtc As Range

'Set range variable to first Unit in Item list
Set c = Sheets("Estimate").Range("E7")

Do While c.Value <> ""
    'Evalute Unit value to set number format and format cell of same row but offset column
    If c.Value = "LS" Or c.Value = "FPLS" Then
        c.Offset(0, -1).NumberFormat = "0.00%"
        c.Offset(0, 3).NumberFormat = "0.00%"
        c.Offset(0, 4).NumberFormat = "0.00%"
        c.Offset(0, 6).NumberFormat = "0.00%"
    End If

    'Move variable range to next Unit in column
    Set c = c.Offset(1, 0)
Loop

End Sub

Seems to work OK for me. Also try stepping through your code with F8 to follow the flow of it. Hope that helps!

 

[ectoid]

Hey thanks man...it works out great. What was confusing me too was a couple of hidden columns that was throwing off the offset...oops.
All good now though.

And thanks for the tip too.

 

[Tazguy37]

You're most welcome, glad you got it worked out.