| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Another VB Code Required
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another VB Code Required
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 On Feb 6, 5:30*pm, 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another VB Code Required
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another VB Code Required
Thank you very much boss.......................
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another VB Code Required
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" wrote: 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 On Feb 6, 5:30 pm, 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another VB Code Required
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 On Feb 6, 6:13*pm, TGV wrote: 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" wrote: 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 On Feb 6, 5:30 pm, 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- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another VB Code Required
You are most welcome
"TGV" wrote: Thank you very much boss....................... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Another VB Code Required
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VB Code Required | Excel Discussion (Misc queries) | |||
| Macro Code Required - Pls Help | Excel Discussion (Misc queries) | |||
| macro code required | Excel Worksheet Functions | |||
| Macro Code required | Excel Discussion (Misc queries) | |||
| help required in completing the code | Excel Discussion (Misc queries) | |||
Linear Mode
