It could be that you are not referencing the correct worksheet. Try:

rng4.Value = Application.Sum(ActiveWorksheet.Range("C8:C" & marketCount))

--
HTHs Martin


"DanSmoach" wrote:

Hi

Does anyone have any suggestions on what can be causing this problem?

As part of a larger macro that formats a report, I need to include the sum
of a range eg Q8:Q25. At the time of running the macro the number of rows to
sum is not known (ie in this case 25), but is captured by the variable
marketCount. The variable marketCount is placed in another cell of the report
and is working correctly.

The following line returns 0

rng4.Value = Application.Sum(Range("Q8:Q" & marketCount))

The values in column Q are formatted as $. When I manually write a sum
formula (ie. =Sum(Q8:Q25)) the correct result is obtained.

Does anyone know what is causing this problem or can suggest alternate code
to sum these values - it is driving me crazy.

Thanks for your consideration of this issue.