Code terminates with no error message

[JTBorton]

I am trying to sum every third cell of a column in a worksheet. The value of the cells are determined by a formula based on the value of the cell directly above, which in turn gets its value by calculating the cell above it:

For each column:
Row 1: User Input
Row 2: Calculated Value from Row1
Row 3: Calculated Value from Row2

The cell formulas are as follows, using R8, R9 & R10 as reference:
[R7 = 90000.00, Calculated]; [E8 = 0.0, User Input]

[User input] = 0.15
=IF(R8<>"",R$7/(R8/100),"") = 60000000
=IF(R8<>"",$E8*$D$6*1000*R8/100,"") = 0 [Because E8 = 0]

This sequence is repeated several times down the spreadsheet, hence why I need to sum every third row. Now honestly this makes it hell to work with, but I didn't design it and I don't have time to redesign it.

So here is the problem: My code just stops dead in its calculation at the highlighted line with no message. The cell then displays #VALUE!

Function GetSum(Column As String) As Double
Dim intLastRow As Integer
Dim G As Integer
Dim Col As Integer
With Worksheets("Product Formulas")
    intLastRow = GetLastRow(wkSht:=ActiveSheet)
    Col = GetColumnNumber(Column) 'Converts base 26 number system to base 10
    For G = 10 To intLastRow Step 3
        [highlight]GetSum = CDbl(.cell(G, Col).Value)[/highlight]
    Next G
End With
End Function

At code failure: 
Column = "O", GetSum = 0, intLastRow = 125, G = 10, Col = 15

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

Sum every third cell. This does every N^th cell

Sub SumEveryX(n As Integer)
    Dim r As Range, i As Integer
    i = 1
    For Each r In Selection
        If i = n Then
            r.Value = Application.Sum(Range(r.Offset((n - 1) * -1), r.Offset(-1)))
            i = 0
        End If
        i = i + 1
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

Okay, so i tried to modify it for what I am trying to do. I was just testing it to see how it works but the macro still stops without an error message at the highlighted line:

Called from Cell R6 [=SumEveryX(3, 18)]

Function SumEveryX(N As Integer, Column As Integer)
Dim rngColumn As Range
Dim rngCell As Range
Dim I As Integer
    I = 1
    [highlight]Set rngColumn = ActiveSheet.Range.Column(Column)[/highlight]
    For Each rngCell In rngColumn
        If I = N Then
            rngCell.Value = Application.Sum(Range(rngCell.Offset((N - 1) * -1), rngCell.Offset(-1)))
            I = 0
        End If
        I = I + 1
    Next
End Function

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

I would not use reserve words as variables or constants.

Column is a reserve word and is a Property of the range object.

Function SumEveryX(N As Integer, Col As Integer)
Dim rngColumn As Range
Dim rngCell As Range
Dim I As Integer
    I = 1
    Set rngColumn = ActiveSheet.Range.Column[red][b]s[/b][/red](Col)
...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops. No Range just ColumnS...

Function SumEveryX(N As Integer, Col As Integer)
Dim rngColumn As Range
Dim rngCell As Range
Dim I As Integer
    I = 1
    Set rngColumn = ActiveSheet.Columns(Col)
...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

Okay, so here is the function as it stands now. It only runs through the For Each... Next loop once. This is something that has been giving me trouble for a while in other places too. I cannot get the For Each [rng] in [rng] loops to cycle more than once. Can you see what I am doing wrong?

Called from R5 [=SumEveryX(3, "R")]

Function SumEveryX(N As Integer, ColumnLetters As String)
Dim rngColumn As Range
Dim rngCell As Range
Dim Col As Integer
Dim I As Integer
    I = 1
    Col = GetColumnNumber(ColumnLetters) '[Returns 18 for R]
    Set rngColumn = ActiveSheet.Columns(Col)
    For Each rngCell In rngColumn
        If I = N Then
            rngCell.Value = Application.Sum(Range(rngCell.Offset((N - 1) * -1), rngCell.Offset(-1)))
            I = 0
        End If
        I = I + 1
    Next rngCell
End Function

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[GVF]

In your first posted code snippet - the word "cell" should be "cells".

You can tell because it isn't capitalized and if it had been correct then VBA would have capitalized it.

 

[GVF]

Regarding the first posted code again...GetSum gets overwritten every "Next G". Did you mean:
GetSum = GetSum + CDbl(.Cells(G, Col).Value)

 

[JTBorton]

GVF

I dont know if the .cell was a misprint when I pasted it, but thanks for the pointer.

To answer your question about As for GetSum being over written, yes, I did mean GetSum = GetSum + CDbl(.Cells(G, Col).Value). I just did not have a chance to catch that particular error since it just kept stopping midcode without any indication of what went wrong.

However, I think the problem was that the cell was a vbNullString. The cell formula is somethng along these lines:

=IF(R8<>"",$E8*$D$6*1000*R8/100,"")

So when the cell is empty VB just stopped dead in its tracks instead of returning 0 on the CDbl(Value) function. I think this may be the problem because I got around it by putting in a simple IsNumeric(cell value) test.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[JTBorton]

Well, I got it to work by omitting the For Each loops and using the following:

Function SumEveryX(X As Integer, ColumnLetter As Range, StartRow As Integer)
'Sums the value of every X cell in a column, or a range of columns
Dim ColNum As Integer
Dim intLastRow As Integer
Dim I As Integer
Dim J As Integer
    If ColumnLetter.Columns.Count > 1 Then
        SumEveryX = "Error"
        Exit Function
    End If
    I = 1
    intLastRow = GetLastRow(ActiveSheet)
    ColNum = ColumnLetter.Column
    For J = StartRow To intLastRow
        If I = X Then
            If IsNumeric(ActiveSheet.Cells(J, ColNum)) Then
                SumEveryX = SumEveryX + CDbl(ActiveSheet.Cells(J, ColNum).Value)
            End If
            I = 0
        End If
        I = I + 1
    Next J
End Function

It works great, but I really want to be able to do it with the For Each loops that Skip implimented. Range objects are one of my weaknesses, and I want to get a better understanding of them.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]