Another VB Code Required

[TGV]

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

 

[Pete_UK]

Without using a macro:

Put this in C1:

=MOD(ROW(A1)-1,200)+1

Temporarily hide column B, select C1, and double-click the fill
handle, which is the small black square in the bottom corner of the
cursor.

Then unhide column B and put this formula in B1:

=A1&TEXT(C1,"000")&D1

Select B1 again, then double-click the fill handle. This will give you
3 digits for the B value, with leading zeros if necessary.

Hope this helps.

Pete

 

[Mike H]

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]

Thank you very much boss.......................

TGV

 

[TGV]

Dear Peter,

Thanks for educating me that the same can be done in function also. I have
also tried to make the same in function, but i failed because i dont have
knowledge like you.

But for this process i cannot able to use the function because every day i
need to do this process so i am making all these things in macro. For this
Mike is giving his excellent support in creating the macro using vb codes.
All the times i used to explain my situation in this discussion and mike boss
is capturing it and providing the solution what i am expecting to do.

I feel very happy because people like me dont know about excel very much and
people like you and mike are spending your valuable time and resolving our
issues.

Once again thank you and mike as well.

TGV

 

[Pete_UK]

You're welcome, and thanks for feeding back. I didn't see your earlier
post until after I had posted the above, but I now realise why you
want a macro solution.

Pete

 

[Mike H]

You are most welcome

Thank you very much boss.......................

TGV

 

[TGV]

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