Loop problem

vip

New Member
Joined
Aug 3, 2007
Messages
7
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.
The latest version is shown below.

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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
For counter = 1 to N ... Next Counter

In VBA, you need to say: Next counter

Not just: Next

(since counter is your loop variable).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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! :oops:

Hopefully your problem is solved by the other suggestion.

Good day to you!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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