You're welcome & thanks for the feedback.
Posts by Fluff13
-
-
-
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.
-
You need to qualify the evaluate, or it's looking at the active sheet
Code- Dim v, ws As Worksheet
- Dim vRws As Variant
- Set ws = ThisWorkbook.Worksheets("Sheet1")
- With ws.ListObjects("Table1").DataBodyRange
- vRws = Filter(Application.Transpose(ws.Evaluate(Replace(Replace("if(#=FALSE,row(#)-~,""X"")", "#", .Columns(35).Address), "~", .Rows(0).row))), "X", False)
- v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 14, 15)
-
Glad to help & thanks for the feedback.
-
-
-