populate price average in textbox on userform

Leopard

Average is an Excel worksheet function (not an Application feature) so your line:

TextBox6.Text = Format(Application.Average(MyPrAv), "#,##0.00")

should read:

TextBox6.Text = Format(WorksheetFunction.Average(MyPrAv), "#,##0.00")

BUT your variable MyPrAv is a single number, accumulated via the line:

If j = 6 Then MyPrAv = MyPrAv + a(i, j)

so it make no sense to average that, a single number.

You probably need to count the number of matches and (assuming that is>0) divide the accumulated number by that count.

Revision #1 (17 June 2024)

Looking at this again, there's no need for the variable MyPrAv the avarage price paid can be caculaded from existing variables MyTotal and MyTotall as follow (changes in bold and withing the attached revised file)

Sub FilterData()

Dim lindex&
Dim MyTotal As Double, MyTotal1 As Double ', MyPrAv As Double ' don't use
Dim fDate As Date, tDate As Date
Dim txt1 As String, txt2 As String, txt3 As String
Dim i As Long, j As Long, k As Long, iMax As Long

ListBox1.Clear: MyTotal = 0

On Error Resume Next
fDate = DateValue(TextBox2.Text)
tDate = DateValue(TextBox3.Text)
On Error GoTo 0

ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))

For i = 1 To UBound(a)
  If TextBox1 = "" Then txt1 = a(i, 4) Else txt1 = TextBox1
  If (LCase(a(i, 4)) Like LCase(txt1) & "*") And ((tDate = 0 And fDate = 0) Or (DateValue(a(i, 1)) >= fDate And DateValue(a(i, 1)) <= tDate)) Then
      k = k + 1
      For j = 1 To 7
          If j = 1 Then  'Change the 1 in this line for the column number where you have the date
              b(k, j) = Format(a(i, j), "dd/mm/yyyy")
          Else
              b(k, j) = a(i, j)
              If j = 5 Then MyTotal = MyTotal + a(i, j)
              'If j = 6 Then MyPrAv = MyPrAv + a(i, j) ' do nothing for this
              If j = 7 Then MyTotal1 = MyTotal1 + a(i, j)
          End If
      Next
  End If
Next
If k > 0 Then ListBox1.List = b
With ListBox1
    .ColumnWidths = "70;100;90;150;80"
    For i = 0 To .ListCount - 1
        .List(i, 4) = Format(.List(i, 4), "#,##0.00")
        .List(i, 5) = Format(.List(i, 5), "#,##0.00")
        .List(i, 6) = Format(.List(i, 6), "#,##0.00")
    Next i
End With
TextBox4.Text = Format(MyTotal, "#,##0.00")
TextBox5.Text = Format(MyTotal1, "#,##0.00")
' TextBox6.Text = Format(Application.Average(MyPrAv), "#,##0.00") ' replace with line below
TextBox6.Text = Format(MyTotal1 / MyTotal, "#,##0.00") ' divide summed price by summed quantity

End Sub

I tried this for item 1200R20 G580 JAP and it gave the correct avearge of 2,484.70 with no data in textboxes 2 and 3 (FD and TD).

Revision #2 (17 June 2024)

Given the user clarified the requirement is the average BATCH price (not the true avarage unit price), the SECOND attached file has the following code (changes in bold):

Sub FilterData()

Dim lindex&
Dim MyTotal As Double, MyTotal1 As Double, MyPrAv As Double
Dim fDate As Date, tDate As Date
Dim txt1 As String, txt2 As String, txt3 As String
Dim i As Long, j As Long, k As Long, iMax As Long
Dim p As Long

ListBox1.Clear: MyTotal = 0

On Error Resume Next
fDate = DateValue(TextBox2.Text)
tDate = DateValue(TextBox3.Text)
On Error GoTo 0

ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))

p = 0 ' reset batch counter

For i = 1 To UBound(a)
  If TextBox1 = "" Then txt1 = a(i, 4) Else txt1 = TextBox1
  If (LCase(a(i, 4)) Like LCase(txt1) & "*") And ((tDate = 0 And fDate = 0) Or (DateValue(a(i, 1)) >= fDate And DateValue(a(i, 1)) <= tDate)) Then
      k = k + 1

      For j = 1 To 7
          If j = 1 Then  'Change the 1 in this line for the column number where you have the date
              b(k, j) = Format(a(i, j), "dd/mm/yyyy")
          Else
              b(k, j) = a(i, j)
              If j = 5 Then MyTotal = MyTotal + a(i, j)
              If j = 6 Then
                MyPrAv = MyPrAv + a(i, j) ' add batch prices
                p = p + 1 ' count batches
              End If
              If j = 7 Then MyTotal1 = MyTotal1 + a(i, j)
          End If
      Next
  End If
Next
If k > 0 Then ListBox1.List = b
With ListBox1
    .ColumnWidths = "70;100;90;150;80"
    For i = 0 To .ListCount - 1
        .List(i, 4) = Format(.List(i, 4), "#,##0.00")
        .List(i, 5) = Format(.List(i, 5), "#,##0.00")
        .List(i, 6) = Format(.List(i, 6), "#,##0.00")
    Next i
End With
TextBox4.Text = Format(MyTotal, "#,##0.00")
TextBox5.Text = Format(MyTotal1, "#,##0.00")
If p > 0 Then
    TextBox6.Text = Format(MyPrAv / p, "#,##0.00") ' calculate average batch price
    Else
    TextBox6.Text = ""
End If

End Sub