| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to do it - please help me! Urgent. | Excel Discussion (Misc queries) | |||
| IME MODE FOR EXCEL 2007 (URGENT URGENT) | Excel Discussion (Misc queries) | |||
| Urgent-Urgent VBA LOOP | Excel Discussion (Misc queries) | |||
| Macro help urgent urgent | Excel Programming | |||
| Macro help urgent urgent | Excel Programming | |||
Hybrid Mode
