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
"Martin Lawrence" wrote in message
...
|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
|
| *** Sent via Developersdex http://www.developersdex.com ***
| Don't just participate in USENET...get rewarded for it!