"" is not blank

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have an equation in my worksheet: =if(a1=0,"",1) (or any equation that
returns "")
then i have code that copies the value, then pastespecial (values) into
column B, so that i have the actual value and not the equation. the problem i
am having is that when i paste, the value of "", is not blank. it messes up
vlookup or sum...etc. so i guess i need to delete if = "" or something like
that

please help

thank you
 
It is an empty text string. That won't mess up SUM and other functions that
ignore text. It shouldn't matter with VLOOKUP if you use 0 for the last
argument. It probably WILL mess up sorting: these cells will come before cells
containing text and truly empty cells.
 
another way to do it so it doesn't happen is

DestSheet.Range("B9").Value = Sourcesheet.Range("C31").Value
or for a multicell contiguous range
DestSheet.Range("B9:B11").Value = Sourcesheet.Range("C31:C33").Value

This won't put a null string in the cell. This is just an example, but you
should be able to adapt it to your code.
 
Dim DestSheet as Worksheet
Dim SourceSheet as Worksheet
Set DestSheet = Worksheets("Sheet1")
Set SourceSheet = Worksheets("Sheet2")
 
thanks that worked perfectly. one more question

Dim DestSheet As Worksheet, sourceSheet As Worksheet
Set sourceSheet = worksheets("accessories")

Set DestSheet = worksheets("sale")

DestSheet.Range("b2:b8").Value = sourceSheet.Range("b2,b4,b6,b8").Value
the prolem is that it puts sourcesheet "b2" into destsheet "b2:b8" is there
a way to have it skip the blanks in the source?
 
Back
Top