Moving data from one cell to another

[email protected] · 04-26-2005, 02:06 PM

Hi all.

It is the first time I have posetd in here and am very interested in
learning more about VB for Excel.
Firstly, anyone know the best way to start learning? Are there any
books you could recommend? or websites even?

Secondly, I am wondering how to do the following,

I need a macro to move data from one cell to a cell two to the right,
but rather than copy and pasting over the top of the cell the data from
the cell on the left gets added onto the end of the destination cell

For example

[red][][book] >>>>>>>> [ ][][red book]

I am looking for it to work with over areas of cells or just single
cells?

Is it possible?
I don't really know where to start.

Any help appreciated

Anthony

**pikus** · 04-26-2005, 02:28 PM

What you posted is not really specific enough to give you a complete answer to your problem. How do we know which cells you're talking about? How does Excel know? Is the destination cell going to have something in it already every time? I'll assume you get that information some other way and do it in a bit of short hand:

x = 5 'this will be the number of the row
y = 3 'this is for the number of the column the data is coming from

If ActiveSheet.Cells(x, y + 2).Value <> "" Then

ActiveSheet.Cells(x, y + 2).Value = ActiveSheet.Cells(x, y + 2).Value & " " & _
ActiveSheet.Cells(x, y).Value

Else

ActiveSheet.Cells(x, y + 2).Value = ActiveSheet.Cells(x, y).Value

End If

ActiveSheet.Cells(x, y).Value = ""

The IF statement checks to see if there is data in the destination cell and if it does it adds a space followed by the value of the originating cell. Otherwise it just moves the data from one to the other. If you know there will always be data in the destination cell you can delete the entire IF statement and use just

ActiveSheet.Cells(x, y + 2).Value = ActiveSheet.Cells(x, y + 2).Value & " " & _
ActiveSheet.Cells(x, y).Value

Carim · 04-26-2005, 03:06 PM

Anthony,

>From what you are describing, concatenation would do the work ... you
don't a macro ...
e.g. =A1&" "&C1

HTH

Carim

[email protected] · 04-26-2005, 04:06 PM

Hiya,

Yeah I have just started getting to grips with concatenation, but I
want to avoid inputting a formula everytime. I don't know how to get a
macro to give the value of that formula. Also I am looking to replace
[book] by [red book] so i can't input into this cell

Ant