Posts by Fluff13

You need to double-up the quotes inside the formula like

Code
1. Cells(LastRow + 6, "A").Formula = "=CONCATENATE(ROUND(DAYS(A" & LastRow + 5 & ",TODAY())/7,0),"" Weeks"")"

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

How about, in D2 copied down

=IF(A2=A3,"",SUMIFS(C:C,A:A,A2))

In the cmdEdit_Click() routine use

Code
1. Me.txtDate.Value = Format(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 1), "mm/dd/yyyy")

Ok I have added the formula

=ROW()

in P2 copied down & then in the Reset code us

Code
1. Id = Sheets("controls").Range("B1")
2. With Sheets("Datatable").Range("A2:P1000")
3. Rws = Filter(.Worksheet.Evaluate(Replace(Replace("transpose(if(@=" & Chr(34) & Id & Chr(34) & ",row(@)-#,false))", "@", .Columns(1).Address), "#", .Rows(0).Row)), False, False)
4. ReDim Preserve Rws(UBound(Rws) + 1)
5. Ary = Application.Index(.Value, Application.Transpose(Rws), Array(1, 7, 15, 16))
6. End With

and n the edit button click use

Code
1. With Me.ListBox1
2. If .ListIndex = -1 Then
3. MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"
4. Exit Sub
5. End If
6. Me.MRRowNumber.Value = .List(.ListIndex, 3)
7. End With
8. 'Code to update the values to respective controls
9. Dim sht1 As Worksheet

Files

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/

You're welcome & thanks for the feedback

Remove this line from the initialize event

Code
1. PartLst.RowSource = "UniquePart"

And then use

Code
1. Private Sub POLst_Click()
2. Me.PartLst.List = Evaluate("FILTER(PartsTbl[PART NUMBER],PartsTbl[PO NUMBER]=" & Me.POLst.Value & ")")
3. End Sub

Glad to help & thanks for the feedback