VBa code to transfer rows base on a sequential number

[jacque]

I have the following string in sheet 1 column A. If you notice, the first left strings are a sequential number.
I want to copy the complete row to sheet 2 column A from for example number 158 to 170.
These numbers changes every run, actually the I have about 1800 rows. I like the code refers to cell B1, and B2, to obtain these numbers, and then run the macro.


154,"9001203","0007541","0000000000"



155,"0002551","0005548","0000000000"



156,"9019074","0002361","0000000000"



157,"0004501","0008363","0000000000"



158,"9016847","0005085","0000000000"



159,"0003769","0000501","0000000000"



160,"0003880","0001990","0000000000"



161,"9012481","0009726","0000000000"



162,"0004789","0009769","0000000000"



163,"9011637","0009674","0000000000"



164,"9007373","0009536","0000000000"



165,"9015222","0009814","0000000000"



166,"9013953","0009426","0000000000"



167,"9022671","0009808","0000000000"



168,"9003903","0009692","0000000000"



169,"9026207","0009793","0000000000"



170,"9025813","0009486","0000000000"



171,"0003574","0009408","0000000000"



172,"9006220","0009620","0000000000"



173,"9010121","0009634","0000000000"



174,"9024978","0009724","0000000000"

[offthelip]

try this:


Sub moved()
Dim rng As Range
Dim nums As Integer
b1 = Cells(1, 2)
b2 = Cells(2, 2)


lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))


For i = 1 To lastrow
comma = InStr(inarr(i, 1), ",")
If comma > 0 Then
nums = Left(inarr(i, 1), comma - 1)
If nums = b1 Then
startRow = i
End If
If nums = b2 Then
endrow = i
Exit For
End If
End If
Next i


rngtxt = startRow & ":" & endrow
ActiveWorkbook.Sheets("Sheet1").Range(rngtxt).EntireRow.Copy _
Destination:=ActiveWorkbook.Sheets("Sheet2").Range("A1")



End Sub

[snb]

Your desciption isn't concise enough. Please post a sample file.

[jacque]

Attached please find the sample file for your review. In the actual file there are about 1800 rows. The move should be between values provided in the B1 and B2.

The strings in column A, and values of B1 and B2 changes, which is calculated from another file.

Thank you for looking into it

[jacque]

Thank you for the help.
Upon running the macro i get an error on
nums = Left(inarr(i, 1), comma - 1)

I attached the file with your macro for your review

Again thanks for the help

[mdmackillop]

Dim nums As Long

[mdmackillop]

Sub Test()
With Sheets("Sheet1")
Set a = .Columns(1).Find(.Cells(1, 2) & "*", lookat:=xlWhole)
Set b = .Columns(1).Find(.Cells(2, 2) & "*", lookat:=xlWhole)
If Not a Is Nothing And Not b Is Nothing Then
.Range(a, b).Copy Sheets("Sheet2").Cells(1, 1)
Else
MsgBox "number not found"
End If
End With
End Sub

[jacque]

Great it works, thanks a lot. This macro is part of three other macros, now the program is final.

Again thanx

[jacque]

Great it works.

Thanks for the help

[snb]

Alternative:


Sub M_snb()
Sheet3.Cells(1).Offset(Sheet3.Cells(1, 2) - Left(Sheet3.Cells(1), 7)).Resize(Sheet3.Cells(2, 2) - Sheet3.Cells(1, 2) + 1).Copy Sheet1.Cells(1)
End Sub

or

Sub M_snb()
with Sheet3
.Cells(1).Offset(.Cells(1, 2) - Left(.Cells(1), 7)).Resize(.Cells(2, 2) - .Cells(1, 2) + 1).Copy Sheet1.Cells(1)
end with
End Sub