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
excel Problem
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: excel Problem
Mmmm perhaps one of the clever people can see what you have done wrong. I could only guess.
-
- Registered User
- Posts: 497
- Joined: 30 Apr 2007, 02:00
- Location: c:\program files\temp
- Contact:
Re: excel Problem
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.
line :
Cells(intMyCount + 1, 6).Formula = "=sum(f2:f" & intMyCount + 1 & ")"
should read:
Cells(intMyCount + 1, 6).Formula = "=sum(f2:f" & intMyCount & ")"
Thanks.
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: excel Problem
Hey glad you sorted it out but I never helped you.