Invalid Next Control Variable Reference

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
> > >
> > >