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

 

[AlexanderBB]

I did create an Excel 2007 workbook with 1 column of Data and the previously posted Sub foo.

It retains the same problem as Excel 2003 and may have been helpful to you to debug. But I see no option here to incude an attachment

Shall we just flag it ? Here's my workaround

Function Countw(theCol As String, NumRows As Integer) As Long
    Dim x As Integer
    For x = 1 To NumRows
        Countw = Countw + Val(Worksheets("Reports").Cells(x, theCol))
    Next x
End Function

And pfft to Excel for not being able to sum a column !! Should be the most basic, fundemantal task....

Regards, ABB and thanks to all.

​

 

[Norie]

I didn't mean attach the workbook, I meant upload it to a file-sharing site and then post a link to the upload.

You definitely shouldn't have to use a UDF for this.

Most of the time when this is a problem it's something to do with the data.

If you want PM me and I'll send you my email address and then you can send me the file.

 

[AlexanderBB]

Ok Norie, anything in the interest of Science!

www.britburn.co.uk/files/others/foo1.xlsm

May not be enough to show much... In fact..... I just entered a couple of new numbers in Sheet2, and ran sub foo on that - and it worked.

Guess the Sheet1 'numbers' must be text... but someone said WorksheetFunction.Sum should still be able to count them....

The SQL for .CopyFromRecordset was altered to Cint(x) so the recordset datatype (if it has one) is integer. I believe that's a number...

 

[Norie]

Well you'll never guess what happened.

I put the SUM formula in B1, and of course it returned 0.

I then entered 1 in F1 and copied it.

Then I went to Paste>Special... and selected Multiplication from the Operation section.

Now comes the good bit, the formula returned a result of 1278.

On closer examination<SUP>*</SUP> that is the sum of A1:A142.

And all the 'numbers' became 'real' numbers and they were aligned to the right.

* Well selecting the range and looking in the bottom right.

PS WorksheetFunction.Sum didn't work until the numbers were changed.

 

[AlexanderBB]

Norie, I've not had that "aha" moment yet - but we must be close !

I tried to emulate what you did - entered 1 in F1. Copied it. Selected Col A -
Paste special - Multiply from 'Operations' and OK

Cell A11 changed to 16/01/1900 !!!!!

Sub Foo now prints 11. Well, darn it, thats' right!!! My Range is still A1:A3

Changing to .Range("A:A") prints 1278. Eureka

I should add I'm not familiar with working directly with the sheet and do everything through VB. Whatever is causing WorksheetFunction.Sum to fail would need to be fixed (in VB). I don't know how to do this. Then maybe, figure out why A11 changed to a date. (A Paste-Format of A10 to A11 sees it change back to 16.)

However, the aligment of the numbers has NOT changed. They're still aligned on left with WorksheetFunction.Sum now working.

Must be time for a stiff drink !

 

[Norie]

The value has changed to 16, but when it's formatted as date becomes 16/1/1900.

That's only formatting though.

Here's how you can do it in code:

Dim x As Long

    With Worksheets("Sheet1").Range("A1:A3")
        .Value = .Value
        x = WorksheetFunction.Sum(.Value)
    End With
 
    Debug.Print x

You could code the copying and paste special but that's probably a better way.