Excel macro help

[Martin Lawrence]

I have an excel macro with the following code:

Sub ITEM_COUNT()
Range("c10").Select
Dim fso
Dim fol
Dim subfol
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.getfolder("c:\" & Range("c10").Text)
Set subfol = fol.subfolders
sc = subfol.Count
With ActiveSheet
Range("c10").Offset(0, 3) = sc
End With

End Sub

This code takes what is written in cell C10(base folder name) and counts
the number of subfolders in it and places the count in cell F10. What I
would like to do now is do the same thing on the next rows(ie. C11, C12,
C13, etc...)and place the subfolder count in its respective cell in
column F.

Thanks, Martin

 

[Jim Rech]

Sub ITEM_COUNT()
Dim fso, fol
Dim Cell As Range
Set fso = CreateObject("Scripting.FileSystemObject")
For Each Cell In Range("C10:C20") ''Adjust as needed
Set fol = fso.getfolder("c:\" & Cell.Text)
Cell.Offset(0, 3) = fol.subfolders.Count
Next
End Sub


--
Jim Rech
Excel MVP
|I have an excel macro with the following code:
|
| Sub ITEM_COUNT()
| Range("c10").Select
| Dim fso
| Dim fol
| Dim subfol
| Set fso = CreateObject("Scripting.FileSystemObject")
| Set fol = fso.getfolder("c:\" & Range("c10").Text)
| Set subfol = fol.subfolders
| sc = subfol.Count
| With ActiveSheet
| Range("c10").Offset(0, 3) = sc
| End With
|
| End Sub
|
| This code takes what is written in cell C10(base folder name) and counts
| the number of subfolders in it and places the count in cell F10. What I
| would like to do now is do the same thing on the next rows(ie. C11, C12,
| C13, etc...)and place the subfolder count in its respective cell in
| column F.
|
| Thanks, Martin
|
|
| Don't just participate in USENET...get rewarded for it!

 

[Martin Lawrence]

Thanks for your reply, but now I have 2 more issues. First I need to
check to see if the folder actually exists Second, I would like the
macro to stop running when the first empty cell is reached in column C.

Martin