Getting wrong value due to ref?
 

Hi Anyone:
I have 2 worksheets.
Sheet1:
-----Col:
RN:-A--B--C---D
1----2--4--""---8

Cell C1 has a blank data. I want to copy this data into sheet2 starting
from colB to colE at row num 5 as follows. The blank data to be replaced
by 0.
Sheet2:
-----Col:
RN:-A--B--C--D--E
5-------2---4--0--8

I wrote down in VB:
Sheets("Sheet2).Range("B5:E5).Value
="=If('Sheet1'!A1:D1="""",0,'Sheet1'!A1:D1)"

--Why the code is giving wrong results.

Thanks a lot for any help.


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=522898

Getting wrong value due to ref?
 

You have missed a few quote marks (after Sheet2 and B5:E5)
And you dont need the A1:D1 just the A1,
Excel will adjust the formula as it copies across

Try this:
Sheets("Sheet2").Range("B5:E5").Value = _
"=If('Sheet1'!A1="""",0,'Sheet1'!A1)"

George

jesmin wrote:
Hi Anyone:
I have 2 worksheets.

I wrote down in VB:
Sheets("Sheet2).Range("B5:E5).Value
="=If('Sheet1'!A1:D1="""",0,'Sheet1'!A1:D1)"

--Why the code is giving wrong results.

Thanks a lot for any help.

Getting wrong value due to ref?
 

Thanks a lot George. Its working. Frankly, I spent a huge time to
figure it out.


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=522898