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

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

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

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


"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

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

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

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

On 13 Sep., 22:44, Kerry wrote:
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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -