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?

[cl0n3]

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

[cl0n3]

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.