If Then Loop?

[mymkovski]

Hey guys and gals,

I'm have an issue that I can't seem to solve...I'm pretty sure this can be done with a simple loop instead of a long drawn out if/then/elseif statement.

I have data that goes:

Cell A Cell B
COMPANY 1 text1
COMPANY 1 text2
COMPANY 2 text1
COMPANY 2 text2
COMPANY 2 text3
COMPANY 3 text1
COMPANY 4 text1
COMPANY 4 text2

On another worksheet, I have headings with each company name. I want to paste the values from Cell B under the headings of Company 1, Company 2, etc. So it would look like:

Cell A
COMPANY 1
text 1
text 2
COMPANY 2
text 1
text 2
text 3
COMPANY 3
text 1
COMPANY 4
text 1
text 2

And the data would change from day to day (the text that is, not the headings)

Thanks!

 

[Scott Huish]

Why not pull the headings from the first sheet along with the data?

Sub test()
Dim c As Range, cn As String
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If c <> cn Then
        cn = c
        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) = cn
    End If
        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) = c.Offset(, 1)
Next
End Sub

 

[mymkovski]

Hey, thanks for the response!

When I run that script it says "subscript out of range"

Also,

I don't want to copy over the headings. I want the values to be copied to a static spot on the 2nd sheet. So the values from Company A would start being pasted in A6 for example, and the values from Company B would start being pasted in A20 for example.

I sort of want it to go, If Company A = heading 1 of Company, paste text value from adjacent cell below heading 1, etc...

 

[Scott Huish]

Do you actually have a sheet called Sheet2? You may need to change that.

If values for Company A start in A6 and Company B starts in A20, will you never have more items than will fit in each range. For example, Company A would have a range of A6:A19, would you ever have more values for Company A than that?

 

[mymkovski]

At the moment, I have it set to a maximum of 6 rows following the heading. I could just make it 50 rows, or just adjust the macro as necessary if I do have to add another row.

I'm copying from a sheet named TRANS to a sheet name called DEAL