Sum formula in macro returns zero
 

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.

Sum formula in macro returns zero
 

Hello
Your syntax works for me (Excel 2003) if I use this:
Dim MarketCount&
MarketCount = Range("Q65536").End(xlUp).Row
Rng4.Value = Application.Sum(Range("Q8:Q" & MarketCount))

Maybe your variable marketCount is the problem?

HTH
Cordially
Pascal

"DanSmoach" a écrit dans le message de
news: ...
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.

Sum formula in macro returns zero
 

I cannot reproduce your problem, even setting the cells as Text. Are you
sure that marketCount is 25?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"DanSmoach" wrote in message
...
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.

Sum formula in macro returns zero
 

I swapped the line

Rng4.Value = Application.Sum(Range("Q8:Q" & MarketCount))

with the 2 lines

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

And it produced the correct result. I am unsure why this has worked (I am
new to VBA) but it now works perfectly.

Thanks

"papou" wrote:

Hello
Your syntax works for me (Excel 2003) if I use this:
Dim MarketCount&
MarketCount = Range("Q65536").End(xlUp).Row
Rng4.Value = Application.Sum(Range("Q8:Q" & MarketCount))

Maybe your variable marketCount is the problem?

HTH
Cordially
Pascal

"DanSmoach" a écrit dans le message de
news: ...
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.

Sum formula in macro returns zero
 

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.

Sum formula in macro returns zero
 

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.

Sum formula in macro returns zero
 

Hi Dan,

you can also try different approach:

rng4.formula = "=sum(" & Range("Q8:Q" & marketCount).address & ")"

Regards,
Ivan