"" is not blank

[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

 

[Guest]

also if if i sort the data AZ, all the blank rows go first

 

[Myrna Larson]

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.

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

Dim DestSheet as Worksheet
Dim SourceSheet as Worksheet
Set DestSheet = Worksheets("Sheet1")
Set SourceSheet = Worksheets("Sheet2")

 

[Guest]

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?