Dear Mike boss,

Please do a small correction in your below vb code that is when it finally
concatenating the cells I want to concatenate the cells in the below
mentioned order.

C1, D1 & A1 like this the macro should concatenate. But in the above code
its concatenating A1,C1 & D1.

So Please help. I have tried to change the code in the above order but I am
not able to do it.

You can only help me.

Thank you

TGV


"Mike H" wrote:

Hi,

This could be done without a macro but from previous questions I think
that's what you want. Right click your sheet tab, view code and paste this in
and run it

Sub marine()
x = 1
Dim MyRange As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
c.Offset(, 2).Value = x
c.Offset(, 1).Value = c.Value & _
c.Offset(, 2).Value & c.Offset(, 3).Value
If x < 200 Then
x = x + 1
Else
x = 1
End If
Next
End Sub

Mike

"TGV" wrote:

Dear Mike,

I require another vba code from you and here after I will not disturb you.
In A column I am having some data from a1 to 6000 that is a6000 cells
continuously and there will not be any blank cells. At the same time the
data will be vary and the 6000 cells are not stable value. So the macro
should itself select the range from A1 Cell to ending cell.

Now in C Column I want a macro to fill the C1 column cell serial number as 1
and the C2 Column cell serial number as 2 and the C3 column cell serial
number as 3 like this it should fill the continuous numeric series up to the
data which is available in A column. Here is the criteria if the C Column
cell serial number touches 200 then I want the C201 cell serial number as 1
and C202 cell serial number as 2 and C203 cell serial number as 3 like this
it should automatically start numbering 1 to 200 for every 200 cells.

(Explaining the above in brief:
In C401 cell I want the cell serial number as 1 and c402 cell I want the
serial number as 2 like this it should continue and if it reaches C601 cell
then again I want the cell serial number as 1 and C602 cell serial number as
2 like this it should calculate for every 200 cells.)

Finally I want to concatenate the A1,C1 & D1 values in B1 Cell and the same
formula should be pasted B Column upto the data available in A column.

Hope I have explained the same clearly.

Thank you,

TGV