if then question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roger - 15 Jun 2007 17:34 GMT

I have two columns one(A) with continuous data from top to bottom, the other
(B) with simular data only dispersed randomly from top to bottom. What I
would like to do is replace the data in column A with the contents of column
B, only when there is data in the cell in column B, if there is no data I
want the data in column A left the way it is.
thanks in advance for the help.
Roger

Reply to this Message

Don Guillett - 15 Jun 2007 17:47 GMT

One way.
Sub replaceifdata()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b1:b" & lr)
If Len(Trim(c)) > 0 Then c.Offset(, -1) = c
Next
End Sub

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I have two columns one(A) with continuous data from top to bottom, the
>other
[quoted text clipped - 5 lines]
> thanks in advance for the help.
> Roger

Reply to this Message

Rick Rothstein (MVP - VB) - 15 Jun 2007 19:55 GMT

>I have two columns one(A) with continuous data from top to bottom, the
>other
[quoted text clipped - 4 lines]
> want the data in column A left the way it is.
> thanks in advance for the help.

You can do that directly... Copy the range you want from column B, click the
matching start cell in column A, then select Paste Special from the Edit
menu item... on the Paste Special dialog box, select Values from the Paste
section, None from the Operation section and check mark the Skip Blanks
box... click OK and you are done.

Rick

Reply to this Message

Don Guillett - 15 Jun 2007 21:34 GMT

Unless a blank in col B is NOT really a blank

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> >I have two columns one(A) with continuous data from top to bottom, the
> >other
[quoted text clipped - 13 lines]
>
> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 15 Jun 2007 23:54 GMT

> Unless a blank in col B is NOT really a blank

I am newly returned to Excel after a long absence, so I am rusty. Can you
please tell me what you are referring to here? Thank you.

Rick

Reply to this Message

Don Guillett - 16 Jun 2007 00:02 GMT

Test both with one of the cells in col B that has been contaminated by
touching the space bar so that is not really blank.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>> Unless a blank in col B is NOT really a blank
>
> I am newly returned to Excel after a long absence, so I am rusty. Can you
> please tell me what you are referring to here? Thank you.
>
> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 16 Jun 2007 00:08 GMT

> Test both with one of the cells in col B that has been contaminated
> by touching the space bar so that is not really blank.

Okay, I see what you were saying. If I smart, I would have guessed that was
what you meant by the Trim function call you made in your VBA code. Thanks
for getting back to me on this.

Rick

Reply to this Message