Avatar of Euro5
Euro5
Flag for United States of America 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

VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Euro5

8/22/2022 - Mon
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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.     
Your help has saved me hundreds of hours of internet surfing.
fblack61
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!