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

[Andrzej]

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

 

[Tom Ogilvy]

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.

 

[Andrzej]

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

 

[Tom Ogilvy]

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

 

[Andrzej]

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

 

[Andrzej]

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

 

[Tom Ogilvy]

My bad

--
Regards,
Tom Ogilvy

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


p1.xls

 

[Tom Ogilvy]

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