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

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

"choice" wrote:

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.

On Sun, 12 Sep 2004 00:47:04 -0700, choice
wrote:

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

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.

--
Regards,
Tom Ogilvy

"choice" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

"choice" wrote in message
...
yea that works...just one question.
how do i define my sourcesheet and destsheet.? i tried but cannot get it
right

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"choice" wrote in message
...
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

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?

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"choice" wrote in message
...
yea that works...just one question.
how do i define my sourcesheet and destsheet.? i tried but cannot get it
right

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"choice" wrote in message
...
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