subscript out of range error that doesn't make sense

[Foo_Man_Chu]

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 :

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:

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
.......

 

[ping_pong]

Use .Range("F29") (with quotes).

 

[Foo_Man_Chu]

Thanks for the reply but unfortunately that doesn't work. Any other suggestions? Anyone?

 

[Caleeco]

I'm not dived into debuging the code, but as a quick check, have you tried:

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

Caleeco

 

[Foo_Man_Chu]

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

 

[JoeMo]

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]

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

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

 

[JoeMo]

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

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]

Then use this and find which line errors

With Workbooks(file)
    With .Worksheets("Esc_Gum_LD")
        With .Range("F29")
            .Value = "c"
        End With
    End With
End With