Invalid Next Control Variable Reference

[VBABeginner1]

I am currently getting an error when I run this on the "Next i" section. If I change it to "Next" and remove the "i", then I don't get an error, but it loops the "i", but not the "i"

I am trying to concatenate 2 cells from one sheet and move them into another on a separate sheet

Sub Concate()


Dim i As Long
Dim c As Long
Dim final As Long




Dim WS1 As Worksheet
Dim WS2 As Worksheet


Set WS1 = Worksheets("BEFORE AND AFTER")
Set WS2 = Worksheets("COMPARE")


For i = 3 To 10003
For c = 4 To 10004


WS2.Cells(i, 226) = WS1.Cells(c, 194) & " " & WS1.Cells(c, 195)




Next i
Next c
 


End Sub

 

[VBABeginner1]

I have removed out the "Dim final as Long" as well

 

[Fluff]

You've got the 2 Next lines the wrong way round. if you indent your code like this

   For i = 3 To 10003
      For c = 4 To 10004
         WS2.Cells(i, 226) = WS1.Cells(c, 194) & " " & WS1.Cells(c, 195)
      Next c
   Next i

it helps to spot mistakes of that sort

 

[Fluff]

Looking at your code more closely, I think you can get rid of 1 of the loops like

   For i = 3 To 10003
      WS2.Cells(i, 226) = WS1.Cells(i, 194) & " " & WS1.Cells(, 195)
   Next i

 

[VBABeginner1]

Next c
Next i
when I do it this way, it won't loop through the "i", just the "c"

I need the i and c references to be different as they pull data from separate sheets that are formatted differently.

 

[Fluff]

It does loop through i, but it's got to loop through c first (ie all 10,001 rows).
Also if you run with both loops, WS2 will have the same value all the way the column.

 

[VBABeginner1]

Looking at your code more closely, I think you can get rid of 1 of the loops like

   For i = 3 To 10003
      WS2.Cells(i, 226) = WS1.Cells(i, 194) & " " & WS1.Cells(, 195)
   Next i

This way did work, so I used this code and re-formatted all my other code in the sheet to allow the starting points to line up.

Thanks for your help!

 

[Fluff]

Glad to help & thanks for the feedback