concatenate cell.value

  • Thread starter Thread starter caroline
  • Start date Start date
C

caroline

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
 
You had Value which is a number and excel doesn't like AND (&) a number.

cell.Value = cell.Text & cell.Offset(0, -1).Text
 
Try something like:

Sub caroline()
For Each cell In Range("D1:D100")
cell.Value = cell.Value & cell.Offset(0, -1).Value
Next
End Sub
 
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
 
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
 
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 D2:D1000


Gord
 
Back
Top