ExcelBanter - Excel 97 "Find" metod

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Excel 97 "Find" metod (https://www.excelbanter.com/excel-programming/275211-excel-97-find-metod.html)

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

Excel 97 "Find" metod

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

Excel 97 "Find" metod

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!)

"M." wrote:

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

--

Dave Peterson

Excel 97 "Find" metod

Yes, "MM" is a range on second workbook!

"Dave Peterson" wrote in message
...
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!)

"M." wrote:

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

--

Dave Peterson

Excel 97 "Find" metod

Maybe worksheets(1) isn't the worksheet you expected?

Have you rearranged your worksheets?

"M." wrote:

Yes, "MM" is a range on second workbook!

"Dave Peterson" wrote in message
...
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!)

"M." wrote:

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

--

Dave Peterson

--

Dave Peterson