VBA Loop Evaluates Cell, Copy & Paste it above Current Row, If Criteria is met

JohnnyBconfused

New Member
Joined
Jun 7, 2015
Messages
5
I'm a novice when it comes to VBA, and I have a list of names in Excel 2013 (Running Windows 8.1) and I want to write code that will generate and include all nicknames the person might have, so that if a user enters some variation - it will still be recognized. For example Richard would have the nicknames "Rich", "Rick", "D---". I want the code to add the additional names above the line of code that it is evaluating. I expected the code I currently have to copy and paste the row being evaluated into the newly created row(s) above. I planned to learn how to change the names later - and am currently doing it by hand. Unfortunately I'm getting a run-time error 1004 "application-defined or object-defined error", at "Rows("i:i").Select". I would like all the names to stay in the same spreadsheet. Can this be accomplished?

I've checked many examples here & on stack overflow, but none fit my use case & most send the rows to another sheet / workbook... Below is my code:




Code:
Sub insertnicknamerows()



Dim First, i, j,k,h


    For i = 1 To Rows.Count
        First = Split(Cells(i, 1).Value, " ")(0)
'this list grabs assigns the first name to the variable    



        If First = "Joe" Then
            j = i + 1
        
        
            Rows("i:i").Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Rows("j:j").Select
            Selection.Copy
            Rows("i:i").Select
            ActiveSheet.Paste
            i = j
            
        ElseIf First = "Richard" Then
            j = i + 1
            k = i + 2
            h = i + 3
            
            
            Rows("i:i").Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Rows("h:h").Select
            Selection.Copy
            Rows("i:i").Select
            ActiveSheet.Paste
            Rows("j:j").Select
            ActiveSheet.Paste
            Rows("k:k").Select
            ActiveSheet.Paste
        
            i = h
        
        End If
        
    Next i
End Sub
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

RudiS

Active Member
Joined
May 7, 2015
Messages
349
Hi,

Welcome to the forum...

Would it be possible to post a representation of your data? A table representing the data you want to process, or a picture or even better, upload a sample workbook to Dropbox or OneDrive and provide a link to the shared file...

It would be much easier to assist you with something more visual or graphical to work with.

TX
 

RudiS

Active Member
Joined
May 7, 2015
Messages
349
TX...

I have scanned your code and I can see you are inserting blank rows above the names if they meet a condition. Where will I get this condition and how many blank rows must be inserted if the condition is true?

Please clarify. TX
 

JohnnyBconfused

New Member
Joined
Jun 7, 2015
Messages
5

ADVERTISEMENT

Hi RudiS,

I actually don't want to insert blank rows. If the name is "Richard Branson", I want to insert rows above that, that contain: "Rich Branson", "Rick Branson", & "D--- Branson"

I was going to code them into either a long if statement, or a select cases statement. Different names will need different numbers of rows (ex: Joe only has the alternative of Joseph, but Richard has alternatives of Rich & Rick & D--- and therefore it needs 3 rows to be added). If you could show me one example of how to code it so that "Richard" in a row, will generate 3 rows above with "Rich" & "Rick" & "D---k" as the first name - then I can write out the if / case statements. I don't understand how to instruct excel to do the first piece.






Thanks!
 

RudiS

Active Member
Joined
May 7, 2015
Messages
349
Hi,

I would approach it differently.
The nickname variations I'd put into a sheet in the workbook and hide the sheet.
The code would then reference the sheet to find the actual name and extract the nicknames to the right of it (see example table below)
This way would be FAR easier to maintain the names and nicknames, adding, editing, adding more variations and referencing than to have all that in code. It will keep the code shorter and running faster too.

If you agree with this I can assist with the code...

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-bottom: 1px solid black;background-color: #FFFF00;;">Actual Name</td><td style="border-bottom: 1px solid black;;">NickN 1</td><td style="border-bottom: 1px solid black;;">NickN 2</td><td style="border-bottom: 1px solid black;;">NickN 3</td><td style="border-bottom: 1px solid black;;">NickN 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;background-color: #FFFF00;;">John</td><td style="border-top: 1px solid black;;">Joe</td><td style="border-top: 1px solid black;;">JJ</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;background-color: #FFFF00;;">Jane</td><td style=";">J</td><td style=";">Joey</td><td style=";">Sweetheart :)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;background-color: #FFFF00;;">Bob</td><td style=";">Bo</td><td style=";">BB</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;background-color: #FFFF00;;">Tom</td><td style=";">Tommy</td><td style=";">Mr. T</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;background-color: #FFFF00;;">Michael</td><td style=";">Mickey</td><td style=";">Mike</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;background-color: #FFFF00;;">George</td><td style=";">Gorge</td><td style=";">Porgy</td><td style=";">Pudding</td><td style=";">Pie</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;background-color: #FFFF00;;">Bill</td><td style=";">Bo</td><td style=";">Billy</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-style: italic;;">VBA will reference col A and find the actual name vertically</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-style: italic;;">Once the name is found, it can offset to the right and collect each name as listed in the row</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Nicknames</p><br /><br />
 

JohnnyBconfused

New Member
Joined
Jun 7, 2015
Messages
5

ADVERTISEMENT

Wow - that will be exponentially easier to maintain / update :) Yes - how can this be done?

Thank You!
 

RudiS

Active Member
Joined
May 7, 2015
Messages
349
Ignore the link above...

Here is an updated and improved version that will remove the nicknames and rebuild the list if you make changes (editions, deletions and added nicknames. It clears the nickname build in the master list and rebuilds it to incorporate the modifications to the nickname list...

Updated workbook and code.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,715
Messages
5,597,726
Members
414,169
Latest member
Preston_Cleric

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