Excel 97 "Find" metod

  • Thread starter Thread starter M.
  • Start date Start date
M

M.

i'd like to use find metod on range object. i have two workbooks, first one
have cba code, second one have searched data. I was put one first worksheet
in first workbook one textBox, when I press ENTER, subrutines (procedure)
start execution and try to find in second workbook with only one worksheet
same data. My code look like this one:

Private Sub txtIzborMM_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = Asc(Chr(13)) Then
'Workbooks("tabela-pogodbe.xls").Worksheets(1).Activate
KopirajNajdeneVrstice (txtIzborMM)
End If
End Sub

in module i put this part of code:

Public Sub KopirajNajdeneVrstice(txtIzborMM As String)
Dim celMM As Range
Dim firstAddress
Dim PogodbeWorksheet As Worksheet
Dim StartWorksheet As Worksheet
Dim stRow As Long
Dim stringRow As String

stRow = 11 'number of row where i would like to copy finded item (data)
Set PogodbeWorksheet = Workbooks("tabela-pogodbe.xls").Worksheets(1)
PogodbeWorksheet.Activate
With PogodbeWorksheet.Range("MM")
Set celMM = .Find(txtIzborMM, LookIn:=xlValues, LookAt:=xlWhole)
If celMM Is Nothing Then
MsgBox "Wrong MM or MM not exist in tabela-pogodbe.xls!"
Exit Sub
Else
firstAddress = celMM.Address

Do
stringRow = CStr(stRow) & ":" & CStr(stRow)
.Rows(celMM.Row).Copy Destination:=StartWorksheet.Range(stringRow)
Set celMM = .FindNext(celMM)
stRow = stRow + 1
Loop While Not celMM Is Nothing And celMM.Address <> firstAddress
End If
End With

End Sub

Whene I press ENTER VBA write this error msg Mistake '1004, Metod range
<<objects>>_Worksheet<< was canceled
 
I forget to explain where applicaton stoped, error was generatet in this
line:
With PogodbeWorksheet.Range("MM")

If i change this line with this:
With Workbooks("tabela-pogodbe.xls").Worksheets(1).Range("MM")

code execution stoped in the same line except msg do not have same
eplanation:
Mistake '1004, protertis Find object's Range can't find (i translate this
form my language).

Regards,

Milos
 
Is there a range named "MM" on that sheet?

It sounds like there isn't.

Were you trying to search column M?

then
With PogodbeWorksheet.Range("M:M")
should work.

(Maybe it was just a typo!)
 
Yes, "MM" is a range on second workbook!


Dave Peterson said:
Is there a range named "MM" on that sheet?

It sounds like there isn't.

Were you trying to search column M?

then
With PogodbeWorksheet.Range("M:M")
should work.

(Maybe it was just a typo!)
Click to expand...
 
Maybe worksheets(1) isn't the worksheet you expected?

Have you rearranged your worksheets?
 
Back
Top