I have question.. I have problem with Cells.find
 

I have two file (p1.xls , p2,.xls). When I work in file (p2.xls) i need to
find something
in second file(p1.xls). Then write value from Cecha in the ActiveCell of
p2.xls, provided
that Cecha is found

I have button and code:
but this code does not work how I want !! because search in file p2....
(it work on inversely)
What I should modify ?? that it search in p1.xls ???


Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
Workbooks.Open Filename:= _
"C:\Documents and Settings\.....\p1.xls"
Set szukana = Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close ' ---p1.xls
?
Exit Sub
Else
Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox "Value " & Cecha & "was found" '--- in p1.xls ?
ActiveWorkbook.Close
ActiveCell.Value = Cecha '---in to
p2.xls ?
End If

End Sub



I will be grateful for every answer

I have question.. I have problem with Cells.find
 

Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open Filename:= _
("C:\Documents and Settings\.....\p1.xls")
Set szukana = bk.Worksheets(1).Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close ' ---p1.xls
Else
ActiveWorkbook.Close
ActiveCell.Value = Cecha '---in to
End If
End Sub

I am not following why you do the search twice.

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
I have two file (p1.xls , p2,.xls). When I work in file (p2.xls) i need to
find something
in second file(p1.xls). Then write value from Cecha in the ActiveCell of
p2.xls, provided
that Cecha is found

I have button and code:
but this code does not work how I want !! because search in file p2....
(it work on inversely)
What I should modify ?? that it search in p1.xls ???


Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
Workbooks.Open Filename:= _
"C:\Documents and Settings\.....\p1.xls"
Set szukana = Cells.Find(What:=Cecha, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close ' ---p1.x
ls
?
Exit Sub
Else
Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox "Value " & Cecha & "was found" '--- in p1.xls ?
ActiveWorkbook.Close
ActiveCell.Value = Cecha '---in to
p2.xls ?
End If

End Sub



I will be grateful for every answer

I have question.. I have problem with Cells.find
 

Hi Tom,

This it it wat I want

One more..

1) I would like: IF founding --- this cells activate

2) searching for all sheets in file p1.xls.. and not only in first sheet.

It is very important for me.

I modify your code: " set bk = Workbooks.Open _ Filename:= ( "C:\Documents
and Settings\.....\p1.xls") "

on my:

set bk = Workbooks.Open ( Filename:= "C:\Documents and
Settings\.....\p1.xls")

I have a question. You don't define bk ? it is correct ?

Geat thanks

Andrzej



U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open Filename:= _
("C:\Documents and Settings\.....\p1.xls")
Set szukana = bk.Worksheets(1).Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
Else
ActiveWorkbook.Close
ActiveCell.Value = Cecha '---in to
End If
End Sub

I am not following why you do the search twice.

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
I have two file (p1.xls , p2,.xls). When I work in file (p2.xls) i need
to
find something
in second file(p1.xls). Then write value from Cecha in the ActiveCell
of
p2.xls, provided
that Cecha is found

I have button and code:
but this code does not work how I want !! because search in file p2....
(it work on inversely)
What I should modify ?? that it search in p1.xls ???


Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
Workbooks.Open Filename:= _
"C:\Documents and Settings\.....\p1.xls"
Set szukana = Cells.Find(What:=Cecha, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.x
ls
?
Exit Sub
Else
Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox "Value " & Cecha & "was found" '--- in p1.xls ?
ActiveWorkbook.Close
ActiveCell.Value = Cecha '---in
to
p2.xls ?
End If

End Sub



I will be grateful for every answer

I have question.. I have problem with Cells.find
 

Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String
Dim bk as Workbook
Dim sh as Worksheet
Dim sh1 as Worksheet

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open(Filename:= "C:\Documents and Settings\.....\p1.xls")
set sh1 = bk.worksheets(bh.worksheets.count)
for each sh in bk.worksheets
Set szukana = sh.Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing and Then
if sh.name = sh1.Name then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close ' ---p1.xls
end if
Else
'ActiveWorkbook.Close
bk.Activate
sh.Activate
szukana.Activate
End If
Next sh
End Sub

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
Hi Tom,

This it it wat I want

One more..

1) I would like: IF founding --- this cells activate

2) searching for all sheets in file p1.xls.. and not only in first sheet.

It is very important for me.

I modify your code: " set bk = Workbooks.Open _ Filename:= ( "C:\Documents
and Settings\.....\p1.xls") "

on my:

set bk = Workbooks.Open ( Filename:= "C:\Documents and
Settings\.....\p1.xls")

I have a question. You don't define bk ? it is correct ?

Geat thanks

Andrzej



U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open Filename:= _
("C:\Documents and Settings\.....\p1.xls")
Set szukana = bk.Worksheets(1).Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
Else
ActiveWorkbook.Close
ActiveCell.Value = Cecha '---in
to
End If
End Sub

I am not following why you do the search twice.

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
I have two file (p1.xls , p2,.xls). When I work in file (p2.xls) i
need
to
find something
in second file(p1.xls). Then write value from Cecha in the ActiveCell
of
p2.xls, provided
that Cecha is found

I have button and code:
but this code does not work how I want !! because search in file
p2....
(it work on inversely)
What I should modify ?? that it search in p1.xls ???


Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
Workbooks.Open Filename:= _
"C:\Documents and Settings\.....\p1.xls"
Set szukana = Cells.Find(What:=Cecha, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.x
ls
?
Exit Sub
Else
Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox "Value " & Cecha & "was found" '--- in p1.xls ?
ActiveWorkbook.Close
ActiveCell.Value = Cecha '---in
to
p2.xls ?
End If

End Sub



I will be grateful for every answer

I have question.. I have problem with Cells.find
 

You are great !!

OK !! works.. THANK YOU ("Dziêkujê" in Polish)

you made tiny mistake in code:

yours code: set sh1 = bk.worksheets(bh.worksheets.count)

on my: set sh1 = bk.worksheets(bk.worksheets.count)

Andrzej

Pozdrowienia z Polski


U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String
Dim bk as Workbook
Dim sh as Worksheet
Dim sh1 as Worksheet

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open(Filename:= "C:\Documents and
Settings\.....\p1.xls")
set sh1 = bk.worksheets(bh.worksheets.count)
for each sh in bk.worksheets
Set szukana = sh.Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing and Then
if sh.name = sh1.Name then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
end if
Else
'ActiveWorkbook.Close
bk.Activate
sh.Activate
szukana.Activate
End If
Next sh
End Sub

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
Hi Tom,

This it it wat I want

One more..

1) I would like: IF founding --- this cells activate

2) searching for all sheets in file p1.xls.. and not only in first
sheet.

It is very important for me.

I modify your code: " set bk = Workbooks.Open _ Filename:= (
"C:\Documents
and Settings\.....\p1.xls") "

on my:

set bk = Workbooks.Open ( Filename:= "C:\Documents and
Settings\.....\p1.xls")

I have a question. You don't define bk ? it is correct ?

Geat thanks

Andrzej



U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open Filename:= _
("C:\Documents and Settings\.....\p1.xls")
Set szukana = bk.Worksheets(1).Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
Else
ActiveWorkbook.Close
ActiveCell.Value = Cecha '---in
to
End If
End Sub

I am not following why you do the search twice.

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
I have two file (p1.xls , p2,.xls). When I work in file (p2.xls) i
need
to
find something
in second file(p1.xls). Then write value from Cecha in the
ActiveCell
of
p2.xls, provided
that Cecha is found

I have button and code:
but this code does not work how I want !! because search in file
p2....
(it work on inversely)
What I should modify ?? that it search in p1.xls ???


Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
Workbooks.Open Filename:= _
"C:\Documents and Settings\.....\p1.xls"
Set szukana = Cells.Find(What:=Cecha, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.x
ls
?
Exit Sub
Else
Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox "Value " & Cecha & "was found" '--- in p1.xls
?
ActiveWorkbook.Close
ActiveCell.Value = Cecha
'---in
to
p2.xls ?
End If

End Sub



I will be grateful for every answer

I have question.. I have problem with Cells.find
 

One more...
what it happend if p1.xls is open. And I run this code..
I need some protect.
For example If p1.xls is open then
MsgBox "File p1.xls is open. Try again later"

Many people will be work with this files..

Andrzej


U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String
Dim bk as Workbook
Dim sh as Worksheet
Dim sh1 as Worksheet

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open(Filename:= "C:\Documents and
Settings\.....\p1.xls")
set sh1 = bk.worksheets(bh.worksheets.count)
for each sh in bk.worksheets
Set szukana = sh.Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing and Then
if sh.name = sh1.Name then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
end if
Else
'ActiveWorkbook.Close
bk.Activate
sh.Activate
szukana.Activate
End If
Next sh
End Sub

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
Hi Tom,

This it it wat I want

One more..

1) I would like: IF founding --- this cells activate

2) searching for all sheets in file p1.xls.. and not only in first
sheet.

It is very important for me.

I modify your code: " set bk = Workbooks.Open _ Filename:= (
"C:\Documents
and Settings\.....\p1.xls") "

on my:

set bk = Workbooks.Open ( Filename:= "C:\Documents and
Settings\.....\p1.xls")

I have a question. You don't define bk ? it is correct ?

Geat thanks

Andrzej



U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open Filename:= _
("C:\Documents and Settings\.....\p1.xls")
Set szukana = bk.Worksheets(1).Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
Else
ActiveWorkbook.Close
ActiveCell.Value = Cecha '---in
to
End If
End Sub

I am not following why you do the search twice.

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
I have two file (p1.xls , p2,.xls). When I work in file (p2.xls) i
need
to
find something
in second file(p1.xls). Then write value from Cecha in the
ActiveCell
of
p2.xls, provided
that Cecha is found

I have button and code:
but this code does not work how I want !! because search in file
p2....
(it work on inversely)
What I should modify ?? that it search in p1.xls ???


Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
Workbooks.Open Filename:= _
"C:\Documents and Settings\.....\p1.xls"
Set szukana = Cells.Find(What:=Cecha, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.x
ls
?
Exit Sub
Else
Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox "Value " & Cecha & "was found" '--- in p1.xls
?
ActiveWorkbook.Close
ActiveCell.Value = Cecha
'---in
to
p2.xls ?
End If

End Sub



I will be grateful for every answer

I have question.. I have problem with Cells.find
 

My bad

--
Regards,
Tom Ogilvy

"Andrzej" wrote in message
...
You are great !!

OK !! works.. THANK YOU ("Dziêkujê" in Polish)

you made tiny mistake in code:

yours code: set sh1 = bk.worksheets(bh.worksheets.count)

on my: set sh1 = bk.worksheets(bk.worksheets.count)

Andrzej

Pozdrowienia z Polski


U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String
Dim bk as Workbook
Dim sh as Worksheet
Dim sh1 as Worksheet

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open(Filename:= "C:\Documents and
Settings\.....\p1.xls")
set sh1 = bk.worksheets(bh.worksheets.count)
for each sh in bk.worksheets
Set szukana = sh.Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing and Then
if sh.name = sh1.Name then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
end if
Else
'ActiveWorkbook.Close
bk.Activate
sh.Activate
szukana.Activate
End If
Next sh
End Sub

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
Hi Tom,

This it it wat I want

One more..

1) I would like: IF founding --- this cells activate

2) searching for all sheets in file p1.xls.. and not only in first
sheet.

It is very important for me.

I modify your code: " set bk = Workbooks.Open _ Filename:= (
"C:\Documents
and Settings\.....\p1.xls") "

on my:

set bk = Workbooks.Open ( Filename:= "C:\Documents and
Settings\.....\p1.xls")

I have a question. You don't define bk ? it is correct ?

Geat thanks

Andrzej



U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open Filename:= _
("C:\Documents and Settings\.....\p1.xls")
Set szukana = bk.Worksheets(1).Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
Else
ActiveWorkbook.Close
ActiveCell.Value = Cecha
'---in
to
End If
End Sub

I am not following why you do the search twice.

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
I have two file (p1.xls , p2,.xls). When I work in file (p2.xls) i
need
to
find something
in second file(p1.xls). Then write value from Cecha in the
ActiveCell
of
p2.xls, provided
that Cecha is found

I have button and code:
but this code does not work how I want !! because search in file
p2....
(it work on inversely)
What I should modify ?? that it search in p1.xls ???


Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
Workbooks.Open Filename:= _
"C:\Documents and Settings\.....\p1.xls"
Set szukana = Cells.Find(What:=Cecha, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls
?
ActiveWorkbook.Close
' ---p1.x
ls
?
Exit Sub
Else
Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox "Value " & Cecha & "was found" '--- in
p1.xls
?
ActiveWorkbook.Close
ActiveCell.Value = Cecha
'---in
to
p2.xls ?
End If

End Sub



I will be grateful for every answer

I have question.. I have problem with Cells.find
 

replace
set bk = Workbooks.Open(Filename:= "C:\Documents and
Settings\.....\p1.xls")
with

On Error Resume Next
set bk = WorkBooks(p1.xls)
On Error goto 0
if bk is nothing then
set bk = Workbooks.Open(Filename:= "C:\Documents and
Settings\.....\p1.xls")
End if

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
One more...
what it happend if p1.xls is open. And I run this code..
I need some protect.
For example If p1.xls is open then
MsgBox "File p1.xls is open. Try again later"

Many people will be work with this files..

Andrzej


U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String
Dim bk as Workbook
Dim sh as Worksheet
Dim sh1 as Worksheet

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open(Filename:= "C:\Documents and
Settings\.....\p1.xls")
set sh1 = bk.worksheets(bh.worksheets.count)
for each sh in bk.worksheets
Set szukana = sh.Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing and Then
if sh.name = sh1.Name then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
end if
Else
'ActiveWorkbook.Close
bk.Activate
sh.Activate
szukana.Activate
End If
Next sh
End Sub

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
Hi Tom,

This it it wat I want

One more..

1) I would like: IF founding --- this cells activate

2) searching for all sheets in file p1.xls.. and not only in first
sheet.

It is very important for me.

I modify your code: " set bk = Workbooks.Open _ Filename:= (
"C:\Documents
and Settings\.....\p1.xls") "

on my:

set bk = Workbooks.Open ( Filename:= "C:\Documents and
Settings\.....\p1.xls")

I have a question. You don't define bk ? it is correct ?

Geat thanks

Andrzej



U¿ytkownik "Tom Ogilvy" napisa³ w wiadomo¶ci
...
Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
set bk = Workbooks.Open Filename:= _
("C:\Documents and Settings\.....\p1.xls")
Set szukana = bk.Worksheets(1).Cells.Find(What:=Cecha, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:= xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls ?
ActiveWorkbook.Close
' ---p1.xls
Else
ActiveWorkbook.Close
ActiveCell.Value = Cecha
'---in
to
End If
End Sub

I am not following why you do the search twice.

--
Regards,
Tom Ogilvy


"Andrzej" wrote in message
...
I have two file (p1.xls , p2,.xls). When I work in file (p2.xls) i
need
to
find something
in second file(p1.xls). Then write value from Cecha in the
ActiveCell
of
p2.xls, provided
that Cecha is found

I have button and code:
but this code does not work how I want !! because search in file
p2....
(it work on inversely)
What I should modify ?? that it search in p1.xls ???


Sub CommandButton3_Click() '------in file p2.xls
Dim szukana As Range
Dim Cecha as String

Cecha = InputBox("Enter the name", "Enter value")
If Cecha = "" Then Exit Sub
Workbooks.Open Filename:= _
"C:\Documents and Settings\.....\p1.xls"
Set szukana = Cells.Find(What:=Cecha, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If szukana Is Nothing Then
MsgBox "Sorry, but " & Cecha & "was not found" '--- in p1.xls
?
ActiveWorkbook.Close
' ---p1.x
ls
?
Exit Sub
Else
Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=
_
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox "Value " & Cecha & "was found" '--- in
p1.xls
?
ActiveWorkbook.Close
ActiveCell.Value = Cecha
'---in
to
p2.xls ?
End If

End Sub



I will be grateful for every answer