Sumproduct with macro

[blckwolf]

Hi everybody,

In the attached file, in the "Hata Koduna Göre Dağılım" page, I tried to show distribution of hourly rates on the machine, according to the error code. I tried to adapt the code, but when it works, code gives "NAME?" error. What is reason? If somebody help me, I am grateful.

[tom1977]

Hi there are differences between the names of worksheets in code and in worksheets like below

("Hata Koduna Göre Dańţlţm")

Hata Koduna Göre Dağılım

it could be the first problem

[blckwolf]

Hi there are differences between the names of worksheets in code and in worksheets like below

("Hata Koduna Göre Dańţlţm")

Hata Koduna Göre Dağılım

it could be the first problem

The code is below, I dont understand, what is difference?

Sub SumProduct15()
'Dim s As Long
Dim s, sRangeA, sRangeB, sRangeC, sRangeD, sRangeE
Dim Criter1, Criter2, Criter3, Criter4, j, i
s = Sheets("Bakım Kayıtları.").[a65536].End(3).Row
sRangeA = "Bakım Kayıtları.!F3:F" & s
sRangeB = "Bakım Kayıtları.!H3:H" & s
sRangeC = "Bakım Kayıtları.!A3:A" & s
sRangeD = "Bakım Kayıtları.!C3:C" & s
sRangeE = "Bakım Kayıtları.!L3:L" & s
Criter1 = Format(Sheets("Hata Koduna Göre Dağılım").[C1], "00000")
Criter2 = Format(Sheets("Hata Koduna Göre Dağılım").[C2], "00000")
For j = 2 To 51
Criter3 = """" & Sheets("Hata Koduna Göre Dağılım").Cells(3, j) & """"
For i = 4 To Sheets("Hata Koduna Göre Dağılım").[a65536].End(3).Row
Criter4 = """" & Sheets("Hata Koduna Göre Dağılım").Cells(i, 1) & """"
Sheets("Hata Koduna Göre Dağılım").Cells(i, j) = Evaluate("=SumProduct((" & sRangeA & ">=" & Criter1 & _
")*(" & sRangeB & "<=" & Criter2 & _
")*(" & sRangeC & "=" & Criter3 & _
")*(" & sRangeD & "=" & Criter4 & _
")*(" & sRangeE & "))")
Next
Next
MsgBox "Bitti."
End Sub

[tom1977]

Hi
I have changed the name of worksheets (just to check the code) I have also changed the code. Now in the sheets formula will appears so You can check it (especially in the cells when code will stop running)
Second thing is that You use names for ranges which are not allow in excel e.g. A123243 or something like this .

[blckwolf]

Hi tom1977

I tried your suggestion and changed page names as like as "Dagilim" and "Bakim". The code is working, but page is empty. Something is wrong, but ı don't understand. Do you have any idea?

[tom1977]

Page is not empty You have conditional formatting (in original file) which changes font color of characters when value is = 0 (delete this or change)

[blckwolf]

Page is not empty You have conditional formatting (in original file) which changes font color of characters when value is = 0 (delete this or change)

Yes, ı see. But, what is the reason the value=0? It must be real total hourly values in cells.

[tom1977]

Your code creates such formula (with changed names) In my opinion the red conditions are never met so the result of formula is 0 and conditional formatting change the font color into white. Check this formula and after checking you should change code in proper way and everything will be ok I hope

SUMPRODUCT((bakim!F3:F381>=41000)*(bakim!H3:H381<=41029)*(bakim!A3:A381=A1)*(bakim!C3:C381=8)*(bakim!L3:L381))

[blckwolf]

The formula means like is below. What is wrong?

Criter1=Date1
Criter2=Date2
Criter3=Error Code (Hata Kodu)
Criter4=Machine No (Makina no)
L3:L381=Total hours

SUMPRODUCT((bakim!F3:F381>=41000)*(bakim!H3:H381<=41029)*(bakim!A3:A381=A001)*(bakim!C3:C381=1)*(bakim!L3:L381))

[tom1977]

Hi ok I found this there was some mistakes in code with """ and ranges. To use code in this shape You have to remember to keep data i column A (“Bakim” sheet) formatted as text because You use number and text in column A of "hata" sheets.
Unfortunately the code is not fast very much and I think You will have to think about rebuilt this if the number of data will increase.

[blckwolf]

Dear tom1977

My problem solved below code. The code working like as Ferrari! I hope this code helps to you or someone else. Thanks for your interests.
Code:
Sub evnSumProduct15()
Dim s As Worksheet
Set s = Sheets("Dagilim")
With Sheets("Bakim")
For i = 3 To .Range("a65536").End(3).Row
If CLng(CDate(.Cells(i, "f").Value)) >= CLng(CDate(s.Range("c1").Value)) And _
CLng(CDate(.Cells(i, "f").Value)) <= CLng(CDate(s.Range("c2").Value)) And _
Len(.Cells(i, "c").Value) = 4 And .Cells(i, "c").Value Like "A*" Then
ariza = .Cells(i, "c").Value
makina = .Cells(i, "a").Value
Set msatir = s.Range("a:a").Find(makina, , , 1)
If msatir Is Nothing Then
makinahata = makinahata & makina & vbLf
Else
satir = msatir.Row
Set asutun = s.Rows(3).Find(ariza, , , 1)
If asutun Is Nothing Then
arizahata = arizahata & ariza & vbLf
Else
sutun = asutun.Column
End If
End If
If satir > 0 And sutun > 0 Then
s.Cells(satir, sutun).Value = .Cells(i, "l").Value
End If
End If
Next i
End With
If arizahata <> "" Then MsgBox "Error code not found " & vbLf & vbLf & arizahata, vbExclamation, "Error Code"
If makinahata <> "" Then MsgBox "Machine code not found " & vbLf & vbLf & makinahata, vbExclamation, "Machine Code"
MsgBox "Bitti.", vbInformation, "Completed "
End Sub