Type Mismatch error: Don't Understand Why

I am trying to add one value to another, but it always stops somewhere in my code and throws:

Error Type 13: Mismatch Error

even when removing one part of the code another error appears with the same error. For example:

When this:

ThisWorkbook.Worksheets(2).Cells(i, 9) = ThisWorkbook.Worksheets(2).Cells(i, 9) + Sheets(j).Cells(i, 42)

The first error, appeared, I wanted to troubleshoot it by erasing it and the same error would appear on this:

ThisWorkbook.Worksheets(2).Cells(i, 6) = ThisWorkbook.Worksheets(2).Cells(i, 6) + Sheets(j).Cells(i, 41)

The Whole Code:

Do While (j < (ThisWorkbook.Worksheets(2).Cells(1, 9)) + 3)


For i = 7 To 65

'Megaform

ThisWorkbook.Worksheets(2).Cells(i, 6) = ThisWorkbook.Worksheets(2).Cells(i, 6) + Sheets(j).Cells(i, 41)
ThisWorkbook.Worksheets(2).Cells(i, 7) = ThisWorkbook.Worksheets(2).Cells(i, 7) + Sheets(j).Cells(i, 44)
ThisWorkbook.Worksheets(2).Cells(i, 8) = ThisWorkbook.Worksheets(2).Cells(i, 8) + Sheets(j).Cells(i, 47)
ThisWorkbook.Worksheets(2).Cells(i, 9) = ThisWorkbook.Worksheets(2).Cells(i, 9) + Sheets(j).Cells(i, 42)
ThisWorkbook.Worksheets(2).Cells(i, 10) = ThisWorkbook.Worksheets(2).Cells(i, 10) + Sheets(j).Cells(i, 45)
ThisWorkbook.Worksheets(2).Cells(i, 11) = ThisWorkbook.Worksheets(2).Cells(i, 11) + Sheets(j).Cells(i, 48)

Next i

Solution

As was mentioned in the comments on your OP, you've got text or an error in the cells you're trying to add. You may have a number stored as text so it looks like a number when you see it in the cell, but Excel (& VBA) will treat it as text. If that's the case CInt(ThisWorkbook...) around each cell reference will convert it. But it won't convert Some text or #ERROR to an integer for you. You may want to create a bit of validation code around each line, something like this:

Sub temp()
  Const NAN   As String = "Non-number in source data"

  With ThisWorkbook.Worksheets(2)
    Dim J     As Long
    Do While (J < (.Cells(1, 9)) + 3)
      Dim OtherWorksheet As Worksheet
      Set OtherWorksheet = ThisWorkbook.Worksheets(J)

      Dim i   As Long
      For i = 7 To 65
        'Megaform
        If IsNumeric(.Cells(i, 6)) And IsNumeric(Sheets(J).Cells(i, 41)) Then
          .Cells(i, 6) = .Cells(i, 6) + Sheets(J).Cells(i, 41)
        Else
          .Cells(i, 6) = NAN
        End If
        If IsNumeric(.Cells(i, 7)) And IsNumeric(OtherWorksheet.Cells(i, 44)) Then
          .Cells(i, 7) = .Cells(i, 7) + OtherWorksheet.Cells(i, 44)
        Else
          .Cells(i, 7) = NAN
        End If

        'etc...

      Next
      'I hope there's some incrementer for J down here!!
    Loop
  End With

End Sub

A couple of notes on the code:

I declared NAN as a constant string to insert when there is invalid input data
I added a With block so I didn't have to type ThisWorkbook.Worksheets(2) dozens of times
- This means that every range reference that starts with . refers back to this to pick up the full reference
Danger #1 the unqualified Sheets(3) reference in the FIRST If statement refers to the currently active workbook. Your 'Megaform comment leads me to believe that this could be a long running process. That could lead to your user clicking on another workbook. If that happens, Sheets() now refers to the workbook he just selected, not the one you were working with.
- I resolved that in the SECOND If statement by setting OtherWorksheet to be sheet #J in ThisWorkbook immediately after the start of the Do While loop. ThisWorkbook always refers to the workbook the code is running in, so even if the user switches books on you, you can still get back to where you were. The 2nd If statement then uses a fully qualified OtherWorksheet. notation to ensure it's reading from the correct worksheet.
Danger #2 the Sheets() collection includes all sheet types in a workbook. This includes Chart sheets. Chart sheets won't work they way you're expecting them to here, so if someone happens to insert a Chart sheet in your workbook, your code will blow up (run-time error) when J points to it. By using the Worksheets() collection, you avoid that potential issue.
I added some proper indentation to the code since that makes the code structure much easier to identify with a quick scan. I used Rubberduck^* to do that for me. It also has a code inspection feature that will provide many suggestions on other improvements you can make to your code.
You don't show your declaration for Jor any initialization or incrementer for it, so I hope those are there. It may make for a more simple loop to use
For J = StartValue to .Cells(1,9) + 3
I tend to prefer a For loop when the end points are definable, but there's no requirement to do so.
I didn't write the If protection around all your addition statements. I figured this was a good example and you can copy/paste the rest.

^*Rubberduck is an open source project (available on GitHub) working hard to bring the VBE into the 21st century. My involvement is as a satisfied user and semi-pro ~~complainer~~ tester.

To address the Subscript out of range error:

You'll note that in the bullet points I did point out that I saw nothing in your original code that set J to begin with. If there was nothing, it would start at 0 which is out of range for the WorkSheet collection (it's 1-based). I also called out a suggestion to insure that there was something in there to increment it.

My presumption was that Thisworkbook.Worksheets(2).Cells(1,9) contained the number (minus 3, for some reason) of worksheets you want to process. It sounds now like you're trying to use it to keep track of the number of WorkSheets in your WorkBook which is unnecessary and fraught with error.

If you're trying to process all the WorkSheets in the WorkBook (except for Worksheets(2) which is where the data will end up, I presume), replace:

Dim J as Long
Do While (J < (.Cells(1, 9)) + 3)
  Dim OtherWorksheet As Worksheet
  Set OtherWorksheet = ThisWorkbook.Worksheets(J)
  .
  .
  .
Loop

With this code instead:

Dim CurrentSheet as WorkSheet
For Each CurrentSheet in ThisWorkbook
  If CurrentSheet.Name <> ThisWorkbook.Worksheets(2).Name Then
    .
    'the existing code here.
    'NOTE: references to OtherWorksheet need to be changed to CurrentSheet
    '      Or, change "CurrentSheet" to "OtherWorksheet" in three places here, whichever is easier
    .
Next

This will process through all the WorkSheets in your WorkBook, skipping WorkSheet(2) which it identifies by name. If there are other worksheets that you need to skip, you can add them to the If statement with an And in between (i.e. don't process if .Name = "Fred" And .Name = "Barney")