I get an 'Invalid Next Control Variable Reference', also is there
a ore efficient way of doing this rather than re-defining:
fin,fin2,vArr,vArr2 etc.etc.
Many Thanks

Public Sub coiD()
Dim fin As Workbook
Dim fin2 As Workbook
Dim vArr As Variant
Dim vArr2 As Variant
Dim rCell As Range
Dim rDest As Range
Dim sDest As Range
Dim i As Long
Dim j As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamC.xls")
Set fin2 = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamM.xls")
vArr = Array("Hudson", "HSB", "C&W")
vArr2 = Array("ACCEN", "AME", "SHEL")

For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
For j = LBound(vArr2) To UBound(vArr2)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy
Destination:=rDest

If .Value = vArr2(j) Then
Set sDest = fin2.Worksheets(vArr2(j)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=sDest

Exit For
Exit For
End If
End If
Next i
Next j
End With
Next rCell
End Sub

the "j" loop is inside the "i" loop, therefore you need to have the "next j"
statement before the "next i" statement

Also, you have two "Exit For" statements. I presume you are trying to exit
BOTH loops (i and j) at that point. It won't happen. The Exit For will only
exit the inner loop, then you will need to have another loop exit test
between the "next j" and "next i" statements.


"teresa" wrote:

I get an 'Invalid Next Control Variable Reference', also is there
a ore efficient way of doing this rather than re-defining:
fin,fin2,vArr,vArr2 etc.etc.
Many Thanks

Public Sub coiD()
Dim fin As Workbook
Dim fin2 As Workbook
Dim vArr As Variant
Dim vArr2 As Variant
Dim rCell As Range
Dim rDest As Range
Dim sDest As Range
Dim i As Long
Dim j As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamC.xls")
Set fin2 = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamM.xls")
vArr = Array("Hudson", "HSB", "C&W")
vArr2 = Array("ACCEN", "AME", "SHEL")

For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
For j = LBound(vArr2) To UBound(vArr2)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy
Destination:=rDest

If .Value = vArr2(j) Then
Set sDest = fin2.Worksheets(vArr2(j)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=sDest

Exit For
Exit For
End If
End If
Next i
Next j
End With
Next rCell
End Sub

Hi still cant get it to work quite

Public Sub coiD()
Dim fin As Workbook
Dim fin2 As Workbook
Dim vArr As Variant
Dim vArr2 As Variant
Dim rCell As Range
Dim rDest As Range
Dim sDest As Range
Dim i As Long
Dim j As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamCB.xls")
Set fin2 = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamMS.xls")
vArr = Array("Hudson", "HSBC", "C&W")
vArr2 = Array("ACCENT", "AMEX", "SHELL")

For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
For j = LBound(vArr2) To UBound(vArr2)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
'If rDest.Row < 18 Then _
' Set rDest = rDest.Offset(18 - rDest.Row, 0)
.EntireRow.Copy Destination:=rDest
'Else: If .Offset(0, 3).Value = "CC" Then
EntireRow.Copy _
'Destination:=fin.Worksheets("CASTROL").Cells(25,
1).End(xlUp).Offset(1, 0)
If .Value = vArr2(j) Then
Set sDest = fin2.Worksheets(vArr2(j)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=sDest
Exit For

End If
End If
Next j
Next i
End With
Next rCell
End Sub




"Charlie" wrote:

the "j" loop is inside the "i" loop, therefore you need to have the "next j"
statement before the "next i" statement

Also, you have two "Exit For" statements. I presume you are trying to exit
BOTH loops (i and j) at that point. It won't happen. The Exit For will only
exit the inner loop, then you will need to have another loop exit test
between the "next j" and "next i" statements.


"teresa" wrote:

I get an 'Invalid Next Control Variable Reference', also is there
a ore efficient way of doing this rather than re-defining:
fin,fin2,vArr,vArr2 etc.etc.
Many Thanks

Public Sub coiD()
Dim fin As Workbook
Dim fin2 As Workbook
Dim vArr As Variant
Dim vArr2 As Variant
Dim rCell As Range
Dim rDest As Range
Dim sDest As Range
Dim i As Long
Dim j As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamC.xls")
Set fin2 = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamM.xls")
vArr = Array("Hudson", "HSB", "C&W")
vArr2 = Array("ACCEN", "AME", "SHEL")

For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
For j = LBound(vArr2) To UBound(vArr2)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy
Destination:=rDest

If .Value = vArr2(j) Then
Set sDest = fin2.Worksheets(vArr2(j)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=sDest

Exit For
Exit For
End If
End If
Next i
Next j
End With
Next rCell
End Sub

Well, the only other possible syntax error I see is the line

EntireRow.Copy _

maybe you meant .EntireRow.Copy with the dot, and without the
line-continuation underscore. As for what it is you are trying to do, I
haven't looked at it that closely.

"teresa" wrote:

Hi still cant get it to work quite

Public Sub coiD()
Dim fin As Workbook
Dim fin2 As Workbook
Dim vArr As Variant
Dim vArr2 As Variant
Dim rCell As Range
Dim rDest As Range
Dim sDest As Range
Dim i As Long
Dim j As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamCB.xls")
Set fin2 = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamMS.xls")
vArr = Array("Hudson", "HSBC", "C&W")
vArr2 = Array("ACCENT", "AMEX", "SHELL")

For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
For j = LBound(vArr2) To UBound(vArr2)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
'If rDest.Row < 18 Then _
' Set rDest = rDest.Offset(18 - rDest.Row, 0)
.EntireRow.Copy Destination:=rDest
'Else: If .Offset(0, 3).Value = "CC" Then
EntireRow.Copy _
'Destination:=fin.Worksheets("CASTROL").Cells(25,
1).End(xlUp).Offset(1, 0)
If .Value = vArr2(j) Then
Set sDest = fin2.Worksheets(vArr2(j)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=sDest
Exit For

End If
End If
Next j
Next i
End With
Next rCell
End Sub




"Charlie" wrote:

the "j" loop is inside the "i" loop, therefore you need to have the "next j"
statement before the "next i" statement

Also, you have two "Exit For" statements. I presume you are trying to exit
BOTH loops (i and j) at that point. It won't happen. The Exit For will only
exit the inner loop, then you will need to have another loop exit test
between the "next j" and "next i" statements.


"teresa" wrote:

I get an 'Invalid Next Control Variable Reference', also is there
a ore efficient way of doing this rather than re-defining:
fin,fin2,vArr,vArr2 etc.etc.
Many Thanks

Public Sub coiD()
Dim fin As Workbook
Dim fin2 As Workbook
Dim vArr As Variant
Dim vArr2 As Variant
Dim rCell As Range
Dim rDest As Range
Dim sDest As Range
Dim i As Long
Dim j As Long

Set fin = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamC.xls")
Set fin2 = Application.Workbooks.Open( _
"C:\My Documents\Business Plans\TeamM.xls")
vArr = Array("Hudson", "HSB", "C&W")
vArr2 = Array("ACCEN", "AME", "SHEL")

For Each rCell In Range("D1:D" & _
Range("D" & Rows.Count).End(xlUp).Row)
With rCell
For i = LBound(vArr) To UBound(vArr)
For j = LBound(vArr2) To UBound(vArr2)
If .Value = vArr(i) Then
Set rDest = fin.Worksheets(vArr(i)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy
Destination:=rDest

If .Value = vArr2(j) Then
Set sDest = fin2.Worksheets(vArr2(j)).Cells( _
25, 1).End(xlUp).Offset(1, 0)
.EntireRow.Copy Destination:=sDest

Exit For
Exit For
End If
End If
Next i
Next j
End With
Next rCell
End Sub