use LIKE within sumproduct in macro, possible?
 

hello, i was wondering if it would be possible to use LIKE somehow
within my sumproduct here is example

Sub TxMissing()
Dim nameOfViolation As String

For i = 7 To 500
If Sheets("Summary").Cells(i, "A").Value = "" Then
Exit Sub
End If
cnt = Evaluate("SUMPRODUCT((Detail!$D$7:$D$500" & _
"=""Tx Missing"")*(Detail!$B$7:$B$500=Summary!$A$" & _
'<---------- THIS LINE
i & "))")
Worksheets("Summary").Cells(i, "L").Value = cnt
Next i

End Sub
-----------------------------------------------------------------
where it says Tx Missing it is not always appears as Tx Missing,
sometimes it would have certain numbers after that sentence, and they
would be always random, for example Tx Missing-15678, or Tx
Missing-14786, so how would I specify in my macro that i need it to
look not for exact words, but for a sentece that starts with Tx
Missing.....
i have tryed to use < signs instead of = will work in my case for
this function, but i have similar funtions that i will need to create
and they are not workting properly :( please help me :)

use LIKE within sumproduct in macro, possible?
 

You need to do it like you would on a worksheet, not from within VBA:

cnt = Evaluate("SUMPRODUCT((LEFT(Detail!$D$7:$D$500,10)" & _
"=""Tx Missing"")*(Detail!$B$7:$B$500=Summary!$A$" & _
i & "))")

HTH,
Bernie
MS Excel MVP

wrote in message
oups.com...
hello, i was wondering if it would be possible to use LIKE somehow
within my sumproduct here is example

Sub TxMissing()
Dim nameOfViolation As String

For i = 7 To 500
If Sheets("Summary").Cells(i, "A").Value = "" Then
Exit Sub
End If
cnt = Evaluate("SUMPRODUCT((Detail!$D$7:$D$500" & _
"=""Tx Missing"")*(Detail!$B$7:$B$500=Summary!$A$" & _
'<---------- THIS LINE
i & "))")
Worksheets("Summary").Cells(i, "L").Value = cnt
Next i

End Sub
-----------------------------------------------------------------
where it says Tx Missing it is not always appears as Tx Missing,
sometimes it would have certain numbers after that sentence, and they
would be always random, for example Tx Missing-15678, or Tx
Missing-14786, so how would I specify in my macro that i need it to
look not for exact words, but for a sentece that starts with Tx
Missing.....
i have tryed to use < signs instead of = will work in my case for
this function, but i have similar funtions that i will need to create
and they are not workting properly :( please help me :)

use LIKE within sumproduct in macro, possible?
 

thank you, but is there a way to do this in VBA anyhow?

use LIKE within sumproduct in macro, possible?
 

you know what... i am sorry, you just gave me an answer what i should
do in order to make my code work, and i didnt even realize that, my
head is not thinking anymore today :) thank you very much your
addition worked perfectly for me