Sumproduct Formula in VBA

[majesteleri]

Hi everybody,

I have a Run-time error 1004 problem at this row = ActiveCell.Formula = "=SUMPRODUCT(" & StrRange1 & ")"

What is the problem ?

Sub CLImproved()

  Application.ScreenUpdating = False
  Dim TLTOPLAMLAR As Integer
  Dim StrRange1 As String
  Dim Kur As String
  Dim rng As Range
  
    
  ActiveWorkbook.Sheets("Keşif Özeti").Select

    firmasayisi = Range("A1").Value
        Cells.Find(What:="Sıra" & Chr(10) & "No", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
    kesifilksatir = ActiveCell.Row + 1
    
    Cells.Find(What:="TL TOPLAMLAR", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    TLTOPLAMLAR = ActiveCell.Row
    Range("A5").Select
        Cells.Find(What:="Kur", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Kur = ActiveCell.Column
    Sheets("Format").Select
    Range("X1").Value = Kur
    Kursutun = Range("X2").Value
    Sheets("Keşif Özeti").Select

    
        Cells.Find(What:="Miktar", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Miktar = ActiveCell.Column
        Sheets("Format").Select
    Range("X1").Value = Miktar
    Miktarsutun = Range("X2").Value
    Sheets("Keşif Özeti").Select
  
    kesifsatirsayisi = kesifsonsatir - kesifilksatir
    Range("A5").Select
For Z = 1 To firmasayisi
    Cells(kesifilksatir - 1, Sonislemsutun + 1).Select
            Cells.Find(What:="Kur", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Kur = ActiveCell.Column
For i = 0 To 3

Cells(kesifilksatir - 1, Sonislemsutun + 1).Select

    Cells.Find(What:="Malzeme" & Chr(10) & "Birim Fiyatı", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
MBF = ActiveCell.Column + i
    Sheets("Format").Select
    Range("X1").Value = MBF
    MBFsutun = Range("X2").Value
    Sheets("Keşif Özeti").Select

StrRange1 = Miktarsutun & kesifilksatir & ":" & Miktarsutun & TLTOPLAMLAR - 1 & "," & Kursutun & kesifilksatir & ":" & Kursutun & TLTOPLAMLAR - 1 & "," & MBFsutun & kesifilksatir & ":" & MBFsutun & TLTOPLAMLAR - 1 & ")"
'''strRange1 = Chr(64 + Miktarsutun) & kesifilksatir & ":" & Chr(64 + Miktarsutun) & TLTOPLAMLAR - 1 & "," & Chr(64 + Kursutun) & kesifilksatir & ":" & Chr(64 + Kursutun) & TLTOPLAMLAR - 1 & "," & Chr(64 + MBFsutun + i) & kesifilksatir & ":" & Chr(64 + MBFsutun + i) & TLTOPLAMLAR - 1
strRange2 = MBFsutun & TLTOPLAMLAR
'''TCFormul = "=SUMPRODUCT(" & strRange1 & ")"
Range(strRange2).Select
ActiveCell.Formula = "=SUMPRODUCT(" & StrRange1 & ")"
Next i
.
.
.
.
.

 

[bebo021999]

Test first, by replace this

ActiveCell.Formula = "=SUMPRODUCT(" & StrRange1 & ")"

with

ActiveCell.Value = "SUMPRODUCT(" & StrRange1 & ")"

what value is in ActiveCell now? Is it a working sumproduct?

 

[My Aswer Is This]

The last line of your code should read:
End Sub

 

[Fluff]

Duplicate to: Excel VBA Set Activecell Formula With String

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.