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

  • Thread starter Thread starter Andrzej
  • Start date Start date
A

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
 
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.
 
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
 
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
 
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
 
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
 
My bad

--
Regards,
Tom Ogilvy

Andrzej said:
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
Click to expand...
 
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
 
Back
Top