Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use LIKE within sumproduct in macro, possible?
thank you, but is there a way to do this in VBA anyhow?
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct not Working in a Macro | Excel Worksheet Functions | |||
SUMPRODUCT macro | Excel Discussion (Misc queries) | |||
macro with SUMPRODUCT help | Excel Programming | |||
sumproduct in Excel Macro | Excel Programming | |||
How to use SUMPRODUCT in macro? | Excel Programming |