runtime error 1004: how can i use rows.
 

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 code:(Excel 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 ***

runtime error 1004: how can i use rows.
 

Hi Milkar,

Try using:

Range(Rows(rowInQuotationsheetBegin),Rows(rowInQuo tationsheetEnd)).Select

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"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 code:(Excel 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 ***