I am assigning ranges with empty cells to array variables.
However, when I reassign the variables with empty values back to a range, the empty cells now contain 0 (zero).
I really want the cell to still be empty instead of containing a 0. How can I fix this?
The following will work:
Define Range1 to be a selection of cells (Insert-Name- etc)
Define Range2 to be the same number of cells (Insert-Name-etc)
Put some values in Range1 eg
1
2
"blank"
3
4
5
6
Then run the macros
Is this what you were after?
ChasAA
Sub assignToVar()
Dim varArray(10)
Dim counter As Integer
Range("Range1").Select
counter = 1
For Each c In Selection
varArray(counter) = c.Value
counter = counter + 1
Next
Call assignToCell(varArray)
End Sub
Sub assignToCell(varArray)
Dim counter As Integer
Range("Range2").Select
counter = 1
For Each c In Selection
c.Value = varArray(counter)
counter = counter + 1
Next
End Sub
[Code Ends]
Joe_D wrote:
I am assigning ranges with empty cells to array variables.
However, when I reassign the variables with empty values back to a range, the empty cells now contain 0 (zero).
I really want the cell to still be empty instead of containing a 0. How can I fix this?
It would be easier to suggest a solution if you provided more information: how the array variables are declared, what the range looks like, what code is used to get the data into the array, to process the array, and to put the data back into the sheet.
- Jon
-
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_
Not quite.
I am actually assigning a range of cells to an array variable. I manipulate the array variable, but there may still be some blank values in the array variable. When I reassign the array variable with the blank values to another range, the blank values are written as zeroes, not blanks. See below
Dim varArray As Variant
varArray = Range(Cells(1,1),Cells(1,20)).Value >> some of the cells are blank
manipulate varArray values that are not blank >> the blank values of the array variable have not been changed and are still supposed to be blank
Range(Cells(2,1),Cells(2,20)).Value = varArray >> the blank values of array are now written as zeroes in the cells in row 2, but I want them to be blank.
Both your lines of code work (the second line keeps the cell blank and not 0 so may you are doing something during your manipulation of the array).
Post the manipulation code and maybe can help
ChasAA
On checking further, you are right. It turns out that I was multiplying the blanks by a number, which I guess forces the value to be a zero instead of a blank.
Thanks.
