Runtime Error 13 Type mismatch- What am I dong wrong?

[ghostbroker2]

here is the dropbox link for the 2 spreadsheets:
Dropbox - Rewinder Tracking

Debug highlights Line 17; .Value = .Value + Range("F5").Value
Here is the VBA:

Sub macSubmitProduction()
Dim W As Workbook
Dim C As Range
Dim lngR As Long

Set W = Application.Workbooks.Open("\\RWCAD\Rewinding Schedule\PRODUCTION REPORTS\REWINDER TRACKING\Rewinder Tracking TEST.xlsx")
Set C = W.Sheets(1).Cells.Find(Range("K1").Value)

If C Is Nothing Then
MsgBox Range("K1").Value & " Was Not Found!"
Exit Sub
End If

lngR = C.Row

With W.Sheets(1).Cells(lngR, "G")
.Value = .Value + Range("F5").Value
End With
With W.Sheets(1).Cells(lngR, "E")
.Value = .Value + Range("F3").Value
End With
With W.Sheets(1).Cells(lngR, "D")
.Value = .Value + Range("T1").Value
End With

W.Close True

Call macClean

End Sub

I'm still new to VBA, so any direction is appreciated.

Thank you

 

[JLGWhiz]

Your range references in red font are defaulting to the host workbook active sheet. If that is not what you intended, then it is probably where the error is being generated due to those cells containing a different data type than the W.Sheets(1) cells.

With W.Sheets(1).Cells(lngR, "G")
 .Value = .Value + [COLOR=#B22222]Range("F5").[/COLOR]Value
 End With
 With W.Sheets(1).Cells(lngR, "E")
 .Value = .Value +[COLOR=#B22222] Range("F3").[/COLOR]Value
 End With
 With W.Sheets(1).Cells(lngR, "D")
 .Value = .Value + [COLOR=#B22222]Range("T1").[/COLOR]Va[COLOR=#B22222][/COLOR]lue
 End With

If you want them to refer to the W.Sheets(1) cells, put a period in front of them.

 

[Beyond_avarice]

When you debug, what line does the error happen on?

You don't need three With/End With's.

    With W.Sheets(1)
               .Cells(lngR, "G") .Value = .Value + Range("F5").Value
               .Cells(lngR, "E") .Value = .Value + Range("F3").Value
               .Cells(lngR, "D").Value = .Value + Range("T1").Value

    End With

 

[mart37]

It is risky to refer to the wrong workbook without the workbookname.
Maybe better to define searchrange.

Sub macSubmitProduction()
    Dim W, wbSource As Workbook
    Dim C As Range
    Dim lngR As Long
    
    Set wbSource = ActiveWorkbook
    Set W = Application.Workbooks.Open("D:\users\martin\Downloads\Rewinder Tracking TEST.xlsx")
    Set C = W.Sheets(1).[COLOR=#ff0000]Range("B1:B13")[/COLOR].Find(wbSource.Sheets(1).Range("K1").Value)
    
    If C Is Nothing Then
        MsgBox wbSource.Sheets(1).Range("K1").Value & " Was Not Found!"
        Exit Sub
    End If
    
    lngR = C.Row
    
    With W.Sheets(1).Cells(lngR, "G")
        .Value = .Value + [COLOR=#ff0000]wbSource.Sheets(1)[/COLOR].Range("F5").Value
    End With
    With W.Sheets(1).Cells(lngR, "E")
        .Value = .Value + wbSource.Sheets(1).Range("F3").Value
    End With
    With W.Sheets(1).Cells(lngR, "D")
        .Value = .Value + wbSource.Sheets(1).Range("T1").Value
    End With
    
    W.Close True
    
    Call macClean
    
End Sub

 

[ghostbroker2]

So far this is performing as advertised.
Thank you!