Cell empty conundrum

[Westbury]

I have a spreadsheet with a data input worksheet where each row includes date followed by 4 data readings. These 4 readings are all numbers. For each date any one, two three or all 4 readings could be populated i.e. some cells could be empty. For any date if readings 1, 2 or 3 are empty the macro sees exactly that, "empty" but if reading 4 is empty the macro sees it as 0. Why? The cell format, if it's relevant, for all 4 readings is set to number.

 

[StephenCrump]

Can you please post your code, and explain in more detail what you mean by the macro "seeing" cells as "empty" or zero?

Also, what exactly do you have in the four data cells? Is reading 4 truly a blank cell? Perhaps a formula returning ""? Or perhaps containing some characters (spaces or similar) that appear as blank - you can use LEN() to test this.

 

[Westbury]

Thanks for your interest in this.

the following 4 lines read the data. When I step through the code and position the mouse over elec, solar , gas and water and see the readings that the macro has captured. The first 3 work ok showing a numeric value or EMPTY, but the water column will capture a reading if there is one or 0 [zero] if there is no reading.

elec = Cells(a, 3).Value
solarpv = Cells(a, 4).Value
gas = Cells(a, 5).Value
water = Cells(a, 6).Value

in the spreadsheet I've populated the cells and set up up the worksheet functions LEN, BLANK & NUMBER linked to the 6th cell of the 2 rows (there's a column off to the left of this extract).

What is it that the macro is "seeing" in the 6th column that isn't being picked up in the worksheet functions?

 

[StephenCrump]

How have you declared your variables elec, solarpv, gas and water?

If you declare as Variant, or don't declare with a Dim statements so they are Variant by default, then the value before it's populated will be blank. And if the cell is blank, the variable's value will remain blank.

It appears you have declared water as numeric, perhaps Long or Double? In this case, the default value will be 0. Your code might need to allow for the difference between a blank cell (i.e. no reading) and a value of zero, i.e. a zero reading?

 

[Westbury]

The variables were all declared as double. I've changed them to variant and now all of them show as empty when the input cell is blank. I've edited the macro so the variables are all processed in the same way.

Thanks for your help in resolving this.