subscript out of range error that doesn't make sense

Foo_Man_Chu

Board Regular
Joined
Jul 22, 2010
Messages
79
I have a spreadsheet where the following code runs after a button is clicked. The code is supposed to ultimately update another spreadsheet from the current spreadsheet. When it runs it gives me a subscript out of range error on this line :

Code:
Workbooks(file).Worksheets("Esc-Gum-LD").Range(F29) = "c"

I've looked around and from what I've seen this is either due to trying to reference an element outside the bounds of an array or trying to refer to something that isn't in the spreadsheet. There is no array involved and the only two values are "Esc-Gum-LD" and the variable "file". I checked and the workbook "file" represents exists and is open and it contains a sheet named "Esc-Gum-LD". Please, can someone help me?

Here is my code:



Code:
Private Sub updateTargetSpreadsheet(Optional ByVal strAddr As String, Optional ByVal strColRow As String, Optional ByVal strColor As String, Optional ByVal strType As String)
    Dim file As String
    Dim strLDAddr As String
    Dim strESCAddr As String
    Dim blnLoadDock As Boolean
    blnLoadDock = False
        
    file = Format(Date, "m.d.yy.x\l\sx")
    
    If strType = "Cell" Then
        If strAddr = "$D$3" Then
            strLDAddr = "B" & strColRow
        
        End If
    
        If strColor = "64636" Then
            If Not strLDAddr = vbNullString Then
                Workbooks(file).Worksheets("Esc-Gum-LD").Range(F29) = "c"
                Workbooks(file).Worksheets("Esc-Gum-LD").Range(strLDAddr).Offset(0, 1) = "h"
            ElseIf Not strESCAddr = vbNullString Then
                Workbooks(file).Worksheets("Esc-Gum-LD").Range(strESCAddr) = "o"
            End If
        Else
            If Not strLDAddr = vbNullString Then
                Workbooks(file).Worksheets("Esc-Gum-LD").Range(strLDAddr) = "o"
                Workbooks(file).Worksheets("Esc-Gum-LD").Range(strLDAddr).Offset(0, 1) = "h"
            ElseIf Not strESCAddr = vbNullString Then
                Workbooks(file).Worksheets("Esc-Gum-LD").Range(strESCAddr) = "x"
            End If
        End If
.......
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Foo_Man_Chu

Board Regular
Joined
Jul 22, 2010
Messages
79
Thanks for the reply but unfortunately that doesn't work. Any other suggestions? Anyone?
 

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
956
I'm not dived into debuging the code, but as a quick check, have you tried:

Code:
Workbooks(file).Worksheets("Esc-Gum-LD").Range("F29").Value ="c"

Caleeco
 

Foo_Man_Chu

Board Regular
Joined
Jul 22, 2010
Messages
79

ADVERTISEMENT

Thanks again for the quick response, but sadly this doesn't work either. Little help anyone?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,217
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks again for the quick response, but sadly this doesn't work either. Little help anyone?


The variable "file" must be the complete filename of the workbook, including the file extension.

.x\l\sx doesn't look like a valid file extension.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,782

ADVERTISEMENT

The variable F29 is out of range.
Try adding quote marks

Code:
Workbooks(file).Worksheets("Esc-Gum-LD").Range([B][COLOR="#FF0000"]"[/COLOR][/B]F29[B][COLOR="#FF0000"]"[/COLOR][/B]) = "c"
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,217
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The variable F29 is out of range.
Try adding quote marks

Code:
Workbooks(file).Worksheets("Esc-Gum-LD").Range([B][COLOR=#FF0000]"[/COLOR][/B]F29[B][COLOR=#FF0000]"[/COLOR][/B]) = "c"
Posts#2 and 4 already suggested this. OP says that doesn't fix the problem.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,782
Then use this and find which line errors

Code:
With Workbooks(file)
    With .Worksheets("Esc_Gum_LD")
        With .Range("F29")
            .Value = "c"
        End With
    End With
End With
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,721
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top