View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
M. M. is offline
external usenet poster
 
Posts: 18
Default Excel 97 "Find" metod

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