Printable View
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
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?
Code: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
yes that is the idea
So will this macro work for you or do we need to make some adjustments?
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.
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.
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
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:
Code:ActiveSheet.PrintOut
Hmmmm. Didn't print the headings. Here is the code:
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
Make Row = 2 like it initially was.
great got it. Thanks. Solved.