MS Excel Macro - insert row and text

May 17, 2010 at 12:56:06
Specs: Windows XP
Hello, I have to find a string of text in row A (Call it String10), insert a row above that line and then insert a "~" in that new row in column B of the newly inserted row. I can do the search for the string and obviously know how to use the insert row but then inserting the text in that cell is the issue. I also need this to run through each cell in column A and do this for all instances where "String10" is found. All the cells in Column A are blank except for the ones with "String10".

Thanks again,

James


See More: MS Excel Macro - insert row and text

Report •


#1
May 17, 2010 at 15:45:15
re: I can do the search for the string and obviously know how to use the insert row but then inserting the text in that cell is the issue.

Why not share with us how you are dong the search and inserting the row? Maybe we just need to modify your code a little bit.


Report •

#2
May 19, 2010 at 15:35:49
This is what I have so far, that is not working:

Sub row_insert3()

a = 1

Do Until a = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
If ActiveSheet.Cells(a, 1).Value = "String10" Then
ActiveSheet.Rows(a).Insert
ActiveSheet.Cells(b - 1, 1).Value = "~"
End If
a = a + 1
Loop

End Sub


Report •

#3
May 19, 2010 at 18:25:17
Since it looks like you're willing to try a little coding on your own, I'm going to suggest that you look at the Find method in the VBA help files.

The Find method is very powerful and much more efficient than looping through a range checking every cell with an "If" statement.

The example that is given in the Help file just needs a little modification to make it do what you want to do.

I'm confident in saying that because I just did it. :-)

I will offer one hint, which won't make any sense until you look at (and understand) the example in the Help files.

The example contains this line:

firstAddress = c.Address

It uses that line to help determine if it has found all of the strings it is searching for.

Keep in mind that once you insert a row above the first String10, the address where it found the first String10 no longer contains String10 since you moved it down one row.

Not only will you need to modify what the example does within the Do Loop (that's the easy part) you'll also have to modify the "firstAddress = c.Address" line to make sure the code knows that it is done.

I assume you know about using F8 to single step through the code. I also assume (hope) that you know about the VBA Watch window. They are both very powerful tools for troubleshooting code.

Give the Find method a shot and let us know how it works out for you. We're here to help if you need it.


Report •

Related Solutions

#4
June 3, 2010 at 03:53:34
Please tell everyone if your problem is solved. We would like to
know more about Excel and the solution to problems that we
might encounter as well. Thanks!

excel development


Report •


Ask Question