Macro to insert lines based on cell content

[MirTraynor]

Hello,

I am new here and having real difficulty with something. I am doing my masters dissertation and have huge amounts of data. I'm finding myself repeating the same action again and again, and if I continue like this I will never complete my project on time! My project is looking at hostage-taking and kidnapping incidents, and my unit of analysis is the individual. However, for many incidents, more than 1 hostage is taken, and so I need to have a seperate line for each hostage.

What I need to do is insert extra rows depending on how many hostages were taken. So far my lines of data look like this, with the long number being the incident number, and the small number being the number of hostages:

200807160018 2
200807270038 2
200808210016 12
200809220014 128
200810250007 17
200811260015 2

I'm not very good at programming I'm afraid... is there a simple way around this?! I really appreciate any help that you can give me on this.
Thanks in advance...

MirTraynor

[gmk]

@MirTraynor

Welcome to the forum. This should work. Insert into a standard module.

Please Login or Register  to view this content.

hth

gmk



Click the * below the post to say thanks and remember to mark the thread as solved if answered satisfactorily.

[Sa DQ]

Please Login or Register  to view this content.

[MirTraynor]

Thanks for that guys. I haven't used these before... I'm used to just recording macros. Will these work for any row/column/cell number?

[MirTraynor]

Neither have worked, it is saying 'run time error' and the fault seems to be with the lines:

NumFrom = CLng(Mid(.Value, InStr(.Value, " ") + 1, 9))

and

nNumOfRows = Right(.Cells(nDataRow, "A"), Len(.Cells(nDataRow, "A")) - InStr(.Cells(nDataRow, "A"), " ")) ''' get rows to insert

Any ideas? To clarify, the large number and the small number are in seperate columns.

Thanks again,

Mirtraynor

[gmk]

@MirTraynor

To clarify, the large number and the small number are in seperate columns.

That would have been helpful to know from the start.

I have amended the code to suit 2 columns. To see what happens change your data references starting at C17 and the row numbers to insert starting at D17. Just make sure sure the 2 lots of data are aligned by row then experiment by changing the row and column numbers where indicated.

Please Login or Register  to view this content.

hth

gmk



Click the * below the post to say thanks and remember to mark the thread as solved if answered satisfactorily.

[MirTraynor]

Ah, apologies gmk. I should have been clearer.
I will try this one and see how I go.
Thanks for your help.

MirTraynor

[MirTraynor]

gmk... This worked a charm, and has made my life a whole lot easier!

Thanks very much!

[Cutter]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[gmk]

@MirTraynor

Good to hear.

made my life a whole lot easier

That's what forums are for.