Not working, do I need Dim and Next c?

"JLGWhiz" wrote:

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" wrote:

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" wrote:


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
myFolder = rCell.Offset(0, 4).Value
myPath = "C:\InvestorFiles\United States\"
CreateFolders rCell.Value, myPath & myFolder

End If
Next rCell


"Kerry" wrote:

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