You're welcome & thanks for the feedback.
Posts by Fluff13
-
-
Ok I have added the formula
=ROW()
in P2 copied down & then in the Reset code us
CodeId = 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 Withand n the edit button click use
CodeWith Me.ListBox1 If .ListIndex = -1 Then MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit" Exit Sub End If Me.MRRowNumber.Value = .List(.ListIndex, 3) End With 'Code to update the values to respective controls Dim sht1 As Worksheet -
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/
Code
Display MoreSub 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
-
How about
Remove this line from the initialize event
CodePartLst.RowSource = "UniquePart"And then use
CodePrivate Sub POLst_Click() Me.PartLst.List = Evaluate("FILTER(PartsTbl[PART NUMBER],PartsTbl[PO NUMBER]=" & Me.POLst.Value & ")") End Sub -
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
CodeDim 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.
-
Assuming you want to ignore col A when finding the last row, how about
Codelastrow = Range("B:H").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row -
You can get rid of these lines
CodeApplication.DeleteCustomList ListNum:=9 Application.AddCustomList ListArray:=Array("PUH IV", "PUH D PUH Charge", "PUH Flex Shift")as you already have the custom list in the code.
-
If they are named ranges try
CodeMeanMSEMod = Application.WorksheetFunction.Average(Range("MSEMod")) StdDevMSEMod = Application.WorksheetFunction.StDev(Range("MSEMod")) -
You're welcome & thanks for the feedback.
-
In that case add 1 to i in the index
-
How about
Codeg = Me.cbChooseGroup.List For i = 0 To UBound(g) If g(i, 0) = Me.cbChooseGroup.Value Then wksDest.Range("GroupImportRange").Resize(, 8).Value = Application.Index(g, i) Exit For End If Next i -
With
CodeMe.cbChooseGroup.Value -
You can put the combo list into an array like
Codeg = Me.cbChooseGroup.Listand then loop through that.