I am trying to concatenate 2 values (for instance new value in cell D4=
current value cell D4 + value Cell D3)

I get an error message 13 with this code:
For Each cell In Columns(4)
cell.Value = cell.Value & cell.Offset(0, -1).Value
Next

Any idea?
thanks
--
caroline

You had Value which is a number and excel doesn't like AND (&) a number.

cell.Value = cell.Text & cell.Offset(0, -1).Text

"caroline" wrote:

> I am trying to concatenate 2 values (for instance new value in cell D4=
> current value cell D4 + value Cell D3)
>
> I get an error message 13 with this code:
> For Each cell In Columns(4)
> cell.Value = cell.Value & cell.Offset(0, -1).Value
> Next
>
> Any idea?
> thanks
> --
> caroline

Try something like:

Sub caroline()
For Each cell In Range("D1100")
cell.Value = cell.Value & cell.Offset(0, -1).Value
Next
End Sub
--
Gary''s Student - gsnu200810


"caroline" wrote:

> I am trying to concatenate 2 values (for instance new value in cell D4=
> current value cell D4 + value Cell D3)
>
> I get an error message 13 with this code:
> For Each cell In Columns(4)
> cell.Value = cell.Value & cell.Offset(0, -1).Value
> Next
>
> Any idea?
> thanks
> --
> caroline

You are trying to concantanate using the value that you are replacing in the
same cell.

2 options

1

use a different cell to concantanate the 2 values

Sub tst()

For Each cell In Columns(5)
cell.FormulaR1C1 = "=rc[-2] & rc[-1]"
Next

End Sub

or

assign the value that is in column d to a variable and concantante col c
with the variable

HTH

"caroline" wrote:

> I am trying to concatenate 2 values (for instance new value in cell D4=
> current value cell D4 + value Cell D3)
>
> I get an error message 13 with this code:
> For Each cell In Columns(4)
> cell.Value = cell.Value & cell.Offset(0, -1).Value
> Next
>
> Any idea?
> thanks
> --
> caroline

For starters..........Offset(0, -1) is C4, not D3

If you meant D3 then Offset(-1, 0)

Bur that will keep adding the contents from each cell in column D to the
next cell below to the next cell below to the next cell below...............

Gonna get a lot of content by the time you loop through column D

If you meant C4 then try this to loop through the used range in column D

Dim rng1 As Range
Range("D1").Select
Set rng1 = Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))
For Each cell In rng1
cell.Value = cell.Value & cell.Offset(0, -1).Value
Next


Gord Dibben MS Excel MVP

On Mon, 27 Oct 2008 11:53:03 -0700, caroline
<(E-Mail Removed)> wrote:

>I am trying to concatenate 2 values (for instance new value in cell D4=
>current value cell D4 + value Cell D3)
>
>I get an error message 13 with this code:
>For Each cell In Columns(4)
>cell.Value = cell.Value & cell.Offset(0, -1).Value
>Next
>
>Any idea?
>thanks

>If you meant D3 then Offset(-1, 0)

But you could not use this on an entire column

D1 has no Offset(-1, 0)

So a range would be required like D21000


Gord