PCFormat Forum Archive

I am pretty new to Excel VB. I have a spread sheet which i Filter for certain records, count the records and sums column F. all works well except the summing. all i get is a zero

my code is :

Sub Cons_Sheet_Format()
'
' Cons_Sheet_Format Macro
' Format the Cons Worksheet
'
' Keyboard Shortcut: Ctrl+Shift+Q
'

'delete coloumn H
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
'Filter for records not matching Helderberg - CPT WH
ActiveSheet.Range("$A$1:$G$10000").AutoFilter Field:=4, Criteria1:= _
"<>Helderberg - CPT WH"
'Delete All filtered records
ActiveSheet.Range("$A$2:$g$10000").Select
Selection.Delete Shift:=xlUp
'Remove Filter
ActiveSheet.Range("$A$1:$G$116").AutoFilter Field:=4
ActiveWindow.SmallScroll Down:=-6

'Count remaining records and insert count value into H2
Dim intMyCount As Integer
intMyCount = Application.CountA(Range("A:A"))
ActiveSheet.Cells(2, .Value = intMyCount

'Add totals field
Cells(intMyCount + 1, 6).Formula = "=sum(f2:f" & intMyCount + 1 & ")"


End Sub

Mmmm perhaps one of the clever people can see what you have done wrong. I could only guess.

Got it, I had a circular reference.

line :
Cells(intMyCount + 1, 6).Formula = "=sum(f2:f" & intMyCount + 1 & ")"

should read:
Cells(intMyCount + 1, 6).Formula = "=sum(f2:f" & intMyCount & ")"

Thanks.

Hey glad you sorted it out but I never helped you.