You're welcome & thanks for the feedback
Posts by Fluff13
-
-
Without the = sign the formula is treating the > as a comparison, so it's looking for anything that is greater than the text "7 Days"
But with the = sign it treats the > as text
-
If it's not working, can you supply a sample workbook
-
Try it like
=SUMIF(Sheet1!B2:B27,"=>7 Days", Sheet1!D2:D27)
-
You're formula is returning this array
{"71";"15";"54";"45";"57";"77";"78";"85";"50";"08";"81";"10";"08";"8"}
As you can see the final digit is repeated on it's own. Try
=MAX(FREQUENCY(0+MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),2),0+MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),2)))>=3
-
You're welcome & thanks for the feedback.
-
-
You're welcome & thanks for the feedback.
-
How about, in D2 copied down
=IF(A2=A3,"",SUMIFS(C:C,A:A,A2))
-
-
You're welcome & thanks for the feedback.
-
Ok I have added the formula
=ROW()
in P2 copied down & then in the Reset code us
Code- Id = Sheets("controls").Range("B1")
- With Sheets("Datatable").Range("A2:P1000")
- Rws = Filter(.Worksheet.Evaluate(Replace(Replace("transpose(if(@=" & Chr(34) & Id & Chr(34) & ",row(@)-#,false))", "@", .Columns(1).Address), "#", .Rows(0).Row)), False, False)
- ReDim Preserve Rws(UBound(Rws) + 1)
- Ary = Application.Index(.Value, Application.Transpose(Rws), Array(1, 7, 15, 16))
- End With
and n the edit button click use
-
I read up on the forum rules a bit more after you posted.
Obviously not very well as you haven't mentioned your cross posting.
as posted on MrExcel https://www.mrexcel.com/board/…-array-and-match.1168920/
Display MoreCode- Sub MRReset()
- Dim Rws As Variant, Ary As Variant
- Dim Id As String
- With MRForm
- .ComboBox1.Value = ""
- .ComboBox2.Value = ""
- .ComboBox3.Value = ""
- .ComboBox4.Value = ""
- .TextBox1.Value = ""
- .ComboBox6.Value = ""
- .ComboBox7.Value = ""
- .TextBox2.Value = ""
- .ComboBox9.Value = ""
- .ComboBox10.Value = ""
- .TextBox3.Value = ""
- .MRRowNumber.Value = ""
- End With
- Id = Sheets("controls").Range("B1")
- With Sheets("Datatable").Range("A2:O1000")
- Rws = Filter(.Worksheet.Evaluate(Replace(Replace("transpose(if(@=" & Chr(34) & Id & Chr(34) & ",row(@)-#,false))", "@", .Columns(1).Address), "#", .Rows(0).Row)), False, False)
- ReDim Preserve Rws(UBound(Rws) + 1)
- Ary = Application.Index(.Value, Application.Transpose(Rws), Array(1, 7, 15))
- End With
- MRForm.ListBox1.List = Ary
- End Sub
-
You're welcome & thanks for the feedback
-
-
Glad to help & thanks for the feedback
-
How about
=INDEX(H5:K12,MATCH(G18,G5:G12,0),MATCH(H18,H4:K4,0))
although I think the result should be 16 not 46
-
Glad you sorted it & thanks for the feedback.
-
Are you sure they are ActiveX controls & not Form controls?
Also was the correct sheet active when you ran that code?
-
You're welcome & thanks for the feedback.