excel Problem

All topics about coding, designing, etc. goes in here.
Post Reply
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

excel Problem

Post by Mclaren »

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, 8).Value = intMyCount

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


End Sub
User avatar
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

Post by Tribble »

Mmmm perhaps one of the clever people can see what you have done wrong. I could only guess.
Image
Mclaren
Registered User
Posts: 497
Joined: 30 Apr 2007, 02:00
Location: c:\program files\temp
Contact:

Re: excel Problem

Post by Mclaren »

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.
User avatar
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

Post by Tribble »

Hey glad you sorted it out but I never helped you.
Image
Post Reply