


Yesuslave
Board Regular
Joined: 19 Sep 2003
Posts: 97
Location: New Jersey
Flag:
Status: Offline

Re: Scary and Confusing Formula Problem
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 _________________ I can't decide whether I want to be a Ludite or a technogeek.

Fri Sep 19, 2003 8:10 pm 




