Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Find, Copy offset to offset on other sheet, Run-time 1004.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default Find, Copy offset to offset on other sheet, Run-time 1004.
I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
..Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Find, Copy offset to offset on other sheet, Run-time 1004.
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default Find, Copy offset to offset on other sheet, Run-time 1004.
That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop




Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default Find, Copy offset to offset on other sheet, Run-time 1004.
I tried it from a general module in the sales sheet. Still err's on
selecting from forecast sheet.
I tried it from a module in PersonalWorkbook. Same error, same Select.
:(

Finny wrote:
That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop




Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Find, Copy offset to offset on other sheet, Run-time 1004.
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

.....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default Find, Copy offset to offset on other sheet, Run-time 1004.
Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default Find, Copy offset to offset on other sheet, Run-time 1004.
It seems the difference is that you assigned the copyarray and
pastecell to range variables and copy/pasted the range vars.
While I was trying to copy directly from the array to the other sheet.

You said:
".Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
says to look at cells(rngfcitem.row+1,26) and grab the value that's in
that cell. Then use that as an address (or Name) in the .Range()
portion.
I bet you really just want to use that cell, but I've been wrong lots
of times."

I assumed that line give the destination for the paste and paste. Go
figure.
So I think you bet right, I that was the left most cell I wanted to
paste to.

"use that as an address in the .Range() portion" for what?

Thanks again,
Finny

Finny wrote:
Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default Find, Copy offset to offset on other sheet, Run-time 1004.
Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default Find, Copy offset to offset on other sheet, Run-time 1004.
alright then, thanks again.

I only posted the first thank you once. but google was tripping

cheers!

Finny wrote:
Dave!
Thank you!

You've ended two days of frustration that making me doubt I knew any
VBA at all.
I think I've got a follow up question on why your solution works, I
just need to take a look at it first.
But thanks so much!


Dave Peterson wrote:
I think I'd try to stop selecting all that stuff:

Dim RngToCopy as range
dim DestCell as range

....



'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
if rngsaleitem is nothing then
'I'd check this--and then what should happen?
else
with all_sales
set rngtocopy = .range(.cells(rngSaleItem.Row, 32), _
.Cells(rngSaleItem.Row,200))
end with
with Forecast
set destcell = .Cells(rngFCItem.Row + 1, 26)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
end if

'and continue???
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value

=============
This line:

.Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial

says to look at cells(rngfcitem.row+1,26) and grab the value that's in that
cell. Then use that as an address (or Name) in the .Range() portion.

I bet you really just want to use that cell, but I've been wrong lots of times.



Finny wrote:

That did work. But I'm getting the same error intermittently.
For a while it was pasting to the same sheet so I wasn't qualifying
properly.
Now the same error is coming at the paste. I don't get it.
Sorry for all the declares, I've trying a lot of things to get rid of
this error.

Dim rngFCItem As Range
Dim rngFCItem2 As Range
Dim SalesRange As Range
Dim SourcePosition As Range
Dim InterRange As Range
Dim DestRange As Range
Dim CurrentItem As String
Dim CurrentRow As Integer
Dim FORECAST As Worksheet
Dim ALL_SALES As Worksheet

Workbooks.Open Filename:="H:\!Files\Live Data\ALL FORECASTS.xls"

Set rngFCItem = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
Set FORECAST = Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts
w Sales")
Set ALL_SALES = Workbooks("ALL SALES.xls").Worksheets("Summary")

CurrentItem = rngFCItem.Value

Do While CurrentItem < ""

'---Find item in sales
Set rngSaleItem = rngSalesColumn.Find(CurrentItem)
'COPY
ALL_SALES.Activate
ALL_SALES.Select
Range(Cells(rngSaleItem.Row, 32), Cells(rngSaleItem.Row,
200)).Copy
'PASTE
FORECAST.Activate
FORECAST.Select
1004== Workbooks("ALL FORECASTS.xls").Worksheets("Forecasts w
Sales").Range(Cells(rngFCItem.Row + 1, 26)).PasteSpecial
Paste:=xlPasteValues
'LOOP
Set rngFCItem = rngFCItem.Offset(2, 0)
CurrentItem = rngFCItem.Value
Loop

Dave Peterson wrote:
Could it be as simple as:

Set rngWriteRow = rngWriteRow.Offset(2, 0)



Finny wrote:

I'm trying to interlace rows of sales numbers from one sheet with
forecast numbers of a second.
Get 1st item from Sh1, find that item in Sh2, copy adjacent cells,
paste into Sh1 adjacent (and one row lower) to the item there.
Thought this would be simple but the offset reference to the range of
sales numbers gives me this error:

Run-time error '1004':
Application-defined or object-defined error

Set rngWriteRow = Workbooks("ALL
FORECASTS.xls").Worksheets("Forecasts w Sales").Range("A6")
Set rngSalesColumn = Workbooks("ALL
SALES.xls").Worksheets("Summary").Range("C1:C200")
CurrentItem = rngWriteRow.Value
Do While CurrentItem < ""
'---Find item in sales
Set rngReadRow = rngSalesColumn.Find(CurrentItem)
'---Copy range offset from sales item to forecast sheet
error- Range(rngReadRow.Offset(0, 29), rngReadRow.Offset(0, 200)).Copy
Destination:= _

Range(rngWriteRow.Offset(1, 31))
'---Move down one item on Forecast sheet
rngWriteRow = rngWriteRow.Offset(2, 0)
'---Grab next item and loop
CurrentItem = rngWriteRow.Value
Loop

Why would excel reject this copy? Even if I use .Select instead of the
.Copy I get the same error.
It is being executed from the Worksheet_Calculate event handler of the
ALL SALES sheet.
Anyone see what I am missing here?

--

Dave Peterson

--

Dave Peterson

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

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
Lookup and offset at the same time? TedT Excel Worksheet Functions 9 March 25th 08 04:15 AM
Copy link to offset sheet SNACK D Excel Worksheet Functions 5 December 14th 07 01:38 AM
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul RyGuy Excel Worksheet Functions 2 September 28th 07 10:54 PM
Find then offset. Pete Excel Worksheet Functions 6 September 13th 06 10:37 PM
range.offset generates a Run Time error 1004 [email protected] Excel Programming 1 August 22nd 06 05:47 PM


All times are GMT +1. The time now is 05:45 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement