Urgent

  • Thread starter Thread starter Varne
  • Start date Start date
V

Varne

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.
 
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
 
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
 
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
 
Back
Top