I want to do a search in the "all" sheet with value I type in the
TextBoxItem1 in "sheet1" . And I can find a block(with the
rowInQuotationsheetBegin and rowInQuotationsheetEnd )in the "all" sheet,
go copy/paste to the sheet "Quotationsheet".

my codeExcel 2003)

Private Sub CommandButtonItem1_Click()
Dim item As Long
Dim rowInQuotationsheetBegin As Long
Dim rowInQuotationsheetEnd As Long
Dim tmp As String

item = Worksheets("Sheet1").TextBoxItem1.Value


For i = 8 To 4000

If item = Worksheets("all").Cells(i, 6).Value Then
rowInQuotationsheetBegin = i
For j = i + 1 To i + 20

If Worksheets("all").Cells(j, 5).Value = "ITEM NO." Then
rowInQuotationsheetEnd = j - 1
Sheets("all").Select
ActiveSheet.Unprotect
Application.CutCopyMode = False
Rows("8:23").Select
'Rows(rowInQuotationsheetBegin : rowInQuotationsheetEnd).Select
Selection.Copy
Sheets("QUOTATIONSHEET").Select
ActiveSheet.Paste
End If
Next j

End If

Next i

End Sub

at first I want to use

'Rows([rowInQuotationsheetBegin : rowInQuotationsheetEnd]).Select

to get the rows i need, but I dont know how to use Rows with variables.
Then I try to test with

Rows("8:23").Select

But I still got runtime error 1004. some code I modified direct from
Macro, maybe it is not so professional. So how can I fix this problem?

Thx.

*** Sent via Developersdex http://www.developersdex.com ***

Rows(rowInQuotationsheetBegin & " :" & rowInQuotationsheetEnd).Select

Make sure you keep the spaces around the Ampersand symbols and make sure the
variables are spelled correctly (using "Option Explicit" at the top of the
module above any code will check this for you).



--
Regards,
Tom Ogilvy

"milkar miao" wrote:





I want to do a search in the "all" sheet with value I type in the
TextBoxItem1 in "sheet1" . And I can find a block(with the
rowInQuotationsheetBegin and rowInQuotationsheetEnd )in the "all" sheet,
go copy/paste to the sheet "Quotationsheet".

my codeExcel 2003)

Private Sub CommandButtonItem1_Click()
Dim item As Long
Dim rowInQuotationsheetBegin As Long
Dim rowInQuotationsheetEnd As Long
Dim tmp As String

item = Worksheets("Sheet1").TextBoxItem1.Value


For i = 8 To 4000

If item = Worksheets("all").Cells(i, 6).Value Then
rowInQuotationsheetBegin = i
For j = i + 1 To i + 20

If Worksheets("all").Cells(j, 5).Value = "ITEM NO." Then
rowInQuotationsheetEnd = j - 1
Sheets("all").Select
ActiveSheet.Unprotect
Application.CutCopyMode = False
Rows("8:23").Select
'Rows(rowInQuotationsheetBegin : rowInQuotationsheetEnd).Select
Selection.Copy
Sheets("QUOTATIONSHEET").Select
ActiveSheet.Paste
End If
Next j

End If

Next i

End Sub

at first I want to use

'Rows([rowInQuotationsheetBegin : rowInQuotationsheetEnd]).Select

to get the rows i need, but I dont know how to use Rows with variables.
Then I try to test with

Rows("8:23").Select

But I still got runtime error 1004. some code I modified direct from
Macro, maybe it is not so professional. So how can I fix this problem?

Thx.

*** Sent via Developersdex http://www.developersdex.com ***