SUMPRODUCT Not Working
 

I am populating an Excel Spreadsheet in VBA from an Access Front end

Dim sTmp As String
Dim ctr As Integer

sTmp = "=SUMPRODUCT((D<<StartRow:D<<Row<" & Chr(34) & Chr(34) &
")/COUNTIF(D<<StartRow:D<<Row,D<<StartRow:D<<Ro w&" & Chr(34) &
Chr(34) & "))"

sTmp = Replace(sTmp, "<<StartRow", StartRow)
sTmp = Replace(sTmp, "<<Row", Row - 1)

With mActiveWorkSheet
.Cells(Row, 4) = sTmp
.Cells(Row, 7) = "=SUM(G" & StartRow & ":G" & Row - 1 & ")"
'Borders
For ctr = 1 To 7
DoEvents
With .Cells(Row, ctr)
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Font.Bold = True
End With
Next

End With

My problem is that Row, 4 takes the formula as a literal and displays
the formula not the result and Row, 7 works fine. I know the formula
work because if I type it in manually it works and I have compared the
outputs visually.

Also just an opinion question but is this the best practice I have no
idea which row the formula will reside on so If there is a way to use a
template and have the formula slide down with every entry and it would
have to recreate its self for each break I would like to know about.

SUMPRODUCT Not Working
 

Maybe you can make sure that cell is nicely formatted:

with .Cells(Row, 4)
.numberformat = "General"
.formula = sTemp
end with

(It kind of looks like that cell was formatted as Text.)

ps. I'd use:
..Cells(Row, 7).formula = "=SUM(G" & StartRow & ":G" & Row - 1 & ")"
too.

Scott wrote:

I am populating an Excel Spreadsheet in VBA from an Access Front end

Dim sTmp As String
Dim ctr As Integer

sTmp = "=SUMPRODUCT((D<<StartRow:D<<Row<" & Chr(34) & Chr(34) &
")/COUNTIF(D<<StartRow:D<<Row,D<<StartRow:D<<Ro w&" & Chr(34) &
Chr(34) & "))"

sTmp = Replace(sTmp, "<<StartRow", StartRow)
sTmp = Replace(sTmp, "<<Row", Row - 1)

With mActiveWorkSheet
.Cells(Row, 4) = sTmp
.Cells(Row, 7) = "=SUM(G" & StartRow & ":G" & Row - 1 & ")"
'Borders
For ctr = 1 To 7
DoEvents
With .Cells(Row, ctr)
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Font.Bold = True
End With
Next

End With

My problem is that Row, 4 takes the formula as a literal and displays
the formula not the result and Row, 7 works fine. I know the formula
work because if I type it in manually it works and I have compared the
outputs visually.

Also just an opinion question but is this the best practice I have no
idea which row the formula will reside on so If there is a way to use a
template and have the formula slide down with every entry and it would
have to recreate its self for each break I would like to know about.

--

Dave Peterson

SUMPRODUCT Not Working
 

That work thanks for your help


*** Sent via Developersdex http://www.developersdex.com ***