|
Urgent
Hi!
Could an expert spot what is wrong with this chunk? For f = 1 To 39 If Not Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) = Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Workbooks("BCS Reports.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value Else Cells(f + 25, 130) = f End If Next f The following works! For f = 1 To 39 If Not Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) = Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Workbooks("BCS Reports.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value Else Cells(f + 25, 130) = f End If Next f The difference is Workbooks("BCS Summary Lines.xls").Worksheets(1). has been removed from the first chunk - 7th line Both workbooks are unprotected. Thank You. |
Urgent
Sorry Don.
Which line you mean. In my chunk. The first one.?? "Don Guillett" wrote: You fail to mention what but maybe. I would also use WITH instead of repeating the range name For f = 1 To 39 If Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) < Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = _ Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Cells(f + 9, 4), _ Cells(f + 9, 4).Offset(11, 100)).Value = _ Workbooks("BCSReports.xls").Worksheets(1). _ Range(Cells(f + 9, 4), Cells(f + 9, 4)).Offset(11,100).Value Else Cells(f + 25, 130) = f End If Next f -- Don Guillett Microsoft MVP Excel SalesAid Software "Varne" wrote in message ... Hi! Could an expert spot what is wrong with this chunk? For f = 1 To 39 If Not Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) = Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Workbooks("BCS Reports.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value Else Cells(f + 25, 130) = f End If Next f The following works! For f = 1 To 39 If Not Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) = Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Workbooks("BCS Reports.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value Else Cells(f + 25, 130) = f End If Next f The difference is Workbooks("BCS Summary Lines.xls").Worksheets(1). has been removed from the first chunk - 7th line Both workbooks are unprotected. Thank You. |
Urgent
Spotted it. Thanks.
For other members; I tested it with a simplified code. In the second half of the formula it is not enough to say cells(1,2) for example. The macro expects worksheets(--).worksheets(--).cells(1,2). But for the first part of the formula it accepts. The one works; Sub t() For f = 1 To 1 Workbooks("dd.xls").Worksheets(1).Range(Cells(f + 4, 1), Cells(f + 4, 1).Offset(2, 0)).Value = Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(3, 1), Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(3, 1).Offset(2, 0)).Value Next f End Sub The one that would not work; Sub t() For f = 1 To 1 Workbooks("dd.xls").Worksheets(1).Range(Cells(f + 4, 1), Cells(f + 4, 1).Offset(2, 0)).Value = Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Cells(3, 1), Cells(3, 1).Offset(2, 0)).Value Next f End Sub Thanks. M Varnendra "Don Guillett" wrote: You fail to mention what but maybe. I would also use WITH instead of repeating the range name For f = 1 To 39 If Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) < Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = _ Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Cells(f + 9, 4), _ Cells(f + 9, 4).Offset(11, 100)).Value = _ Workbooks("BCSReports.xls").Worksheets(1). _ Range(Cells(f + 9, 4), Cells(f + 9, 4)).Offset(11,100).Value Else Cells(f + 25, 130) = f End If Next f -- Don Guillett Microsoft MVP Excel SalesAid Software "Varne" wrote in message ... Hi! Could an expert spot what is wrong with this chunk? For f = 1 To 39 If Not Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) = Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Workbooks("BCS Reports.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value Else Cells(f + 25, 130) = f End If Next f The following works! For f = 1 To 39 If Not Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) = Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Workbooks("BCS Reports.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value Else Cells(f + 25, 130) = f End If Next f The difference is Workbooks("BCS Summary Lines.xls").Worksheets(1). has been removed from the first chunk - 7th line Both workbooks are unprotected. Thank You. |
Urgent
If what you posted works, try it this way using WITH . Notice the dots . .
.. Sub t() For f = 1 To 1 with Workbooks("BCS Summary Lines.xls").Worksheets(1) Workbooks("dd.xls").Worksheets(1). _ Range(Cells(f + 4, 1), Cells(f + 4,1).Offset(2, 0)).Value = _ .Range(.Cells(3, 1), .Cells(3, 1).Offset(2, 0)).Value end with Next f End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Varne" wrote in message ... Spotted it. Thanks. For other members; I tested it with a simplified code. In the second half of the formula it is not enough to say cells(1,2) for example. The macro expects worksheets(--).worksheets(--).cells(1,2). But for the first part of the formula it accepts. The one works; Sub t() For f = 1 To 1 Workbooks("dd.xls").Worksheets(1).Range(Cells(f + 4, 1), Cells(f + 4, 1).Offset(2, 0)).Value = Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(3, 1), Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(3, 1).Offset(2, 0)).Value Next f End Sub The one that would not work; Sub t() For f = 1 To 1 Workbooks("dd.xls").Worksheets(1).Range(Cells(f + 4, 1), Cells(f + 4, 1).Offset(2, 0)).Value = Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Cells(3, 1), Cells(3, 1).Offset(2, 0)).Value Next f End Sub Thanks. M Varnendra "Don Guillett" wrote: You fail to mention what but maybe. I would also use WITH instead of repeating the range name For f = 1 To 39 If Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) < Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = _ Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Cells(f + 9, 4), _ Cells(f + 9, 4).Offset(11, 100)).Value = _ Workbooks("BCSReports.xls").Worksheets(1). _ Range(Cells(f + 9, 4), Cells(f + 9, 4)).Offset(11,100).Value Else Cells(f + 25, 130) = f End If Next f -- Don Guillett Microsoft MVP Excel SalesAid Software "Varne" wrote in message ... Hi! Could an expert spot what is wrong with this chunk? For f = 1 To 39 If Not Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) = Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Workbooks("BCS Summary Lines.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Workbooks("BCS Reports.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value Else Cells(f + 25, 130) = f End If Next f The following works! For f = 1 To 39 If Not Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 9, 105) = Workbooks("BCS Summary Lines.xls").Worksheets(1).Cells(f + 10, 105) Then Range("d10:cz477").ClearContents Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Range(Cells(f + 1009, 4), Cells(f + 1009, 4).Offset(11, 100)).Value Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value = Workbooks("BCS Reports.xls").Worksheets(1).Range(Cells(f + 9, 4), Cells(f + 9, 4).Offset(11, 100)).Value Else Cells(f + 25, 130) = f End If Next f The difference is Workbooks("BCS Summary Lines.xls").Worksheets(1). has been removed from the first chunk - 7th line Both workbooks are unprotected. Thank You. |
| All times are GMT +1. The time now is 11:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com