Macro to search value in column and then insert row

[Reimertoad]

Greetings,

I'm attempting to create a macro that will search a range of values, and insert a row directly underneath any of the values found - via an input box.

In column F, I have a list of employees' 'Start Times'. I want to create an input box that prompts me to enter a specific start time (there are 37), and once it finds an exact match of that particular value it will A) copy the row in which it was found, B) insert the entire row directly underneath (including the formulas), and C) shade the new row in yellow.

I've had very limited success and any help would be greatly appreciated!!

[alansidman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Reimertoad]

Here is a snippet of the sheet in question. Skills are in blocks of five. So ideally (for example) I would enter '1700-0100' into the input box that was created, and for each '1200-2000' that appears, a new line would be placed immediately underneath with the entire '1700-0100' row's information/formulas copied with it.

[Reimertoad]

....and ideally this would be how it would look after. Keep in mind there are numerous examples of each 'start time' as you can see by the example with '1200-2000' - so a new line would be required for each time the search value is found. It's ok that the blocks will no longer be in fives - I'm looking to create another macro after the fact that will hide the rows that I've just searched (after further data is inputted/removed etc).

I hope this makes sense - and thank you for the tips so far!

[alansidman]

I guess I wasn't clear enough in my request. I will try again.


Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Reimertoad]

Ah - apologies for that, but I think we're all good now.

The file 'Roster' is attached and sensitive info gutted.

Here is the code I have for inserting a blankline so far - but it only searches for the value '1200-2000' - and not any of the values in column F of which there are many. It also doesn't copy the formulas nor shade the freshly inserted line yellow.

Please Login or Register  to view this content.

Apologies again for the inexperience - but any help would be appreciated.

[alansidman]

Give this a try:

Please Login or Register  to view this content.

[Reimertoad]

Thank you very much for the quick reply.

Unfortunately though, after I was prompted for a start time via the input box, nothing further happened.... although if I click 'enter' with no start time entered, it will do exactly as hoped to the rows containing no values at all in Column F.

Further, I did copy your code exactly as above.

[alansidman]

For start time, did you enter only four digits, ie 1200? This is how the code is set up based upon your first post.

[Reimertoad]

I just tried entering in only the first four digits (eg 1200, and not the entire 1200-2000) and yes it worked a treat.

I adjusted the code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

It now will accept the specific time criteria - and this was done purely because I have both 1200-2000 and (full time) 1200- (casual) team members. Everything else is magnificent!

Thank you very much for your help alansidman - I cannot thank you enough for your help and efforts in getting this solved. It's greatly appreciated.

[alansidman]

Thanks for the feedback and rep. Glad to hear it is working for you.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.