Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to next empty line
Hi,
Very new to this so would be grateful for any help. I'm trying to copy rows from one workbook to another when they meet a certain criteria. But I do not want to overwrite previously copied lines. I want to add new rows, that meet the criteria, to the end of the older copied data. Dont know whether its relevent but to make sure that lines that are already on sheet 6 aren't duplicated, I have a lookup function in col 23 of sheet 1 that brings back the unique identifier if it is already on sheet 6. I know this is very convaluted! Sub UnmatchedPOs() Set rng = Sheets(1).Range("A:V").Rows Set rngTo = Sheets(6).Range("A1").End(xlDown) For Each r In rng If r.Cells(3).Value = "NO" Then If r.Cells(9).Value = DateValue("April, 01, 2007") Then If r.Cells(9).Value < DateValue("April 01, 2008") Then If Not r.Cells(6).Value = r.Cells(23).Value Then i = i + 1 r.Copy rngTo(i) End If End If End If End If Next End Sub When I run this I get: 1004 - Application defined or object defined error Many Thanks, Jig |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to next empty line
change the following
from: r.Copy rngTo(i) to: r.Copy destination:=rngTo(i) Or if it is only one cell rngTo(i).value = r.value " wrote: Hi, Very new to this so would be grateful for any help. I'm trying to copy rows from one workbook to another when they meet a certain criteria. But I do not want to overwrite previously copied lines. I want to add new rows, that meet the criteria, to the end of the older copied data. Dont know whether its relevent but to make sure that lines that are already on sheet 6 aren't duplicated, I have a lookup function in col 23 of sheet 1 that brings back the unique identifier if it is already on sheet 6. I know this is very convaluted! Sub UnmatchedPOs() Set rng = Sheets(1).Range("A:V").Rows Set rngTo = Sheets(6).Range("A1").End(xlDown) For Each r In rng If r.Cells(3).Value = "NO" Then If r.Cells(9).Value = DateValue("April, 01, 2007") Then If r.Cells(9).Value < DateValue("April 01, 2008") Then If Not r.Cells(6).Value = r.Cells(23).Value Then i = i + 1 r.Copy rngTo(i) End If End If End If End If Next End Sub When I run this I get: 1004 - Application defined or object defined error Many Thanks, Jig |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to next empty line
On 8 Nov, 18:44, Joel wrote:
change the following from: r.Copy rngTo(i) to: r.Copy destination:=rngTo(i) Or if it is only one cell rngTo(i).value = r.value " wrote: Hi, Very new to this so would be grateful for any help. I'm trying to copy rows from one workbook to another when they meet a certain criteria. But I do not want to overwrite previously copied lines. I want to add new rows, that meet the criteria, to the end of the older copied data. Dont know whether its relevent but to make sure that lines that are already on sheet 6 aren't duplicated, I have a lookup function in col 23 of sheet 1 that brings back the unique identifier if it is already on sheet 6. I know this is very convaluted! Sub UnmatchedPOs() Set rng = Sheets(1).Range("A:V").Rows Set rngTo = Sheets(6).Range("A1").End(xlDown) For Each r In rng If r.Cells(3).Value = "NO" Then If r.Cells(9).Value = DateValue("April, 01, 2007") Then If r.Cells(9).Value < DateValue("April 01, 2008") Then If Not r.Cells(6).Value = r.Cells(23).Value Then i = i + 1 r.Copy rngTo(i) End If End If End If End If Next End Sub When I run this I get: 1004 - Application defined or object defined error Many Thanks, Jig- Hide quoted text - - Show quoted text - Hi Joel, I still got the above error, with the text you said to put in. Many Thanks, Jig |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to next empty line
I'm running with excel 2003 and do not get any errors either with the
original code or my modification. I 've tried making some changes to the worksheets to see if I get the errror and I'm not able to duplicate this error with your code. Can you tell me which line is failing? "Jeegna" wrote: On 8 Nov, 18:44, Joel wrote: change the following from: r.Copy rngTo(i) to: r.Copy destination:=rngTo(i) Or if it is only one cell rngTo(i).value = r.value " wrote: Hi, Very new to this so would be grateful for any help. I'm trying to copy rows from one workbook to another when they meet a certain criteria. But I do not want to overwrite previously copied lines. I want to add new rows, that meet the criteria, to the end of the older copied data. Dont know whether its relevent but to make sure that lines that are already on sheet 6 aren't duplicated, I have a lookup function in col 23 of sheet 1 that brings back the unique identifier if it is already on sheet 6. I know this is very convaluted! Sub UnmatchedPOs() Set rng = Sheets(1).Range("A:V").Rows Set rngTo = Sheets(6).Range("A1").End(xlDown) For Each r In rng If r.Cells(3).Value = "NO" Then If r.Cells(9).Value = DateValue("April, 01, 2007") Then If r.Cells(9).Value < DateValue("April 01, 2008") Then If Not r.Cells(6).Value = r.Cells(23).Value Then i = i + 1 r.Copy rngTo(i) End If End If End If End If Next End Sub When I run this I get: 1004 - Application defined or object defined error Many Thanks, Jig- Hide quoted text - - Show quoted text - Hi Joel, I still got the above error, with the text you said to put in. Many Thanks, Jig |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to next empty line
On 9 Nov, 11:40, Joel wrote:
I'm running with excel 2003 and do not get any errors either with the original code or my modification. I 've tried making some changes to the worksheets to see if I get the errror and I'm not able to duplicate this error with your code. Can you tell me which line is failing? "Jeegna" wrote: On 8 Nov, 18:44, Joel wrote: change the following from: r.Copy rngTo(i) to: r.Copy destination:=rngTo(i) Or if it is only one cell rngTo(i).value = r.value " wrote: Hi, Very new to this so would be grateful for any help. I'm trying to copy rows from one workbook to another when they meet a certain criteria. But I do not want to overwrite previously copied lines. I want to add new rows, that meet the criteria, to the end of the older copied data. Dont know whether its relevent but to make sure that lines that are already on sheet 6 aren't duplicated, I have a lookup function in col 23 of sheet 1 that brings back the unique identifier if it is already on sheet 6. I know this is very convaluted! Sub UnmatchedPOs() Set rng = Sheets(1).Range("A:V").Rows Set rngTo = Sheets(6).Range("A1").End(xlDown) For Each r In rng If r.Cells(3).Value = "NO" Then If r.Cells(9).Value = DateValue("April, 01, 2007") Then If r.Cells(9).Value < DateValue("April 01, 2008") Then If Not r.Cells(6).Value = r.Cells(23).Value Then i = i + 1 r.Copy rngTo(i) End If End If End If End If Next End Sub When I run this I get: 1004 - Application defined or object defined error Many Thanks, Jig- Hide quoted text - - Show quoted text - Hi Joel, I still got the above error, with the text you said to put in. Many Thanks, Jig- Hide quoted text - - Show quoted text - I'm running Excel 2000 so could that be it? When I hit debug, the line that is highlighted is the one you asked me to change. It was that line before I changed it as well. Cheers, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to next empty line
I couldn't get it to fail. I have no idea. Because this is the only polace
in the code where rngTo is used it probably has something to do with this range try adding a line after this instruction to help you find the problem Set rngTo = Sheets(6).Range("A1").End(xlDown) new line for testing rngTo.select stop the code after this line is executed and see if the rngTo is selected. "Jeegna" wrote: On 9 Nov, 11:40, Joel wrote: I'm running with excel 2003 and do not get any errors either with the original code or my modification. I 've tried making some changes to the worksheets to see if I get the errror and I'm not able to duplicate this error with your code. Can you tell me which line is failing? "Jeegna" wrote: On 8 Nov, 18:44, Joel wrote: change the following from: r.Copy rngTo(i) to: r.Copy destination:=rngTo(i) Or if it is only one cell rngTo(i).value = r.value " wrote: Hi, Very new to this so would be grateful for any help. I'm trying to copy rows from one workbook to another when they meet a certain criteria. But I do not want to overwrite previously copied lines. I want to add new rows, that meet the criteria, to the end of the older copied data. Dont know whether its relevent but to make sure that lines that are already on sheet 6 aren't duplicated, I have a lookup function in col 23 of sheet 1 that brings back the unique identifier if it is already on sheet 6. I know this is very convaluted! Sub UnmatchedPOs() Set rng = Sheets(1).Range("A:V").Rows Set rngTo = Sheets(6).Range("A1").End(xlDown) For Each r In rng If r.Cells(3).Value = "NO" Then If r.Cells(9).Value = DateValue("April, 01, 2007") Then If r.Cells(9).Value < DateValue("April 01, 2008") Then If Not r.Cells(6).Value = r.Cells(23).Value Then i = i + 1 r.Copy rngTo(i) End If End If End If End If Next End Sub When I run this I get: 1004 - Application defined or object defined error Many Thanks, Jig- Hide quoted text - - Show quoted text - Hi Joel, I still got the above error, with the text you said to put in. Many Thanks, Jig- Hide quoted text - - Show quoted text - I'm running Excel 2000 so could that be it? When I hit debug, the line that is highlighted is the one you asked me to change. It was that line before I changed it as well. Cheers, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to next empty line
On 9 Nov, 12:51, Joel wrote:
I couldn't get it to fail. I have no idea. Because this is the only polace in the code where rngTo is used it probably has something to do with this range try adding a line after this instruction to help you find the problem Set rngTo = Sheets(6).Range("A1").End(xlDown) new line for testing rngTo.select stop the code after this line is executed and see if the rngTo is selected. "Jeegna" wrote: On 9 Nov, 11:40, Joel wrote: I'm running with excel 2003 and do not get any errors either with the original code or my modification. I 've tried making some changes to the worksheets to see if I get the errror and I'm not able to duplicate this error with your code. Can you tell me which line is failing? "Jeegna" wrote: On 8 Nov, 18:44, Joel wrote: change the following from: r.Copy rngTo(i) to: r.Copy destination:=rngTo(i) Or if it is only one cell rngTo(i).value = r.value " wrote: Hi, Very new to this so would be grateful for any help. I'm trying to copy rows from one workbook to another when they meet a certain criteria. But I do not want to overwrite previously copied lines. I want to add new rows, that meet the criteria, to the end of the older copied data. Dont know whether its relevent but to make sure that lines that are already on sheet 6 aren't duplicated, I have a lookup function in col 23 of sheet 1 that brings back the unique identifier if it is already on sheet 6. I know this is very convaluted! Sub UnmatchedPOs() Set rng = Sheets(1).Range("A:V").Rows Set rngTo = Sheets(6).Range("A1").End(xlDown) For Each r In rng If r.Cells(3).Value = "NO" Then If r.Cells(9).Value = DateValue("April, 01, 2007") Then If r.Cells(9).Value < DateValue("April 01, 2008") Then If Not r.Cells(6).Value = r.Cells(23).Value Then i = i + 1 r.Copy rngTo(i) End If End If End If End If Next End Sub When I run this I get: 1004 - Application defined or object defined error Many Thanks, Jig- Hide quoted text - - Show quoted text - Hi Joel, I still got the above error, with the text you said to put in. Many Thanks, Jig- Hide quoted text - - Show quoted text - I'm running Excel 2000 so could that be it? When I hit debug, the line that is highlighted is the one you asked me to change. It was that line before I changed it as well. Cheers,- Hide quoted text - - Show quoted text - I got an error: 1004 - Select method of range class failed for the line rngTo.select |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to next empty line
typos!
becomes if rngto.row = rows.count then msgbox "You're out of space!" else i = i + 1 r.Copy rngTo(i) end if Dave Peterson wrote: I think you're at the bottom of the worksheet. I'd add a check to help debug: i = i + 1 r.Copy rngTo(i) becomes if rngto.row = rows.count then i = i + 1 r.Copy rngTo(i) else msgbox "You're out of space! end if It could be because you don't have anything below A1 in Sheets(6)--or you have everything filled in on that column A of that sheet. wrote: Hi, Very new to this so would be grateful for any help. I'm trying to copy rows from one workbook to another when they meet a certain criteria. But I do not want to overwrite previously copied lines. I want to add new rows, that meet the criteria, to the end of the older copied data. Dont know whether its relevent but to make sure that lines that are already on sheet 6 aren't duplicated, I have a lookup function in col 23 of sheet 1 that brings back the unique identifier if it is already on sheet 6. I know this is very convaluted! Sub UnmatchedPOs() Set rng = Sheets(1).Range("A:V").Rows Set rngTo = Sheets(6).Range("A1").End(xlDown) For Each r In rng If r.Cells(3).Value = "NO" Then If r.Cells(9).Value = DateValue("April, 01, 2007") Then If r.Cells(9).Value < DateValue("April 01, 2008") Then If Not r.Cells(6).Value = r.Cells(23).Value Then i = i + 1 r.Copy rngTo(i) End If End If End If End If Next End Sub When I run this I get: 1004 - Application defined or object defined error Many Thanks, Jig -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to next empty line
I only get the error if there is no data in column A on sheet 6 the 1st time
I call the code. If I put data into column A the error stops occuring. Then if I remove the data from column A the error doesn't return. I don't recommend using indexing to reference worksheets. Use the actual name of the worksheet like Sheets("Sheet1") not Sheets(1). The index refers to the ordering the sheets appear in the workbook. You can move the order or the worksheets by sliding the tab on the vbottom of the worksheet. I think the problem may be that the sheet you are calling sheet(6) is not the 6th sheet in the workbook but some other sheet. "Jeegna" wrote: On 9 Nov, 12:51, Joel wrote: I couldn't get it to fail. I have no idea. Because this is the only polace in the code where rngTo is used it probably has something to do with this range try adding a line after this instruction to help you find the problem Set rngTo = Sheets(6).Range("A1").End(xlDown) new line for testing rngTo.select stop the code after this line is executed and see if the rngTo is selected. "Jeegna" wrote: On 9 Nov, 11:40, Joel wrote: I'm running with excel 2003 and do not get any errors either with the original code or my modification. I 've tried making some changes to the worksheets to see if I get the errror and I'm not able to duplicate this error with your code. Can you tell me which line is failing? "Jeegna" wrote: On 8 Nov, 18:44, Joel wrote: change the following from: r.Copy rngTo(i) to: r.Copy destination:=rngTo(i) Or if it is only one cell rngTo(i).value = r.value " wrote: Hi, Very new to this so would be grateful for any help. I'm trying to copy rows from one workbook to another when they meet a certain criteria. But I do not want to overwrite previously copied lines. I want to add new rows, that meet the criteria, to the end of the older copied data. Dont know whether its relevent but to make sure that lines that are already on sheet 6 aren't duplicated, I have a lookup function in col 23 of sheet 1 that brings back the unique identifier if it is already on sheet 6. I know this is very convaluted! Sub UnmatchedPOs() Set rng = Sheets(1).Range("A:V").Rows Set rngTo = Sheets(6).Range("A1").End(xlDown) For Each r In rng If r.Cells(3).Value = "NO" Then If r.Cells(9).Value = DateValue("April, 01, 2007") Then If r.Cells(9).Value < DateValue("April 01, 2008") Then If Not r.Cells(6).Value = r.Cells(23).Value Then i = i + 1 r.Copy rngTo(i) End If End If End If End If Next End Sub When I run this I get: 1004 - Application defined or object defined error Many Thanks, Jig- Hide quoted text - - Show quoted text - Hi Joel, I still got the above error, with the text you said to put in. Many Thanks, Jig- Hide quoted text - - Show quoted text - I'm running Excel 2000 so could that be it? When I hit debug, the line that is highlighted is the one you asked me to change. It was that line before I changed it as well. Cheers,- Hide quoted text - - Show quoted text - I got an error: 1004 - Select method of range class failed for the line rngTo.select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Cell From Subtotal Line To Detail Line | Excel Discussion (Misc queries) | |||
Hide the line when cell B is empty | Excel Discussion (Misc queries) | |||
Clipboard empty but get cannot empty CB when trying to copy | Excel Worksheet Functions | |||
Copy and Paste in the first empty available line. | Excel Programming | |||
how to do sum when meet an empty line in vba | Excel Programming |