Looping

[dc2288]

Hi again,

I am trying to create a loop which stops once a cell is empty.
Assuming I have declared proper DIM, my code is

Row=1

Do
Row = Row + 1
SheetName = Cells(Row, "A")
Sheets.Add.Name = SheetName
Loop Until IsEmpty(SheetName.Offset(1, 0))

Thanks!!

 

[ajetrumpet]

if i understand that code correctly, this would work a lot better (or maybe just different, but you wouldn't have to worry about stopping when a cell is empty):

dim r as range, sheetname as string

  for each r in range("a1" range(''a1'').end(xldown))
    sheetname = r
      sheets.add.name = sheetname
  next r

trying to add a sheet for every value in column A?

 

[dc2288]

Yeah.

That's exactly what I am trying to do.

I managed to complete the loop, but if someone enters same name twice, macro errors out. Is there some way to prevent people from entering same name twice?

Thanks

 

[ajetrumpet]

no there's not, but maybe so, if you explain more about what you are doing within the interface. but, to cover the duplicate entries, you could throw the values you're using in Column A into an array and search them everytime. But, better yet, before you run your macro, you could simply use a vlookup on your column A to test the values and see if they are duplicated. I used to do this all the time. Use a blank column, and put a '2' in the VLOOKUP() function if it appears twice in the column 'A', and a '1' if it's unique. that will work fine.

 

[dc2288]

Thanks man.

worked like a charm~

 

[ajetrumpet]

no problem. good luck with it.

 

[dc2288]

Now onto the next step,

I have following table in workbook called "master"

A1 A2
Apple Folder A
Orange Folder A
Pear Folder B
Banana Folder C
... ...
Mango Folder A


Folders A through C (or more) are located in C:\workspace

I need to
1) open C:\Workspace\FolderA\Apple\Apple.xls
2) select the tab named Apple
3) Select All in tab Apple
4) Select Workbook "master"
5) Select tab Apple within master
6) Paste all as formula
7) Close Apple.xls, and say no to save

Rinse and repeat for all names in column A till it's empty.

We already created appropriately named worksheets.
Also note that C:\Workspace\FolderA\Apple\Apple.xls is using names in A1.
so for Orange, it would be C:\Workspace\FolderA\Orange\Orange.xls and it will have a tab called Orange.

Thanks a lot in advance man.

 

[ajetrumpet]

if you need additional assistance with your project DC, i will be more than happy to evaluate it on an individual basis. I hope that is OK.

If you're interested, you may contact me about it.

 

[Colin Legg]

Yeah.

That's exactly what I am trying to do.

I managed to complete the loop, but if someone enters same name twice, macro errors out. Is there some way to prevent people from entering same name twice?

Thanks

You can use data validation to prevent the same name being entered twice. For example, select column A, then go to data validation, custom and put in this formula:

=COUNTIF(A:A,A1)=1

Also, going back to the original code you posted, you shouldn't give a variable a name "Row" because that is already a range object property: there is potential for confusion.

Hope that helps..

 

[dc2288]

Thank you Colin!