Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 in search/copy
Ooo Great ones,
I have the following: 1) Start at J3 and go down 2) find that info in different S/S 3) return different cell info to cells M3 to T3 Any suggestions? Thanks in advance!!! Ron Sub Pull_From() Dim rng As Range Dim sh As Worksheet Dim rng1 As Range Dim cell As Range With Workbooks("dallas.xls").Worksheets("sheet1") Set rng1 = .Range(.Cells(3, 11), _ .Cells(Rows.Count, 1).End(xlUp)) End With For Each cell In rng1 For Each sh In Workbooks("detailed report.xls").Worksheets Set rng = Nothing Set rng = sh.Cells.Find(What:=cell, After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then Exit For Next If Not rng Is Nothing Then cell.Offset(0, 13).Value = rng.Offset(0, -10).Value cell.Offset(0, 14).Value = rng.Offset(0, 5).Value cell.Offset(0, 15).Value = rng.Offset(0, 4).Value cell.Offset(0, 16).Value = rng.Offset(0, 1).Value cell.Offset(0, 17).Value = rng.Offset(0, 2).Value cell.Offset(0, 18).Value = rng.Offset(0, -1).Value cell.Offset(0, 19).Value = rng.Offset(0, -2).Value cell.Offset(0, 20).Value = rng.Offset(0, -11).Value End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 in search/copy
I would try changing
Set rng1 = .Range(.Cells(3, 11), _ .Cells(Rows.Count, 1).End(xlUp)) to Set rng1 = .Range(.Cells(3, 11), _ .Cells(Rows.Count, 11).End(xlUp)) and then see how it performs. -- Regards, Tom Ogilvy "ron_dallas" wrote in message oups.com... Ooo Great ones, I have the following: 1) Start at J3 and go down 2) find that info in different S/S 3) return different cell info to cells M3 to T3 Any suggestions? Thanks in advance!!! Ron Sub Pull_From() Dim rng As Range Dim sh As Worksheet Dim rng1 As Range Dim cell As Range With Workbooks("dallas.xls").Worksheets("sheet1") Set rng1 = .Range(.Cells(3, 11), _ .Cells(Rows.Count, 1).End(xlUp)) End With For Each cell In rng1 For Each sh In Workbooks("detailed report.xls").Worksheets Set rng = Nothing Set rng = sh.Cells.Find(What:=cell, After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then Exit For Next If Not rng Is Nothing Then cell.Offset(0, 13).Value = rng.Offset(0, -10).Value cell.Offset(0, 14).Value = rng.Offset(0, 5).Value cell.Offset(0, 15).Value = rng.Offset(0, 4).Value cell.Offset(0, 16).Value = rng.Offset(0, 1).Value cell.Offset(0, 17).Value = rng.Offset(0, 2).Value cell.Offset(0, 18).Value = rng.Offset(0, -1).Value cell.Offset(0, 19).Value = rng.Offset(0, -2).Value cell.Offset(0, 20).Value = rng.Offset(0, -11).Value End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 in search/copy
Thanks, That worked, don't understand why, but thats OK!!!!!!
I was wanting to remove the loop For Each sh In Workbooks("detailed report.xls").Worksheets Could you tell me what needs to be deleted. I want it to search only sheet1 Thanks, Ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 in search/copy
Sub Pull_From()
Dim rng As Range Dim sh As Worksheet Dim rng1 As Range Dim cell As Range With Workbooks("dallas.xls").Worksheets("sheet1") Set rng1 = .Range(.Cells(3, 11), _ .Cells(Rows.Count, 11).End(xlUp)) End With For Each cell In rng1 set sh = Workbooks("detailed report.xls").Worksheets("Sheet1") Set rng = Nothing Set rng = sh.Cells.Find(What:=cell, After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then cell.Offset(0, 13).Value = rng.Offset(0, -10).Value cell.Offset(0, 14).Value = rng.Offset(0, 5).Value cell.Offset(0, 15).Value = rng.Offset(0, 4).Value cell.Offset(0, 16).Value = rng.Offset(0, 1).Value cell.Offset(0, 17).Value = rng.Offset(0, 2).Value cell.Offset(0, 18).Value = rng.Offset(0, -1).Value cell.Offset(0, 19).Value = rng.Offset(0, -2).Value cell.Offset(0, 20).Value = rng.Offset(0, -11).Value End If Next End Sub -- Regards, Tom Ogilvy "ron_dallas" wrote in message oups.com... Thanks, That worked, don't understand why, but thats OK!!!!!! I was wanting to remove the loop For Each sh In Workbooks("detailed report.xls").Worksheets Could you tell me what needs to be deleted. I want it to search only sheet1 Thanks, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 1004 Copy method of worksheet class failed | Excel Discussion (Misc queries) | |||
Copy Worksheets Error #1004 Work Around | Excel Programming | |||
Run-Time Error 1004 Copy method of worksheet class failed | Excel Programming | |||
Range.Copy and error 1004 | Excel Programming | |||
error 1004 - metod Copy of class Range was failed | Excel Programming |