Worksheet subscript out of range

user_12843740 0
Viewed 47

I wrote VBA code that opens Workbooks located on the Server to copy data.

I have one PC where it says "subscript out of range".
It opens the workbook but can't find the sheet.
Network location is mapped on both PC under the same name.

For looks for the Column with Date -1 and set i for coordinates.
Later While is checking if the Cell is without Fill as coloured cell is irrelevant. If so it moves on cell left as long as it finds Cell that is not filled.

Sub importData()
    Dim sht As String
    Dim i As Integer
    sht = Cstr(Date - 1)
    Dim Temp(1 To 38) As String
    
    For i = 4 To 42
        If Cells(3, i).Value = sht Then
            Exit For
        End If
    Next i
    
    While Cells(2, i).Interior.ColorIndex <> xlColorIndexNone
        i = i - 1
        sht = Cells(3, i).Value
    Wend
    
    Workbooks.Open "path to file & filename.xlsm"
    Workbooks("filename").Worksheets(sht)
worksheet vba excel
1 Answers

The problem lies in sht = Cstr(Date - 1).

Cstr will convert a date into a string according the date format that is set in the operating system. In my case that is yyyy-mm-dd so for today I will get as result 2022-06-01 and that will be used as sheet name.

Now if your computer date is set to mm/dd/yyyy for example your computer generates 06/01/2022 as sheet name. So that's no reliable way to generate your string.

Instead use Format function and define which format your string should have:

sht = Format$(Date - 1, "yyyy-mm-dd")

Now this will always generate the same string no matter which date format your computer uses.

Not that since you use the text in Cells(3, i).Value = sht too make sure if you use numeric dates in your cells that you do not use a String to compare it but a numeric date too:

Cells(3, i).Value = Date - 1

otherwise you might fail here too if the computer date is a different format!

And same for sht = Cells(3, i).Value if the cell value is numeric date and not a text/string you need to use

sht = Format$(Cells(3, i).Value, "yyyy-mm-dd")

and define the format.

user_32196130
Related