Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macro help
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macro help
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macro help
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet | Excel Programming |