Solved: Excel Auto Fill columns

[naruto]

Cell # Form Letter IDS CODE
100 X Y Z
101 X Y Z
105 x1 y1 Z1
106 x2 y2 z2

result
Group # Package Code Cell # Form Lettertext IDS Code
M1 600-601 100 X Y Z
101 X Y Z

M2 602-603 105 x1 y1 z1

M3 604-605 106 x2 y2 z2


I want sort the columns based on form, lettertext and ids code
introduce line after every group, add two columns group # and package Code and auto fill them with M1 and 600 numbers..I want to use input boxes for group and package code entries. It should automatically fill based on the last row in the spreadsheet..

How to do this using VBA?

Thanks.

Naruto

[lucas]

I'm totally confused....can you try to clarify this. An example workbook would help with sample data and 2 sheets...one with what you have to begin with and one with what you want the result to look like.

[naruto]

Original table
Cell #Form Letter IDS Code100XYZ101X Y Z103X1Y1Z1105X2Y2Z2

Result table
Group #Package CodeCell #Form Letter IDS Codem1600-601100XYZ 101X Y Z m2602-603103X1Y1Z1 m3 604-605105X2Y2Z2


Hope that helps...

[lucas]

If this is cross posted in another forum you should read this (http://www.excelguru.ca/node/7) and post a link to the cross post.

[naruto]

I have the file

[lucas]

Where is the link so we can see what has already been done on this?

[naruto]

Nothing has been done on this so far...

[Norie]

naruto

This still doesn't make much sense.

Where for example does the 600-601 come from?

[lucas]

Would still be polite to post the link.....so here it is:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22480312.html

[naruto]

The user assigns the package codes ...The user enters 600-601 and then the autofill function should automatically fill in 602-603, 604-605 until the last active row..

[naruto]

Lucas, I didn't know that you were asking for the link in experts-exchange.. I did post there as well. I read the cross posting info and in future will stick to just one...Thanks

Sorry..

[Charlize]

To get you started ...Sub make_package_list()
Dim rngA As Range, cellA As Range
Dim vForm As String, vLetter As String, vIds As String
Dim vGroup As String, vPackage As String
Dim avGroup() As String, avPackage() As String
Dim no As Long, no2 As Long, Lastrow As Long
ReDim Preserve avGroup(2)
ReDim Preserve avPackage(2)
Beep
vGroup = InputBox("Give starting groupcode (ABC/1)", "Provide " & _
"starting groupcode ...", "M/1")
Beep
vPackage = InputBox("Give starting packageno (123-123)", "Provide " & _
"starting packagecode ...", "600-601")
avGroup() = Split(vGroup, "/")
avPackage() = Split(vPackage, "-")
no = avGroup(1)
no2 = avPackage(0)
Set rngA = Worksheets(1).Range("A2:A" & _
Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row)
Lastrow = Worksheets(2).Range("A" & Rows.Count).End(xlUp).Row
For Each cellA In rngA
If vForm <> cellA.Offset(0, 1).Value And _
vLetter <> cellA.Offset(0, 2).Value And _
vIds <> cellA.Offset(0, 3).Value Then
With Worksheets(2).Range("A" & Lastrow + 1)
.Value = avGroup(0) & no
.Offset(0, 1).Value = no2 & "-" & no2 + 1
.Offset(0, 2).Value = cellA.Offset(0, 0).Value
'column b from original starting point
.Offset(0, 3).Value = cellA.Offset(0, 1).Value
'c
.Offset(0, 4).Value = cellA.Offset(0, 2).Value
'd
.Offset(0, 5).Value = cellA.Offset(0, 3).Value
no = no + 1
End With
Else
With Worksheets(2).Range("A" & Lastrow + 1)
.Offset(0, 2).Value = cellA.Offset(0, 0).Value
.Offset(0, 3).Value = cellA.Offset(0, 1).Value
.Offset(0, 4).Value = cellA.Offset(0, 2).Value
.Offset(0, 5).Value = cellA.Offset(0, 3).Value
End With
End If
vForm = cellA.Offset(0, 1).Value
vLetter = cellA.Offset(0, 2).Value
vIds = cellA.Offset(0, 3).Value
Lastrow = Lastrow + 1
no2 = no2 + 1
Next cellA
End SubCharlize

[Charlize]

So I just earned 500 points ... or what ?

A little thank you would have been nice.

[Aussiebear]

Well I for one thought you did a magnificent job Charlize..... so thanks from me.

[naruto]

Charlize, I don't know why message didn't get posted. HERE's a BIG THANK YOU FOR YOUR HELP. I am a new user and don't know how to grade your efforts...Thanks a million.


Naruto

[Charlize]

Charlize, I don't know why message didn't get posted. HERE's a BIG THANK YOU FOR YOUR HELP. I am a new user and don't know how to grade your efforts...Thanks a million.


NarutoYou don't have to grade the efforts I made to get to this solution for you (I hope you did noticed the other forum of the solution I provided for you and say that your question is answered ?). I did this because I wanted it to do. And best of all, I did it for free. So I'll leave it to this and say, your welcome, it was my pleasure.

Charlize

[mdmackillop]

So I just earned 500 points ... or what ?

Maybe 100 :devil2: