Excel VBA Loop Question

[vunsev]

This is my current code, how can I change the range to E9,E10 and onward to continue the code until the range cell is empty.

Dim Cellname As String
Dim rng As Range

Set rng = Sheets("CPD").Range("E8")
Cellname = rng.Text

MkDir "C:\Documents and Settings\administrator.BILLESLEY1\Desktop\CPD\" & Cellname

Any help would be greatful.

[penfold_99]

Hi

you need to create a loop either a while or for
it will increment a value eg counter

then reference the field by

Set rng = Sheets("CPD").Range("E" & counter)

as part of the loop or as an if statement inside the loop, check if the cell conatins anything if not exit the loop

[vunsev]

Sorry, I am very new to programming how would I do the things you have said.

Can you rewrite the code for me please?

[Shane]

go = 1
counter = 1

While go = 1
Sheets("CPD").Range("E" & counter).Select
contents = ActiveCell.Value
If contents = "" Then go = 0
counter = counter + 1

MkDir "C:\Documents and Settings\administrator.BILLESLEY1\Desktop\CPD\" & contents

Wend

If this isn't what you want the script to do, can you give a brief explanation of the outcome you expect?
Cheers

[Shane]

sw = 1

While Range("e" & sw) <> ""
Range("e" & sw).Select
person = ActiveCell.Value
Range("d" & sw).Select
course = ActiveCell.Value

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists("c:\" & person) = False Then fs.CreateFolder ("c:\" & person)
fs.createtextfile ("c:\" & person & "\" & course & ".txt")

sw = sw + 1
Wend

Did this do the trick?
Cheers

[vunsev]

Yes that worked, thanks very much.