Excel VBA Loop Question
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.
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
Sorry, I am very new to programming how would I do the things you have said.
Can you rewrite the code for me please?
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
If this isn't what you want the script to do, can you give a brief explanation of the outcome you expect?
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
Did this do the trick?
Yes that worked, thanks very much.