What Am I doing wrong here?

Greetings,

On the below code, its breaking at exactly the same spot, I cant for the life of me figure out why.

I get VB 400 error or 1004 error depending on which line you remove the comment ' from

Sub WhatsWrong()
Dim i As Integer, j As Integer, k As integer
Dim eName As String, SaTotal As String

    For j = 3 to 15
       SaTotal = "=Sum"
           For i = 4 to ThisWorkBook.Sheets("Master Edit").Range("C" & Rows.Count).End(xlUp).Row
               eName = ThisWorkBook.Sheets(Master Edit).Range("C" & i).Value
               SaTotal =SaTotal + "(" & eName & "!$B$" & j & ")"
           Next i
     k = j + 1

         ThisWorkBook.Sheets("Master Edit").Range("A1").Select 'trying to get the activesheet

          MsgBox "The resulting formula for cell (F" & k & ")" & SaTotal

        With ActiveSheet
         .Range(k, 6).Value = SaTotal
       '  .Cell(k, 6).Value = SaTotal
       '  .Range("F" & k).Value = SaTotal
        End With

       '   ThisWorkBook.Sheets("Annual Report").Range("F" & k).Value = SaTotal
    
 Next j

I've tried all combinations of the code listed here, commenting out as required.

Its still breaking..

The Message Box indicats the CORRECT formula I am after is being generated.

Any Clues?

Aurbo99

The syntax is Cells (plural)
I can't see why the string based Ranges are erroring, I'd try

Range("F" & CStr(k))

or changing to this

SaTotal =SaTotal & "(" & eName & "!$B$" & j & ")"

I'm not sure why it isn't erroring with the plus sign.

Thanks Mike,

I've changed the + to an &

the formula still looks correct after that change,

The CStr(k)) had no change in the error.

Hi

Try this.

Sub WhatsWrong()
Dim i As Integer, j As Integer, k As Integer
Dim eName As String, SaTotal As String

    For j = 3 To 15
       SaTotal = "=Sum(" '*** changed
           For i = 4 To ThisWorkbook.Sheets("Master Edit").Range("C" & Rows.Count).End(xlUp).Row
               eName = ThisWorkbook.Sheets("Master Edit").Range("C" & i).Value '********** Change here
               SaTotal = SaTotal & eName & "!$B$" & j & ","  '***change
           Next i
     k = j + 1

         ThisWorkbook.Sheets("Master Edit").Range("A1").Select 'trying to get the activesheet
          SaTotal = Left(SaTotal, Len(SaTotal) - 1) & ")"  '*** new line
          MsgBox "The resulting formula for cell (F" & k & ")" & SaTotal
        
        With ActiveSheet
        ' .Range(k, 6).Value = SaTotal
        .Cells(k, 6).Formula = SaTotal '*** new line
       '  .Cell(k, 6).Value = SaTotal
       '  .Range("F" & k).Value = SaTotal
        End With

       '   ThisWorkBook.Sheets("Annual Report").Range("F" & k).Value = SaTotal
    
 Next j
End Sub

rylo