need help opening text file
 

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

need help opening text file
 

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

--
Regards,
Tom Ogilvy

"armadeus" wrote in message
...
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

need help opening text file
 

Keeps giving the error 'First Instance not found' on a popup and doesn't
paste into worksheet.
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"armadeus" wrote in message
...
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

need help opening text file
 

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

--
Regards,
Tom Ogilvy


"armadeus" wrote in message
...
Keeps giving the error 'First Instance not found' on a popup and doesn't
paste into worksheet.
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"armadeus" wrote in message
...
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

need help opening text file
 

Thanks Tom that did it!
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"armadeus" wrote in message
...
Keeps giving the error 'First Instance not found' on a popup and doesn't
paste into worksheet.
"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"armadeus" wrote in message
...
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