loop backwards for searching a string Closed

Question

Hello, 


Hi,

I want to run the loop backwards in order to search a string. The requirement is as below:

A	B	C

A5	KUY	copy
A6	        shirt
A7	        rat
A8	        mat
A9	        hat


I want to copy the rows from one sheet to another based on the string I am searching.
Lets say if the string I am searching from example above is "hat" in column C and there is no value corresponding to it in cell B9, then a loop should start to search for value in column B starting from B9 till the cell where it finds some value which in this case would be in cell B5 and value is "KUY". 

I want to have the code for looping in reverse order.

Any help would be much appreciated.

Thanks,
Abhimanyu

TrowaD · Answer

Hi Abhimanyu,

If I understand you correctly the following should work:

Sub MoveData()
Dim lRow, lRow2, x As Integer
Dim SV As String

lRow = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
SV = InputBox("Please input search value:")

Sheets("Sheet1").Select

    For Each cell In Range("C2:C" & lRow)
    x = 1

        Do
        x = x - 1

If cell.Value = SV And cell.Offset(x, -1).Value <> vbNullString Then
cell.Offset(x, 0).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & lRow2)
Exit Sub
End If

        Loop Until cell.Offset(x, 0).Row = 1

    Next cell
    
End Sub
Best regards,
Trowa

Loop backwards for searching a string

1 response

Similar discussions

Subscribe To Our Newsletter!