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]

GTO, I thought you had a fix - as the column was Centered. But changing that to Left Aligned makes no difference.

There is no leading apostrohe in the formula bar.

But now I give up! Just realised I could check a different column - and this works as expected with the right count. It's format shows as 'General'.

So I changed the Format of F1 and F2 to 'General' but still get zero.

There is a difference though, Col F is left justified and M (which works) is correctly justified for numbers.

 

[GTO]

GTO, I thought you had a fix - as the column was Centered. But changing that to Left Aligned makes no difference.

Sorry, I was less than clear. I meant to ask whether the "numbers" naturally fall to the left or right, as determined by Excel. Changing the alignment formatting-wise, will not affect how Excel reads the values.

There is no leading apostrohe in the formula bar.

Thanks, as the leading apostrophe wouldn't show in the cell, but affect the return.

But now I give up! Just realised I could check a different column - and this works as expected with the right count. It's format shows as 'General'.

So I changed the Format of F1 and F2 to 'General' but still get zero.

There is a difference though, Col F is left justified and M (which works) is correctly justified for numbers.

Although the cells' number format (even text) doesn't affect SUM(), I would try changing all the cells to General for the moment. If they left align, Excel thinks they're text.

So... where did the "numbers" in F column come from? Are they imported? It is just a stab, but I'm wondering (if imported) if there may be non-printing characters throwing us.

 

[AlexanderBB]

Hi GTO,
This seems to be getting a bit complex, and worth getting to the bottom of.

You say the Format (even text) should work with Sum ?? So I wonder what is happening - obviously part of it must be where the data comes from.

First I do a .Cells.ClearContents. Then a .Cells(1, 1).CopyFromRecordset

I'm never sure how - or when - the worksheets cell format is set, or whether what you see with Right-click Format Cell is what it is, or what it could be ?? Can you find a cells properties at all ? And should Format be changed or set before or after data is entered?

The Recordset is from an Access table. The F column datatype is not specified in the SQL but I could enclose with Cint. The Field in Access is set to Number.

And the 'numbers' display as
1
14
3
10

etc. Single unit in the tens column.

 

[Norie]

Copy an empty cell, select the range of numbers causing the problem.

Then goto Paste>Special... and select add from the Operations selection.

That should convert the 'numbers' to 'real numbers and the formula should work.

Before you do that you can test if they are text using ISTEXT.

 

[AlexanderBB]

Hi Norie,

Sometimes nothing's easy! IsText doesn't seem to be recognised as a keyword in my Excel with Definition advising 'Identifier under cursor is not recognized'.

I copy a blank cell from the same column with a right click.

Paste Special shows no "Add' option. Pasting with any of the available options removes the numbers so there's no values remaining to test.

 

[Norie]

ISTEXT is a worksheet function not a VBA function.

Don't copy the blank cell from the same column that's causing the problem.

If you do then the cell might have the same problem, that might even be why you aren't gettiing the Add option when you goto Paste>Special...

PS What options do you get?

 

[AlexanderBB]

The options I get are


-- removed inline image ---


Whatever is copied, replaces the 'numbers' and your instructions result in an empty cell. Is that the desired effect?

If you give an example of IsText (I get no F1 help on it) I'll give that a try. I presume it's returns True or False depending on cell contents being text, or not?

 

[AlexanderBB]

Ah... options was a picture that showed in Edit mode then disappeared after posting the message. But surely it's the same as your right-click menu after you select Paste Special ?

 

[Norie]

ISTEXT (all capitals) will return true if the value in the cell it references is text.

So if you had text in A1 this would return TRUE.

=ISTEXT(A1)

Any chance you could upload this workbook somewhere and post a link?

Then we might get to the bottom of this.

PS Don't think the image came through properly - you could also upload that somewhere.

 

[Norie]

Oops, my bad, forgot adding 0 doesn't work for everything.

Anyway, put 1 in a cell, copy it and then Paste>Special...Multiply.