Scary and Confusing Formula Problem

Can you post the macro?

EDIT: And the formula which yields 0 when copied over itself.

This code isn't annotated yet, so it might be somewhat confusing. But the point is that the code looks for cells that contain things like "=SUM(F5:F10)" which may or may not contain a total of 0.00. If it does contain a sum of 0.00, the whole total in the end ends up as zero, even if other cells in the Sum have non zero totals.


Function FinalTotals(strMarker As String)

Dim strMarkerCell As String
Dim i As Integer
Dim x1, x2, x3, x4, x5, x6 As Integer
Dim y1, y2, y3, y4, y5 As Integer
Dim strFormula As String

Range("C2").Select
i = 0
x1 = 0
x2 = 0
x3 = 0
x4 = 0
x5 = 0
x6 = 0


Do Until ActiveCell.Text = strMarker
ActiveCell.Offset(1, 0).Select
Loop

strMarkerCell = ActiveCell.Address


Do Until i = 6
Do Until ActiveCell.Text = strMarker
ActiveCell.Offset(1, 0).Select
Loop
If x6 = 0 Then
x6 = ActiveCell.Row
ElseIf x6 <> 0 And x5 = 0 Then
x5 = ActiveCell.Row
ElseIf x5 <> 0 And x4 = 0 Then
x4 = ActiveCell.Row
ElseIf x4 <> 0 And x3 = 0 Then
x3 = ActiveCell.Row
ElseIf x3 <> 0 And x2 = 0 Then
x2 = ActiveCell.Row
ElseIf x2 <> 0 And x1 = 0 Then
x1 = ActiveCell.Row
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop

ActiveCell.Offset(-1, 0).Select

y1 = x1 - x2
y2 = x1 - x3
y3 = x1 - x4
y4 = x1 - x5
y5 = x1 - x6

strFormula = "=R[-" & y1 & "]C+R[-" & y2 & "]C+R[-" & y3 & "]C+R[-" & y4 & "]C+R[-" & y5 & "]C"
varVar = InsertToCells(strFormula)




End Function


Function InsertToCells(strFormula As String)

varVar = CellPrep(3)
ActiveCell.Formula = strFormula

varVar = CellPrep(2)
ActiveCell.Formula = strFormula

varVar = CellPrep(8)
ActiveCell.Formula = strFormula

i = 0
Do Until i = 5
varVar = CellPrep(2)
ActiveCell.Formula = strFormula
i = i + 1
Loop

End Function
Function CellPrep(intOffset As Integer)
ActiveCell.Offset(0, intOffset).Select
With Selection.Font
.Underline = xlUnderlineStyleDoubleAccounting
End With
Selection.NumberFormat = "0.00"
End Function

For reference this query has also been posted here:

http://www.theofficeexperts.com/forum/showthread.php?s=&threadid=2134

Though i did not find out the answer to this question, I did create a loop that checks to see if a cell's sum equals 0. If it does, I did not include it in the formula.

Thanks anyway...looking forward to learning a lot here.

Joshua Wise