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<<Row>>&" & 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.

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<<Row>>&" & 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

That work thanks for your help


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