Create Folder Variable

[Kerry]

I would like to create a variable that I can use to replace portion of my
create folder path. I am using this code to create folders. I would like to
replace the state "Colorado" as a variable based on a column of data in my
speard sheet. I just haven't been able to figure this out. Any when would be
appriciated.

Can the folder path be a variable as well based on a column of data?


Sub StartHere()

Dim rCell As Range, rRng As Range

With Sheet1
Set rRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each rCell In rRng.Cells

If rCell.Offset(0, 4).Value = "Colorado" Then

CreateFolders rCell.Value, "C:\InvestorFiles\United States\Colorado"

End If
Next rCell

End Sub

 

[Kerry]

JLGWhiz..Is there a way to get rid of the State variable and read it directly
from a column of data. I have 50 states and I dod not what to change the
state each time?

 

[JLGWhiz]

Assume you have the 50 states listed consecutively in column "A".
The range to search is column B which is Col A.Offset(0, 1)
Sub getState()
'Establish the last row in col A with data.
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'Set the range assuming header row 1
Set sRng = ActiveSheet.Range("A2:A" & lr)
For Each c In sRng
If c.Value = c.Offset(0 , 1).Value Then
myPath = "C:\InvestorFiles\United States\"
myFolder = c.Offset(0, 1).Value
End If
End Sub

 

[JLGWhiz]

Forgot your folder.

Sub getState()
'Establish the last row in col A with data.
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'Set the range assuming header row 1
Set sRng = ActiveSheet.Range("A2:A" & lr)
For Each c In sRng
If c.Value = c.Offset(0 , 1).Value Then
myPath = "C:\InvestorFiles\United States\"
myFolder = c.Offset(0, 1).Value
MkDir myPath & myForlder
End If
End Sub

The CreateFolder sytax works with the FileSystemObject, but I don't think
you need that here. The MkDir method will create a folder for the found
state name. Be careful because it tries to create a folder each time it
finds the state name, so if you have the state in the search column more than
once, it could produce errors.

 

[Kerry]

Not working, do I need Dim and Next c?

Forgot your folder.

Sub getState()
'Establish the last row in col A with data.
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'Set the range assuming header row 1
Set sRng = ActiveSheet.Range("A2:A" & lr)
For Each c In sRng
If c.Value = c.Offset(0 , 1).Value Then
myPath = "C:\InvestorFiles\United States\"
myFolder = c.Offset(0, 1).Value
MkDir myPath & myForlder
End If
End Sub

The CreateFolder sytax works with the FileSystemObject, but I don't think
you need that here. The MkDir method will create a folder for the found
state name. Be careful because it tries to create a folder each time it
finds the state name, so if you have the state in the search column more than
once, it could produce errors.

 

[Per Jessen]

Hi

A next statement is needed before End Sub.

Dim isn't required, but putting "Option Explicit" at the top of the
module and declaring the variables will catch typo's as the one in the
statement "MkDir myPath & myForlder", should be ".....myFolder"

Regards,
Per