Previous Topic Next Topic

Loop backwards for searching a string

Closed
abhimanyu - Jul 15, 2012 at 02:33 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 17, 2012 at 10:05 AM
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
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 17, 2012 at 10:05 AM
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
0