Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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 ***
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 48
Default 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 ***

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
runtime error '1004' application or object defined error Janis Excel Programming 4 November 18th 09 03:01 PM
runtime error '1004' application or object defined error. Please help deej Excel Programming 0 August 1st 07 09:26 AM
runtime 1004 error on deleting rows with blank cells Janis Excel Programming 5 July 18th 07 09:38 PM
Hiding Rows Error - "Runtime Error 1004 - Unable to set the hidden property of the range class" Punsterr Excel Programming 2 April 9th 07 05:32 PM
Excel 2003 Macro Error - Runtime error 1004 Cow Excel Discussion (Misc queries) 2 June 7th 05 01:40 PM


All times are GMT +1. The time now is 01:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"