Multiple OFFSET functions in a single line in Excel VBA don't work - Why?

Question

Simply put, I'm trying to get what seems like a fairly simple macro to work in Excel. It needs to run equally well on Macs and PCs, but I need to loop through all the rows in Column A and then create a file folder for each row in the format:

I know there's more I can do (checking to see if the folder already exists, etc), but for the moment I just want to get the mkdir line functioning. Can anyone help with this? Thank you.

Text of Column A Text of Column B - Text of Column C

Sub CreateDirs()
    Dim R As Range
    Dim RootFolder As String
    RootFolder = ThisWorkbook.path
    For Each R In Range("A7:A64000")
        If Len(R.Text) > 0 Then
            On Error Resume Next
            MkDir RootFolder & Application.PathSeparator & R.Text & " " & R.Offset(0, 1).Text & " - " & R.Offset(0, 2).Text
            On Error GoTo 0
        End If
    Next R
End Sub

Answer 1

Your code executes fine under Windows when tested here. However, since you also need to execute on a Mac, you might want to step away from MkDir. You also want to check whether the folder exists or not.

The complete answer to your problem is readily available on Ron de Bruin his website here: http://www.rondebruin.nl/mac/mac010.htm

His code as published on the above link:

Sub MakeFolderTest1()
'Make folder on the Desktop
    MakeFolderIfNotExist (MacScript("return (path to desktop folder) as string") & "TestFolder1")
End Sub

Sub MakeFolderTest2()
'Add folder in the same path as your workbook with this code
    MakeFolderIfNotExist (ThisWorkbook.Path & Application.PathSeparator & "TestFolder2")
End Sub


'Change the path of the two macro below before you test them

Sub MakeFolderTest3()
'Enter the complete path
    MakeFolderIfNotExist ("YosemiteLacie256:Users:rondebruin:Desktop:TestFolder3")
End Sub

Sub MakeFolderTest4()
'Do not include the harddisk name if you use a posix path
    MakeFolderIfNotExist ("/Users/rondebruin/Desktop/TestFolder4")
End Sub


Function MakeFolderIfNotExist(Folderstring As String)
'Ron de Bruin, 22-June-2015
' http://www.rondebruin.nl/mac/mac010.htm
    Dim ScriptToMakeFolder As String
    Dim str As String
    If Val(Application.Version) < 15 Then
        ScriptToMakeFolder = "tell application " & Chr(34) & _
                             "Finder" & Chr(34) & Chr(13)
        ScriptToMakeFolder = ScriptToMakeFolder & _
                "do shell script ""mkdir -p "" & quoted form of posix path of (" & _
                        Chr(34) & Folderstring & Chr(34) & ")" & Chr(13)
        ScriptToMakeFolder = ScriptToMakeFolder & "end tell"
        On Error Resume Next
        MacScript (ScriptToMakeFolder)
        On Error GoTo 0

    Else
        str = MacScript("return POSIX path of (" & _
                        Chr(34) & Folderstring & Chr(34) & ")")
        MkDir str
    End If
End Function

His website is a great resource for Excel VBA development in general.