Search 12345 and insert row above

[donlincolnmre2]

Hello

I'm looking for a macro that will search the whole sheet for 12345 and if found then insert a row above and position the cursor in the Cell A of that inserted row, any help would be appreciated.

Thanks.

 

[Rick Rothstein]

I'm looking for a macro that will search the whole sheet for 12345 and if found then insert a row above and position the cursor in the Cell A of that inserted row, any help would be appreciated.

Will 12345 only exist once on the worksheet? If not, please explain about what should be selected.

 

[donlincolnmre2]

Yes only one time in the whole sheet.

 

[Rick Rothstein]

Yes only one time in the whole sheet.

Okay, give this macro a try...

Sub Find12345InsertRowAboveSelectColumnA()
  Dim Rw As Long
  On Error GoTo No12345
  Rw = ActiveSheet.UsedRange.Find(12345, , xlValues, xlWhole, , , , , False).Row
  Rows(Rw).Insert
  Cells(Rw, "A").Select
  Exit Sub
No12345:
  MsgBox "12345 could not be found!", vbCritical
End Sub

 

[donlincolnmre2]

Hello

I'm using Excel 2000 and below is the dummy data, when i run the macro its says not found.

columns

A B C

C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
12345
C 134123 1243
C
C

 

[Rick Rothstein]

I'm using Excel 2000...

I don't think this should matter for your current question, but you should mention this fact wherever you ask a question on this (or any other) forum given that your version of Excel is nearly 20 years old and a lot has changed in Excel since then.

...and below is the dummy data, when i run the macro its says not found.

columns

A B C

C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
C 134123 1243
12345
C 134123 1243
C
C

I cannot tell from your post what cell the 12345 is in, but are you sure that is all of the text in the cell (I am thinking you might have hidden spaces or non-breaking spaces in it)? Lets assume the 12345 is in cell A7 (correct the address if that guessed is wrong), put this formula in an empty cell and tell me what it returns...

=LEN(A1)

 

[donlincolnmre2]

Below is the code that was written by someone, it does find 12345 and then insert a row.

What i'm looking for is for macro to move one cell LEFT and then one cell UP (after 12345 is found)

Hopefully this helps.

Thanks.


Dim c As Range
Set rng = ActiveSheet.Range("b1:b5000")
For dblCounter = rng.Cells.Count To 1 Step -1
Set c = rng(dblCounter)
If c.Value Like "12345" Then
c.EntireRow.Insert
End If
Next dblCounter

 

[donlincolnmre2]

I don't think this should matter for your current question, but you should mention this fact wherever you ask a question on this (or any other) forum given that your version of Excel is nearly 20 years old and a lot has changed in Excel since then.




I cannot tell from your post what cell the 12345 is in, but are you sure that is all of the text in the cell (I am thinking you might have hidden spaces or non-breaking spaces in it)? Lets assume the 12345 is in cell A7 (correct the address if that guessed is wrong), put this formula in an empty cell and tell me what it returns...

=LEN(A1)

Here is the link to the picture of the data as to how it looks.

https://drive.google.com/open?id=1jOyR-FMN-Yyq2Q_VbVvGQa-7VFNgg1wY

 

[Rick Rothstein]

Maybe your version of Excel does matter (I'm thinking your version of Excel's VBA might not support the Find method that I used). Okay, since the code you post does work to insert the row, try modifying the code as I show in red below and see if that works for you...

Dim c As Range
Set rng = ActiveSheet.Range("b1:b5000")
For dblCounter = rng.Cells.Count To 1 Step -1
  Set c = rng(dblCounter)
  If c.Value Like "12345" Then
    c.EntireRow.Insert
    [B][COLOR="#FF0000"]c.Offset(-1,-1).Select[/COLOR][/B]
    [B][COLOR="#FF0000"]Exit For[/COLOR][/B]
  End If
Next dblCounter

 

[donlincolnmre2]

The cursor just stayed where, it finished macro, it didn't move 1 left and 1 up.