Martin ( and Bob)

The variable marketCount is working correctly as that value is included in
another cell and if I hard code the marketCount with 25 the problem persists.
I believe I am referencing the correct sheet. It is not the activesheet, but
I set that when setting my rng4 variable.

I have replaced that line of code with the following

Set rng5 = Sheets("Marketing").Range("Q8:Q" & marketCount - 1)
rng4.Formula = "=Sum(" & rng5.Address & ")"

It now works perfectly. I would like to understand why this change works
though, to further my use of VBA in the future. Any ideas? I am using excel
2002.

Thanks for your help

"Martin Fishlock" wrote:

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.