Stuck on syntax

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
2,092
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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 !) :confused:
 
Upvote 0
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 !) :confused:
If you've made the correction Smitty suggested, there's nothing wrong with your syntax.
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
Are the values in column F actually numbers, or are they text that appear to be numbers?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,386
Messages
6,178,285
Members
452,835
Latest member
ExcelNerd24

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top