PDA

View Full Version : Solved: Print report from Listbox

austenr
04-20-2005, 07:39 AM
Currently I have a macro that lets the user choose a company and the information is then populated into the corresponding fields. The data is stored in 1 column. I would like to be able to let the user create a report that will print the entire list horizontally. There are blank lines seperating each companies info. Can anyone lend a hand to help me get started. File is attached. Thanks to everyone for their help.

forgot to attach the file

Jacob Hilderbrand
04-20-2005, 07:57 AM
You can create a loop to re-arrange the data. Now do you want to put the data for each company on its own row?

LastRow = Range("A65536").End(xlUp).Row
Row = 2
For i = 1 To LastRow Step 6
Range("C" & Row).Value = Range("B" & i).Text
Range("D" & Row).Value = Range("B" & i + 1).Text
Range("E" & Row).Value = Range("B" & i + 2).Text
Range("F" & Row).Value = Range("B" & i + 3).Text
Range("G" & Row).Value = Range("B" & i + 4).Text
Row = Row + 1
Next i

austenr
04-20-2005, 09:50 AM
yes that is the idea

Jacob Hilderbrand
04-20-2005, 10:31 AM
So will this macro work for you or do we need to make some adjustments?

austenr
04-20-2005, 11:34 AM
That should work. Thanks. I have another question. Could you break down what is happening in this formula?

=ISNA(Match(E3,\$I\$3:\$i\$14,False))

I am trying to compare two columns of data to see if there is a match. Here is what I think. Is it comparing what is in cell E3 to the range of data in column I all the way to row 14? It would put True in the cell the formula is in else would return False.

Jacob Hilderbrand
04-20-2005, 11:38 AM
Match will return the position in the array that the value is in. So it will look in I3:I14 to match the value in E3. If there is a match you will get the relative position (I3 is position 1, I4 is position 2 etc.)

If there is no match you will get an #N/A error.

So then the ISNA function evaluates the result from the Match function.

If there is no match you will get TRUE. If there is a match you will get FALSE.

austenr
04-20-2005, 01:30 PM
Hey that helps. That formula has always intimidated me a little. The macro does a good job assembling the report. A couple of things:

How can I get headings to print from my list in col A. Also can it just be sent to a printer after it is formatted with headings? Thanks

Jacob Hilderbrand
04-20-2005, 01:33 PM
I started at Row 2 so you can use Row 1 for your headers.

Range("C1").Value = ...

When you want to print it, you can use this line of code:

ActiveSheet.PrintOut

austenr
04-20-2005, 02:06 PM
Hmmmm. Didn't print the headings. Here is the code:

Public Sub PrintMtvGroupList()
LastRow = Range("A65536").End(xlUp).Row
Row = 1
Range("C1").Value = "Group Name"
Range("D1").Value = "Bill Rep"
Range("E1").Value = "Extension"
Range("F1").Value = "Group Number"
Range("G1").Value = "Back Up"
For i = 1 To LastRow Step 6
Range("C" & Row).Value = Range("B" & i).Text
Range("D" & Row).Value = Range("B" & i + 1).Text
Range("E" & Row).Value = Range("B" & i + 2).Text
Range("F" & Row).Value = Range("B" & i + 3).Text
Range("G" & Row).Value = Range("B" & i + 4).Text
Row = Row + 1
Next i
Columns("C:G").PrintOut
Columns("C:G").Clear
End Sub

Jacob Hilderbrand
04-20-2005, 02:10 PM
Make Row = 2 like it initially was.

austenr
04-20-2005, 02:44 PM
great got it. Thanks. Solved.