Do Loops

Resolved · Low Priority · Version 2010

Tue 5 Feb 2013, 12:02 Delegate Emma said...

Emma has attended:
Excel VBA Intro Intermediate course

Do Loops

I have written the below Macro to add a column, name the column & then input depending on the cell content in the column previous.

When I try & run the macro it is bringing up the error message

Compile Error:
Loop without Do

I have done do until ... Loop - what have i missed?

Sub CategoryType()

Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

[h1].Select
ActiveCell.FormulaR1C1 = "Category Type"

[h2].Select

Do Until ActiveCell.Offset(0, -7) = ""

If ActiveCell.Offset(0, -1) = "MA*" Then
ActiveCell = "Materials or Stock"

If ActiveCell.Offset(0, -1) = "GE*" Then
ActiveCell = "Overheads"

Else
ActiveCell = "PNPO"

End If

ActiveCell.Offset(1, 0).Select

Loop
End Sub

Thanks

Wed 6 Feb 2013, 09:25 Trainer Gary said...

RE: Do Loops

Hi Emma

Thanks for getting in touch.

This is one of the most irritating error messages - it's complaining about the wrong thing! Look inside your DO LOOP and you'll find that you haven't closed one of your IF statements.

Add a second END IF line and it will be happy again. I'm not completely certain where you need the END IF as it depends on what your code is trying to achieve on the "MA*" and "GE*" conditions.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 7 Feb 2013, 11:53 Trainer Rodney said...

RE: Do Loops

Hello Emma,

Just to add to my colleague's reply I have attached a file containing 3 macros which need to be enabled (they are safe) which you may run on the sample data sheets.

Please examine each set of code and choose whichever you think is the best for your situation. The macro called CategoryType2 is based on the Do Loop with a few minor changes to your original code.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer