sum column vba

[billandrew]

This is an additional question from a previous post.

I received the following code, however when using in a different worksheet it is not working well. I am trying to place a total row (sum) and format this row. Possibly bold font & fill


The range is D5:Lxx). Column 12 may expand.

Dim lr As Long

For i = 4 To 12
lr = Cells(Rows.Count, i).End(xlUp).Row
If lr > 4 Then
Cells(lr + 1, i) = WorksheetFunction.Sum(Range(Cells(5, i), Cells(lr, i)))

Next i

 

[bsquad]

whats the error you are getting? -- you are missing your 'End If'

 

[billandrew]

Compile Error
Expected To

 

[Joe4]

As bsquad mentioned, you are missing your END IF statement, i.e.

For i = 4 To 12
    lr = Cells(Rows.Count, i).End(xlUp).Row
    If lr > 4 Then
        Cells(lr + 1, i) = WorksheetFunction.Sum(Range(Cells(5, i), Cells(lr, i)))
[COLOR=#ff0000]    End If[/COLOR]
Next i

The only time you don't use an End If is if you put the THEN clause on the same line as your IF, or as a continuation, i.e.

For i = 4 To 12
    lr = Cells(Rows.Count, i).End(xlUp).Row
    If lr > 4 Then Cells(lr + 1, i) = WorksheetFunction.Sum(Range(Cells(5, i), Cells(lr, i)))
Next i

or

For i = 4 To 12
    lr = Cells(Rows.Count, i).End(xlUp).Row
    If lr > 4 Then [B]_[/B]
        Cells(lr + 1, i) = WorksheetFunction.Sum(Range(Cells(5, i), Cells(lr, i)))
Next i

(note the location of the underscore after the word "Then"; it should be a single space followed by the underscore)

 

[billandrew]

Sorry

I have an end if in my code

 

[billandrew]

Not sure what is going on. I used the code with the underscore and it worked fine.

 

[Joe4]

Then there is no error with that part of the code. If you are getting an error, I am guessing it has to do with the rest of your code.
Can you post the entire code from the procedure you have this code snippet located in?

 

[billandrew]

I'd like to learn how to format the row. Bold and Fill with a color. If that is possible..

 

[Joe4]

Not sure what is going on. I used the code with the underscore and it worked fine.

Yes, that would, for the reasons I mentioned. What the underscore says is that what follows on the next line is a continuation of the current line.
So even though it is on different rows, it is treated as if it is all on the same row. People often use that for readability when they have really long lines of code.

Which lines exactly do you want to bold and highlight?
Just the cells that the Sums are going in, or the whole range?

 

[billandrew]

Just the cells the sums would be going into.