asked on

VBA find and move down 1 row

I have been using this code, but now I believe it is deleting the row instead of moving the text one cell down!

I need to find these numbers in the array (part) in the row D and move the text down one row.
If it finds 63110 in D3, move it to D4.
HELP!??

    Worksheets("Sheet1").Activate
    Set Ws = ActiveSheet
    
    With Ws
        varArray = Array("63110", "63120", "63130", "63140", "63150", "63100", "63395", "66045")
        
        For lngEntry = 0 To UBound(varArray)
            Set rngFound = .Cells.Find(What:=varArray(lngEntry), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                MatchCase:=False, SearchFormat:=False)
            If Not rngFound Is Nothing Then
                strSave = rngFound
                lngRow = rngFound.Row
                .Cells(lngRow, "D").EntireRow.Delete
                .Cells(lngRow, "D") = strSave
            End If
        Next
    End With
End Sub

Open in new window

Bembi

Yes, it deletes the row and writes the content into the next following one (what is the same at the end).
You may work with offset. If you have the current Rage Object of the found cell, you can use Rage.Offset(row,column) to refer to a cell, which is relative (rows, columns) to the current object...
Range("D3").Offset(1,0) is the field under D3 = D4

Euro5

ASKER

I am getting an error at strSave = ringFound.Offset(1, 0)

Sub move()
Dim rngFound As Range
Dim ws As Worksheet
Dim varArray As Variant
Dim lngEntry As Long
Dim strSave As String
Dim lngRow As Long
Dim downRow As String


 Worksheets("Sheet1").Activate   
 Set ws = ActiveSheet
    
    With ws
        varArray = Array("60200", "62200", "62500", "66000")
        
        For lngEntry = 0 To UBound(varArray)
            Set rngFound = .Cells.Find(What:=varArray(lngEntry), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                MatchCase:=False, SearchFormat:=False)
            If Not rngFound Is Nothing Then
                strSave = rngFound
                lngRow = rngFound.Row
                
                strSave = ringFound.Offset(1, 0)
            End If
        Next
    End With
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Euro5

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bembi

Yes, just a typo in the first code, as "ringFound" is not defined, and even in the wrong direction.

Let me add the comment, that it is usual to accept the solution of the expert, which gave you the hint to solve your problem. This is just the honor for the experts here, whichs takes the time to answer your question.

You accepted yourself as solutions, what means you ignore the experts help.
Doing this may have the effect, that some of the experts will ignore you in the future.

Euro5

ASKER

Sorry, Bembi , you are right. I closed that in haste!
I see that I didn't capture all of the code - and even revised it a little more to rngFound.Clear

So the full solution relied your suggestion WAS the key to getting it to work!
Thanks for pointing this out!