Macro to auto insert column

[dmr3796]

Hey All,

Im searching for a macro that will find matches based off a certain lookup value, and auto insert a row underneath for multiple matches.

The sheet where the macro will take place will look as such starting with column A: "Sheet1"

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Lookup Values[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Return 1[/TD]
[TD]Return 2[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



This is the sheet that's going to be searched: "Sheet2"
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Lookup Values[/TD]
[TD]x[/TD]
[TD]I want this[/TD]
[TD]I want this[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]

I want the macro to search sheet2 for "Lookup values" and return the values in the "I want this" columns inside of the "return 1 & 2" columns

Additionally, where this gets tricky is that some lookup values have MULTIPLE matches. If there is 2 matches I want a row inserted under to accomodate the second match, placing all the return values in the same columns as specified above.

***Columns labeled as "x" IN 'SHEET1' contain data that MUST align to the respective lookup value, so it's important that if a new row is inserted due to multiple matches, that the row extends PAST those "x" columns to keep the data aligned**

Please let me know if I can clarify further!

 

[ONEMINUTETUTORIAL]

Duplicate post : https://www.mrexcel.com/forum/excel-questions/1022169-macro-help.html

 

[Macropod]

Please read Mr Excel's policy against making duplicate threads, in Rule 12: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Thread closed. You may continue the discussion in your other thread.