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 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 *** |
runtime error 1004: how can i use rows.
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 *** |
All times are GMT +1. The time now is 01:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com