Run time Error 1004 in my Do While loop

**Kicassrt** · 02-18-2013, 07:08 PM

I am getting this to work but it will create the worksheet then give me the Error 1004. trying to figure out what I have wrong in this. I am going to want to run this and place a few other name sets that will create a new sheet if found

Sub Do_While_loop()
Dim Fail As String
Fail = "Failure"
r = 1
Do Until Cells(r, 1) = ""
Do Until r = Fail
r = r + 1
If Cells(r, 1) = Fail Then
Sheets.Add.Name = "Failure"
Sheets("Sheet1").Activate
Exit Do
End If
Loop
Loop

End Sub

**HSV** · 02-18-2013, 07:51 PM

You can't add a sheet with the same name twice.

Maybe is this a solution.

Please Login or Register  to view this content.

Ps. Create from cell A1 a cell that you will never use, f.e. XFD1

**abousetta** · 02-18-2013, 08:06 PM

How can r = Fail?

r should be a number (long preferably to allow for Excel 2010's larger sheet sizes) so I'm missing something.

abousetta

**HSV** · 02-18-2013, 08:14 PM

Nothing is missing, there is too much @abousetta

Please Login or Register  to view this content.

**abousetta** · 02-18-2013, 08:57 PM

Hi,

I should have noted that I was referring to the OP's original post. I thought there was something I was not understanding in the logic.

abousetta

**Kicassrt** · 02-18-2013, 09:27 PM

Thanks.

I guess what I am trying to do is I need a do until loop. Trying to run through column A where there will be multiple data strings of different variety but will have duplicates etc. I want to find the instance of one, stop the do loop and create a new sheet. I need it to return to the loop to check for the other types I specify.

They are all string type elements. I am doing this on the fly so any help and guidance on building this is helpful!