# Loop problem

#### vip

##### New Member
I have a table with approx 6,000 rows.
In column B, there are some duplicate ID #s that I need to merge the multiple rows into one in order to get a vlookup formula to work.
Most ID #s appear on only one row, some ID #s have 2 rows, some appear on 3 rows and an ID appears on 5 rows.

I have a formula in column A that identifies duplicate ID #s that is
=IF(AND(B20=B19,A19=""),1,IF(AND(B20=B19,A19=1),2,IF(AND(B20=B19,A19=2),3,IF(AND(B20=B19,A19=3),4,IF(AND(B20=B19,A19=4),5,"")))))

I'm trying to loop thru the 6,000 rows to determine if a 1 is located, and if the next cell below blank, if so, then copy a formula (concatenates & totals data for the 2 rows above) from row 3 and insert it below the row with the 1. If a 1 is located but there is a 2 below and it is blank below the 2, then copy a formula from row 4 insert it below the row with the 2.

I've tried several formulas.

When I run it, it get a compile error: Next without For.

I'd like to solve this because as the year progresses, the number of transactions increases, and I don't want to have to manually copy and insert the formulas - it is time-consuming.

I'd appreciate some insight from one of you masters out there.
Thanks.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 08/03/2007 by
'

'
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
'Set the range to evaluate to rng.
Set rng = Range("a10:a6000")

'initialize i to 1
I = 1

'Loop for a count of 1 to the number of rows
'in the range that you want to evaluate.
For counter = 1 To rng.Rows.Count
If rng.Cells(I) = 1 And rng.Cells(I + 1) = "" Then
Rows("3:3").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=7
Rows(I + 1).Select
Selection.Insert Shift:=xlDown
End If
If rng.Cells(I) = 1 And rng.Cells(I + 1) = 2 And rng.Cells(I + 2) = "" Then
Rows("4:4").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=7
Rows(I + 2).Select
Selection.Insert Shift:=xlDown
End If
If rng.Cells(I) = 1 And rng.Cells(I + 1) = 2 And rng.Cells(I + 2) = 3 And rng.Cells(I + 3) = "" Then
Rows("5:5").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=7
Rows(I + 3).Select
Selection.Insert Shift:=xlDown
End If
If rng.Cells(I) = 1 And rng.Cells(I + 1) = 2 And rng.Cells(I + 2) = 3 And rng.Cells(I + 4) = 5 And rng.Cells(I + 5) = "" Then
Rows("8:8").Select
Selection.Copy
ActiveWindow.LargeScroll Down:=7
Rows(I + 3).Select
Selection.Insert Shift:=xlDown
Else: I = I + 1
Next
End If

End Sub

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### mlx

##### New Member
For counter = 1 to N ... Next Counter

In VBA, you need to say: Next counter

Not just: Next

(since counter is your loop variable).

#### Boller

##### Banned
At the end of the macro, the last 3 lines should read :-

End If
I = I + 1
Next

In VBA, you need to say: Next counter

It is not necessary to have : Next counter.

Using just Next does the same thing

#### vip

##### New Member
Melissa,
Thnks for the tip. I do have other macros that just have Next, like Boller mentioned.

Boller,
Thanks for the tip. I'll try the change you suggested and let you know the results.

Y'all take care

#### mlx

##### New Member
Well sorry it was a red herring. I tried it after Boller posted and yes, Boller is right. Next time I'll test before posting!

Hopefully your problem is solved by the other suggestion.

Good day to you!

#### Scott Huish

##### MrExcel MVP
All the scroll code is not necessary, nor are all the selects. You can work with ranges directly in VBA.

I'm still not sure what you are trying to do exactly, although that formula looks like it could be replaced with a COUNTIF.

Perhaps if you provided a sample of your data and expected results. If you look at the main Excel questions page, look for the link Sticky: How to display your sheet on the board/HTML Maker FAQ. It is currently the 4th item down, it gives the download link for the HTML Maker and how to use it.

Replies
2
Views
226
Replies
2
Views
338
Replies
3
Views
465
Replies
3
Views
209
Replies
2
Views
367

1,191,214
Messages
5,985,312
Members
439,957
Latest member
venky2002

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back