Paste to cell instead of a msg box - VBA Edit

[cjouban]

I found this macro and it almost does what I need, except for the msg box. Can anyone help me edit the code so it paste the count result to a cell in my open workbook instead of the message box. Feels like there is something really easy that I'm missing to accomplish this.

Sub FolderColl()
    Dim oFSO As Object
    Dim folder As Object
    Dim subfolders As Object
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set folder = oFSO.GetFolder("J:\folderpath....")
    Set subfolders = folder.subfolders
    Sheet1.Range("B2").Value = subfolder.Count
    MsgBox subfolders.Count
    
    
    Set oFSO = Nothing
    Set folder = Nothing
    Set subfolders = Nothing
    'release memory


End Sub

 

[cooper645]

Sub FolderColl()
    Dim oFSO As Object
    Dim folder As Object
    Dim subfolders As Object
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set folder = oFSO.GetFolder("J:\folderpath....")
    Set subfolders = folder.subfolders
    Sheet1.Range("B2").Value = subfolder.Count
    Sheet1.Range("A1").Value = subfolders.Count
    
    
    Set oFSO = Nothing
    Set folder = Nothing
    Set subfolders = Nothing
    'release memory


End Sub

This is should put the value on sheet 1 cell A1 rather than display a message box

 

[cjouban]

Gives me a Run-time error '424': Object required

 

[cjouban]

Sub FolderColl()
    Dim oFSO As Object
    Dim folder As Object
    Dim subfolders As Object
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set folder = oFSO.GetFolder("J:\folderpath....")
    Set subfolders = folder.subfolders
    Sheet1.Range("B2").Value = subfolder.Count
    Sheet1.Range("A1").Value = subfolders.Count
    
    
    Set oFSO = Nothing
    Set folder = Nothing
    Set subfolders = Nothing
    'release memory


End Sub

This is should put the value on sheet 1 cell A1 rather than display a message box

Gives me a Run-time error '424': Object required

 

[cooper645]

Sub FolderColl()    Dim oFSO As Object
    Dim folder As Object
    Dim subfolders As Object
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set folder = oFSO.GetFolder("[COLOR=#ff0000]change me for your filepath ie: C:\users\JoeBloggs\[/COLOR]")
    Set subfolders = folder.subfolders
    Range("[COLOR=#0000ff]A1[/COLOR]").Value = subfolders.Count
    
    Set oFSO = Nothing
    Set folder = Nothing
    Set subfolders = Nothing
    'release memory




End Sub

Change the red text for your filepath your looking to count subfolders in.
Change the Blue cell reference for the cell you wish the number to go into.

This will work on whichever sheet is the active sheet at the time, so if your assigning this macro to a button on a sheet, there should be no issues.

 

[cjouban]

Perfect. Thank you. Looks like I was missing a "s" in my original code Sheet1.Range("B2").Value = subfolders.Count .


​Thanks Again

 

[cooper645]

no problem