Solved: counta question

[vzachin]

hi,

i need to put in a CountA formula in each column.
i'm stuck with the CountA portion.
how do i correct the syntax below so that the CountA will adjust accordingly to the column
the coding below puts the same formula in each column

Sub TotalS()
Col = 5
Rw = 4
For i = Col To 20
With Worksheets("test")
If .Cells(Rw, i).Value <> "" Then
iLastRow = Cells(Rows.Count, i).End(xlUp).Row
.Cells(Rw - 2, i).Value = "=COUNTA(E5:E65536)"
End If
End With
Next
End Sub

thanks
zach

[Bob Phillips]

Sub TotalS()
Dim col As String
col = 5
Rw = 4
For i = col To 20
With Worksheets("test")
If .Cells(Rw, i).Value <> "" Then
iLastRow = Cells(Rows.Count, i).End(xlUp).Row
col = ColumnLetter(i)
.Cells(Rw - 2, i).Value = "=COUNTA(" & col & "5:" & col & ActiveSheet.Rows.Count & ")"
End If
End With
Next
End Sub


'-----------------------------------------------------------------
Function ColumnLetter(col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

[tstav]

Sub TotalS()
Col = 5
Rw = 4
For i = Col To 20
With Worksheets("test")
If .Cells(Rw, i).Value <> "" Then
iLastRow = Cells(Rows.Count, i).End(xlUp).Row
.Cells(Rw - 2, i).Value = WorksheetFunction.CountA _
(.Range(.Cells(Rw + 1, i), .Cells(.Rows.Count, i)))
'.Cells(Rw - 2, i).Value = "=COUNTA("E5:E65536)"
End If
End With
Next
End Sub

[david000]

xld,

I looked at that in excel 2007 (i lost my copy of 2003) and noticed some data type confusion.

the "col = 5" --- is at first an integer then a string "E".
And is typed as Long originally in the function.

I can only get that to work if the function is typed (As Variant).

Did I miss something?:doh:

[Bob Phillips]

That was just me being sloppy, not realising that the OP had used col as his variable, I should have used some other name.

... but, it works fine for with Long and String, I have no neeed for variant, although of course it throws the loop right off. I would expect it wo work as we only have 16,384 columns even in XL2007, well within the bounds of Long.

[vzachin]

hi tstav,

that works! one final question: if i wanted to change this: .Cells(Rw - 2, i).Value = WorksheetFunction.CountA _
(.Range(.Cells(Rw + 1, i), .Cells(.Rows.Count, i)))

to "=SUBTOTAL(3,E5:E65536)" instead, how can i rewrite the code?
i need the formula instead of the values.
thanks again
zach

[vzachin]

xld & david000

you guys lost me. how do i correct the code?

thanks
zach

[Bob Phillips]

This is fine here



Sub TotalS()
Dim colLett As String
Dim colNum As Long
Dim Rw As Long
Dim iLastRow As Long
Dim i As Long
colNum = 5
Rw = 4
For i = colNum To 20
With Worksheets("test")
If .Cells(Rw, i).Value <> "" Then
iLastRow = Cells(Rows.Count, i).End(xlUp).Row
colLett = ColumnLetter(i)
.Cells(Rw - 2, i).Formula = "=COUNTA(" & colLett & "5:" & colLett & ActiveSheet.Rows.Count & ")"
End If
End With
Next
End Sub


'-----------------------------------------------------------------
Function ColumnLetter(col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function


or



Sub TotalS()
Dim colLett As String
Dim colNum As Long
Dim Rw As Long
Dim iLastRow As Long
Dim i As Long
colNum = 5
Rw = 4
For i = colNum To 20
With Worksheets("test")
If .Cells(Rw, i).Value <> "" Then
iLastRow = Cells(Rows.Count, i).End(xlUp).Row
colLett = ColumnLetter(i)
.Cells(Rw - 2, i).FormulaR1C1 = "=COUNTA(R5C:R" & ActiveSheet.Rows.Count & "C)"
End If
End With
Next
End Sub

[tstav]

hi tstav,

if i wanted to change this: .Cells(Rw - 2, i).Value = WorksheetFunction.CountA _
(.Range(.Cells(Rw + 1, i), .Cells(.Rows.Count, i)))

to "=SUBTOTAL(3,E5:E65536)" instead, how can i rewrite the code?
i need the formula instead of the values.
thanks again
zach

hi zach,
If I misinterpreted you, please write back.
I'm sending you the substitute code for either "=COUNTA(E5:E65536)" or "=SUBTOTAL(3,E5:E65536)".
What puzzled me though, was the "i need the formula instead of the values" part of your message.
Regards, tstav

Sub SubTotal()
Dim i, col, Rw, iLastRow As Integer
col = 5
Rw = 4
For i = col To 20
With Worksheets("test")
If .Cells(Rw, i).Value <> "" Then
iLastRow = Cells(Rows.Count, i).End(xlUp).Row
'.Cells(Rw - 2, i).Value = "=COUNTA(E5:E65536)"
'The following 2 lines can take the place of the line above
.Cells(Rw - 2, i).Value = WorksheetFunction.CountA _
(.Range(.Cells(Rw + 1, i), .Cells(.Rows.Count, i)))

'.Cells(Rw - 2, i).Value = "=SUBTOTAL(3,E5:E65536)"
'The following 2 lines can take the place of the line above
.Cells(Rw - 2 + 1, i).Value = WorksheetFunction.SubTotal _
(3, (.Range(.Cells(Rw + 1, i), .Cells(.Rows.Count, i))))
End If
End With
Next
End Sub

[vzachin]

hi tstav,

you didn't misinterpret me. it was me not sure of what i was doing. as it stands, i need to apply an autofilter on e4:m4, which is why i needed a SUBTOTAL formula to be placed instead of the actual value.
does this make any sense...

thanks again
zach

[tstav]

(from post #10) does this make any sense...

Sure it makes sense zach, and from what I can see the code must have worked fine.
Regards, tstav

[vzachin]

thanks tstav & xld for the coding


zach