If Then Loop?

mymkovski

New Member
Joined
Sep 15, 2008
Messages
17
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!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why not pull the headings from the first sheet along with the data?

Code:
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

New Member
Joined
Sep 15, 2008
Messages
17
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Sep 15, 2008
Messages
17
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top