Link to home
Create AccountLog in
Avatar of Euro5
Euro5Flag 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

Avatar of Bembi
Bembi
Flag of Germany image

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

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.     
Avatar of 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!