need help opening text file

[armadeus]

What I need is to open a text file, search through column A for a specific
number lets say "9135". this number will appear twice in column A so I want
to copy everything between the instance of 9135 and paste in a worksheet in
the next available empty row. I am not a programmer and I am completely lost
any help would be appreciated!

Armadeus

 

[Tom Ogilvy]

Sub OpenAndCopy()
Dim rng as Range, rng1 as Range
dim sh as Worksheet, bk as Workbook
set sh = Activesheet
set bk = Workbooks.Open("C:\MyFolder\Myfile.csv")
set rng = Columns(1).find(9135)
if not rng is nothing then
set rng1 = columns(1).find(9135,rng)
if not rng1 is nothing then
Range(rng,rng1).Entirerow.copy Destination:=sh.Range("A1")
else
msgbox "Second instance not found"
end if
else
msgbox "First Instance not found
End if
bk.close Savechanges:=False
End sub

 

[armadeus]

Keeps giving the error 'First Instance not found' on a popup and doesn't
paste into worksheet.

 

[Tom Ogilvy]

It is looking for the number 9135 in a cell by itself. If it is a string,
then it won't find it or if it is part of a string. Here is the adjustment
if that is the case:

Sub OpenAndCopy()
Dim rng as Range, rng1 as Range
dim sh as Worksheet, bk as Workbook
set sh = Activesheet
set bk = Workbooks.Open("C:\MyFolder\Myfile.csv")
set rng = Columns(1).find(What:="9135", _
Lookin:=xlValues, LookAt:=xlPart)
if not rng is nothing then
set rng1 = columns(1).find(What:="9135",After:=rng, _
Lookin:=xlValues, LookAt:=xlPart)
if not rng1 is nothing then
Range(rng,rng1).Entirerow.copy Destination:=sh.Range("A1")
else
msgbox "Second instance not found"
end if
else
msgbox "First Instance not found
End if
bk.close Savechanges:=False
End sub

 

[armadeus]

Thanks Tom that did it!

It is looking for the number 9135 in a cell by itself. If it is a string,
then it won't find it or if it is part of a string. Here is the adjustment
if that is the case:

Sub OpenAndCopy()
Dim rng as Range, rng1 as Range
dim sh as Worksheet, bk as Workbook
set sh = Activesheet
set bk = Workbooks.Open("C:\MyFolder\Myfile.csv")
set rng = Columns(1).find(What:="9135", _
Lookin:=xlValues, LookAt:=xlPart)
if not rng is nothing then
set rng1 = columns(1).find(What:="9135",After:=rng, _
Lookin:=xlValues, LookAt:=xlPart)
if not rng1 is nothing then
Range(rng,rng1).Entirerow.copy Destination:=sh.Range("A1")
else
msgbox "Second instance not found"
end if
else
msgbox "First Instance not found
End if
bk.close Savechanges:=False
End sub