Posts by Fluff13

• Range.Cells function with formula

You're welcome & thanks for the feedback.

• Range.Cells function with formula

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"")"
• Sumif function not working.Returning zero

You're welcome & thanks for the feedback

• Sumif function not working.Returning zero

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

• Sumif function not working.Returning zero

If it's not working, can you supply a sample workbook

• Sumif function not working.Returning zero

Try it like

=SUMIF(Sheet1!B2:B27,"=>7 Days", Sheet1!D2:D27)

• repetition numbers in a single cell 3 times

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

• Go to another Sheet Macro

You're welcome & thanks for the feedback.

• sum total invoice value on list line

You're welcome & thanks for the feedback.

• sum total invoice value on list line

How about, in D2 copied down

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

• VBA Userform displays serial number instead of date

In the cmdEdit_Click() routine use

Code
1. Me.txtDate.Value = Format(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 1), "mm/dd/yyyy")
• UserForm ListBox with Array & Match

You're welcome & thanks for the feedback.

• UserForm ListBox with Array & Match

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

• UserForm ListBox with Array & Match

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/

• ComboBox Data Based on Previous ComboBox Selection

You're welcome & thanks for the feedback

• ComboBox Data Based on Previous ComboBox Selection

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
• Multiple Criteria Lookup while Working With Number

Glad to help & thanks for the feedback