Stuck on syntax

[AlexanderBB]

Hi,

Trying to code the sum of all numbers in column "F" by

Dim x as Long
x = worksheetsFunction.Sum(Worksheets("Reports").Range("F:F"))


It doesn't work ! Would someone correct it please ?

Thanks, ABB

 

[Smitty]

You have a typo. It's "WorksheetFunction" (you've got an extra "s".)

HTH,

 

[AlexanderBB]

Thanks Smitty! Guess I should have known... but not out of the woods yet. It always returns a zero when there's numbers there. Even if I specify e.g. "F1:F4" (Which is 5,1,5,2 should = 13 !)

 

[JoeMo]

Thanks Smitty! Guess I should have known... but not out of the woods yet. It always returns a zero when there's numbers there. Even if I specify e.g. "F1:F4" (Which is 5,1,5,2 should = 13 !)

If you've made the correction Smitty suggested, there's nothing wrong with your syntax.

 

[Smitty]

It shouldn't be doing that even with blanks or zeros in that range.

This works fine:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        x = WorksheetFunction.Sum(Worksheets("Reports").Range("F:F"))<br>        <br>        Debug.Print x<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[AlexanderBB]

If you've made the correction Smitty suggested, there's nothing wrong with your syntax.

Fair enough, so it doesn't return the sum of numbers in the column? What is it meant to do?

Here's results from my debug window -

? worksheetFunction.Sum(Worksheets("Reports").Range("F1:F4"))
0
for i = 1 to 4: ? worksheets("Reports").cells(i,"F"):next
5
1
5
2

 

[Smitty]

Maybe you need to post all of your code.

 

[Norie]

Are the values in column F actually numbers, or are they text that appear to be numbers?

 

[AlexanderBB]

Not that it could be different, but Sub foo also prints zero.

There's no more code involved, I've reduced it to the sheet and Debug window for testing.

It should actually print 13 (the sum of F1:F4) ?

Are they text instead of numbers? I don't know! If I right click-Fornat Cells the category highlighted is Number, Sample 5, Decimal Places 0, Negative -1234

If it won't work, I can loop through and count them one by one, but I though this would be the more 'correct' method.

 

[GTO]

By chance, even with the cells formatted as Number, do the "numbers" left align in the column?

With one of the cells selected, is there a leading apostrophe that shows in the formula bar?