summing double with empty string

vhanacek

New Member
Joined
Jul 12, 2005
Messages
42
In VBA I am summing values of cells and when i load cell which is empty, it assume that this cell is a string and i can't to sum it.
any help please?
Q = Q + ActiveSheet.Cells(Y, W).Value

ActiveSheet.Cells(Y, W).Value = "" ....it is VARIANT/STRING type
I initiatized Q as double, but it change itself to VARIANT/DOUBLE
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Perhaps:

If IsNumeric(ActiveSheet.Cells(y, w)) Then Q = Q + ActiveSheet.Cells(y, w)
 

vhanacek

New Member
Joined
Jul 12, 2005
Messages
42
Perhaps:

If IsNumeric(ActiveSheet.Cells(y, w)) Then Q = Q + ActiveSheet.Cells(y, w)

Thanks a lot.
It works now.
I spent whole day with this.....
I am not sure if it can't skip some cells with values using this code. Don't you know why it assumes some cells as VARIANT/EMPTY and some as VARIANT/STRING? Even if both are empty and the same number format.
Thanks
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
An empty cell has nothing in it, but "" is not an empty cell it contains an empty string and is breaking with an error because you are trying to add a string to a number.
 

vhanacek

New Member
Joined
Jul 12, 2005
Messages
42

ADVERTISEMENT

An empty cell has nothing in it, but "" is not an empty cell it contains an empty string and is breaking with an error because you are trying to add a string to a number.


How can I convert the cell with empty string to empty cell?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Is it there as a result of a formula?

If not, select the cell and hit Delete.

If it is the result of a formula, you may want to consider returning a 0 value instead and formatting the cells to hide zero values.
 

vhanacek

New Member
Joined
Jul 12, 2005
Messages
42

ADVERTISEMENT

Is it there as a result of a formula?

If not, select the cell and hit Delete.

If it is the result of a formula, you may want to consider returning a 0 value instead and formatting the cells to hide zero values.


Thanks for help...i deleted it and it works now
 

vhanacek

New Member
Joined
Jul 12, 2005
Messages
42
Is it there as a result of a formula?

If not, select the cell and hit Delete.

If it is the result of a formula, you may want to consider returning a 0 value instead and formatting the cells to hide zero values.


Thanks for help...i deleted it and it works now

sorry for disturbing again, but is there any code instead of hit delete?
 

vhanacek

New Member
Joined
Jul 12, 2005
Messages
42
Is it there as a result of a formula?

If not, select the cell and hit Delete.

If it is the result of a formula, you may want to consider returning a 0 value instead and formatting the cells to hide zero values.


sorry for disturbing again, but is there any code instead of hit delete?
I tried:

If ActiveSheet.Cells(Y, W).Value = "" Then ActiveSheet.Cells(Y, W).Value = Empty

but this doesn't work
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If ActiveSheet.Cells(Y, W) = "" Then ActiveSheet.Cells(Y, W).ClearContents
 

Forum statistics

Threads
1,136,635
Messages
5,676,902
Members
419,657
Latest member
ExcelAl1

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
Top