Loop Macro

Mondo001

New Member
Joined
Apr 9, 2009
Messages
9
Below is my attempt at a loop macro, it seems to be working fine and then after 2 loops it stops!
It shouldn't stop until it finds the "ProductName" cell to be blank.

Can anyone see something i am doing wrong?

I have also got it running all the way through by removing the line in red, but it only stops because of an error......
Using this method what is the best way for me to stop the macro?

---------------------------------------------------------------

Dim ProductParts
Dim ProductName
Dim ProductRange
Dim KeepSearching
ProductParts = -1
ProductName = -1
ProductRange = -1
KeepSearching = True


Do While KeepSearching


If ProductName = Empty Then
KeepSearching = False

End If
ProductParts = ProductParts + 1
ProductName = ProductName + 1
ProductRange = ProductRange + 1
If Range("A2").Offset(0, ProductName) <> Empty Then

Sheets("P1").Select
Range("A4").Select
ActiveCell.Offset(0, ProductParts).Select
Sheets("family_attach_rate").Select
ActiveSheet.Range("$A$1:$G$282").AutoFilter Field:=2, Criteria1:=Sheets("P1").Range("A2").Offset(0, ProductName), Operator:=xlOr, Criteria2:=Sheets("P1").Range("A1")
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("P1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("A4").Select
ActiveCell.Offset(0, ProductRange).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.RemoveDuplicates Columns:=1, Header:=xlYes

End If

Loop
End Sub
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Code:
If ProductName = Empty Then

What is Empty?

Do you mean

Code:
If ProductName = "" Then
 

Mondo001

New Member
Joined
Apr 9, 2009
Messages
9
Excel seems to recognise Empty, i would think as a blank cell.
I have run it with "" also and it runs the same way.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,807
Members
414,104
Latest member
imamalidadashzada

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
Top