I need VBA Help for Excel spreadsheet -- Please!
in [Programming]
|
Prev: General Question on one line of code - parentheses and quotation marks
Next: Using Select Case on Single column only
From: ILoveMyCorgi on 21 May 2010 14:22 I have an Excel spreadsheet with three columns: ColA has a student number, ColB has a comment, and ColC has an amount. I have many rows of different comments and amounts for the same student number followed by rows with new student numbers and so on. I need to move all of columns B and columns C to the same row of the first line for the student number and move on to the next student number. What I am trying to do is have all the data for one student on one row so that I can merge the data with a Word document. For instance, 1495 writing in book $10.00 1495 football trans $ 5.00 3456 Water damage $15.00 3456 Lost Textbook $35.00 Witn an outcome of: 1495 writing in book $10.00 football trans $5.00 3456 Water damage $15.00 Lost Textbook $35.00 I hope someone can help me with this. Thak you.
From: Rich Locus on 21 May 2010 16:37 Dear Excel Member: If this solves your problem, please give me credit and check the "Answer" box. Here's a solution that I tested. You may need to change the data type for student (I chose string), but other than that, I believe it does what you need. Option Explicit Option Base 1 Public Sub CombineRows() Dim intLastRowColumnA As Long Dim intStartingRow As Long Dim intNextColumnForSameStudent As Long Dim intCurrentActiveRowForStudent As Long Dim strLastStudentNumber As String Dim i As Long Dim j As Long intLastRowColumnA = Cells(Rows.Count, "A").End(xlUp).Row intStartingRow = 2 intNextColumnForSameStudent = 3 intCurrentActiveRowForStudent = intStartingRow strLastStudentNumber = Cells(intStartingRow, 1).Value For i = intStartingRow + 1 To intLastRowColumnA If strLastStudentNumber = Cells(i, 1).Value Then intNextColumnForSameStudent = intNextColumnForSameStudent + 1 Cells(intCurrentActiveRowForStudent, intNextColumnForSameStudent).Value = Cells(i, 2) intNextColumnForSameStudent = intNextColumnForSameStudent + 1 Cells(intCurrentActiveRowForStudent, intNextColumnForSameStudent).Value = Cells(i, 3) Else intNextColumnForSameStudent = 3 intCurrentActiveRowForStudent = intCurrentActiveRowForStudent + 1 Cells(intCurrentActiveRowForStudent, 1).Value = Cells(i, 1).Value Cells(intCurrentActiveRowForStudent, 2).Value = Cells(i, 2).Value Cells(intCurrentActiveRowForStudent, 3).Value = Cells(i, 3).Value strLastStudentNumber = Cells(i, 1).Value End If Next i For j = intLastRowColumnA To intCurrentActiveRowForStudent + 1 Step -1 Rows(j).Delete Next j End Sub -- Rich Locus Logicwurks, LLC "ILoveMyCorgi" wrote: > I have an Excel spreadsheet with three columns: ColA has a student number, > ColB has a comment, and ColC has an amount. I have many rows of different > comments and amounts for the same student number followed by rows with new > student numbers and so on. I need to move all of columns B and columns C to > the same row of the first line for the student number and move on to the next > student number. What I am trying to do is have all the data for one student > on one row so that I can merge the data with a Word document. > > For instance, > 1495 writing in book $10.00 > 1495 football trans $ 5.00 > 3456 Water damage $15.00 > 3456 Lost Textbook $35.00 > > Witn an outcome of: > 1495 writing in book $10.00 football trans $5.00 > 3456 Water damage $15.00 Lost Textbook $35.00 > > I hope someone can help me with this. Thak you.
From: Rich Locus on 21 May 2010 16:42 I also made two other assumptions: 1) The data in the spreadsheet is sorted by Column A 2) Student data starts in row 2 If the student data starts in a different row, you can change the program. The data, however, must be sorted. -- Rich Locus Logicwurks, LLC "ILoveMyCorgi" wrote: > I have an Excel spreadsheet with three columns: ColA has a student number, > ColB has a comment, and ColC has an amount. I have many rows of different > comments and amounts for the same student number followed by rows with new > student numbers and so on. I need to move all of columns B and columns C to > the same row of the first line for the student number and move on to the next > student number. What I am trying to do is have all the data for one student > on one row so that I can merge the data with a Word document. > > For instance, > 1495 writing in book $10.00 > 1495 football trans $ 5.00 > 3456 Water damage $15.00 > 3456 Lost Textbook $35.00 > > Witn an outcome of: > 1495 writing in book $10.00 football trans $5.00 > 3456 Water damage $15.00 Lost Textbook $35.00 > > I hope someone can help me with this. Thak you.
From: ILoveMyCorgi on 21 May 2010 17:14 You assumed correct. When I ran the code, I got compile error: Syntax error and stops at Cells(intCurrentActiveRowForStudent, When I scroll up, Public Sub CombineRows() is highlighted in yellow... I am new to do this so I am not sure what I need to do. Thank you for your help. "Rich Locus" wrote: > I also made two other assumptions: > 1) The data in the spreadsheet is sorted by Column A > 2) Student data starts in row 2 > > If the student data starts in a different row, you can change the program. > The data, however, must be sorted. > -- > Rich Locus > Logicwurks, LLC > > > "ILoveMyCorgi" wrote: > > > I have an Excel spreadsheet with three columns: ColA has a student number, > > ColB has a comment, and ColC has an amount. I have many rows of different > > comments and amounts for the same student number followed by rows with new > > student numbers and so on. I need to move all of columns B and columns C to > > the same row of the first line for the student number and move on to the next > > student number. What I am trying to do is have all the data for one student > > on one row so that I can merge the data with a Word document. > > > > For instance, > > 1495 writing in book $10.00 > > 1495 football trans $ 5.00 > > 3456 Water damage $15.00 > > 3456 Lost Textbook $35.00 > > > > Witn an outcome of: > > 1495 writing in book $10.00 football trans $5.00 > > 3456 Water damage $15.00 Lost Textbook $35.00 > > > > I hope someone can help me with this. Thak you.
From: Rich Locus on 21 May 2010 17:26
Hello: I actually ran the code on Excel 2003 and it worked fine. Copy the code, then go to the VBA area by selecting ALT-F11. Then select Insert, Module from the menu. Highlight everything in the module (probably only one line) and then paste what you copied. Then from the Debug menu, you can select "Compile VBA Project. If you are still having problems, shoot me an email to richlocus(a)aol.com and I will send you a working version in which you can copy and past all your spreadsheet data. -- Rich Locus Logicwurks, LLC "ILoveMyCorgi" wrote: > You assumed correct. When I ran the code, I got compile error: Syntax error > and stops at Cells(intCurrentActiveRowForStudent, When I scroll up, Public > Sub CombineRows() is highlighted in yellow... I am new to do this so I am not > sure what I need to do. Thank you for your help. > > "Rich Locus" wrote: > > > I also made two other assumptions: > > 1) The data in the spreadsheet is sorted by Column A > > 2) Student data starts in row 2 > > > > If the student data starts in a different row, you can change the program. > > The data, however, must be sorted. > > -- > > Rich Locus > > Logicwurks, LLC > > > > > > "ILoveMyCorgi" wrote: > > > > > I have an Excel spreadsheet with three columns: ColA has a student number, > > > ColB has a comment, and ColC has an amount. I have many rows of different > > > comments and amounts for the same student number followed by rows with new > > > student numbers and so on. I need to move all of columns B and columns C to > > > the same row of the first line for the student number and move on to the next > > > student number. What I am trying to do is have all the data for one student > > > on one row so that I can merge the data with a Word document. > > > > > > For instance, > > > 1495 writing in book $10.00 > > > 1495 football trans $ 5.00 > > > 3456 Water damage $15.00 > > > 3456 Lost Textbook $35.00 > > > > > > Witn an outcome of: > > > 1495 writing in book $10.00 football trans $5.00 > > > 3456 Water damage $15.00 Lost Textbook $35.00 > > > > > > I hope someone can help me with this. Thak you. |