Subscript out of range error only on certain computers

[msmarchingtenor]

Hello,

I am running across an issue where I receive the Subscript out of Range Error 9 when trying to execute a macro. Using a identical file, it only happens on half of my coworker's computers, but works just fine on others. Have any of you come across this type of scenario? Could this be an issue of our settings?

Not sure if it is helpful, but here is the section of code the debugger shows (it highlights the second to last line):

'Dim i As Integer

' Take away the .xls from the name


thiflnm = ActiveWorkbook.Name
j = 1
Do Until Mid(thiflnm, j + 1, 1) = "."
j = j + 1
Loop
thiflnm = Left(thiflnm, j)
Workbooks(thiflnm).Sheets("Row Numbers").Columns("A:A").ClearContents
Workbooks(thiflnm).Sheets("Sample").Cells.ClearContents

Thanks for the help!

 

[RickXL]

Hi,

You don't actually need the name of the workbook to make your code work. This should work as well:

ActiveWorkbook.Sheets("Row Numbers").Columns("A:A").ClearContents
ActiveWorkbook.Sheets("Sample").Cells.ClearContents

If that fails to work then do you have a sheet named: "Row Numbers"?

 

[msmarchingtenor]

I do have a sheet named Row Numbers. That is the reason I am so stumped on this one. I am able to run the exact same file on other computers, so I don't think it has anything to do with the code. I'm guess it's an Excel setting? But I am not familiar with settings.

 

[RickXL]

Did you try using ActiveWorkbook instead?

I don't think you need to remove the extension.

In the code below, the first print statement works but the second fails with a sunscript out of range error:

Sub x()
    thiflnm = ActiveWorkbook.Name
    Debug.Print Workbooks(thiflnm).Worksheets("Sheet1").Range("A1").Value
    
    j = 1
    Do Until Mid(thiflnm, j + 1, 1) = "."
        j = j + 1
    Loop
    thiflnm = Left(thiflnm, j)
    Debug.Print Workbooks(thiflnm).Worksheets("Sheet1").Range("A1").Value

End Sub

 

[Blade Hunter]

thiflnm = Replace(ActiveWorkbook.Name, ".xls", "")
Workbooks(thiflnm).Sheets("Row Numbers").Columns("A:A").ClearContents
Workbooks(thiflnm).Sheets("Sample").Cells.ClearContents

Some machines will show the .xls some will not, out of range is because you are looking for a . when it doesn't exist.