Excel Forum

Code: Select all

For i = 2 To Application.WorksheetFunction.CountA(Sheeet1.Range("A:A"))

Code: Select all

Private Sub UserForm_Initialize()
Dim i As Long
For i = 4 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
a = Application.WorksheetFunction.CountIf(Sheet1.Range("D" & 4, "D" & i), _
Sheet1.Cells(i, 4))
If a = 1 Then
Me.ListBox1.AddItem Sheet1.Cells(i, 4)
End If
Next i
End Sub

Private Sub CommandButton1_Click()
Me.ListBox1.Clear
Call UserForm_Initialize
Me.CommandButton1.Enabled = False
End Sub

Private Sub ListBox1_Click()
Me.TextBox1 = Me.ListBox1.Column(0)
End Sub

Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim i As Long
Me.ListBox1.Clear
Me.ListBox1.AddItem
For x = 0 To 8
Me.ListBox1.List(0, x) = Sheet1.Cells(1, x + 1)
Next x

For i = 2 To Sheet1.Range("A1048576").End(xlUp).Row

If Sheet1.Cells(i, 2) = Me.TextBox1 Then
Me.ListBox1.AddItem
For c = 0 To 8
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1)
Next c
End If
Next i
For d = 1 To Me.ListBox1.ListCount - 1
Sum = Sum + Val(Me.ListBox1.List(h, 3))
Next d
Me.ListBox1.AddItem
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Total Price"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Sum & ".00"
Me.CommandButton1.Enabled = True
Me.ListBox1.Selected(0) = True

End Sub

Code: Select all

'Other code
For i = 2 To Sheet1.Range("A1048576").End(xlUp).Row
    If Sheet1.Cells(i, 4) = CLng(Me.TextBox1.Text) Then
        Me.ListBox1.AddItem
        For c = 0 To 8
            If c + 1 = 1 Then
                 Me.ListBox1.List(ListBox1.ListCount - 1, c) = Format( _
                    Sheet1.Cells(i, c + 1).Value2, "dd/mm/yyyy")
            Else
                Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1)
            End If
        Next c
    End If
Next i
'Other code

Code: Select all

Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim i As Long
Me.ListBox1.Clear
Me.ListBox1.AddItem
'Other code
For i = 2 To Sheet1.Range("A1048576").End(xlUp).Row
    If Sheet1.Cells(i, 4) = CLng(Me.TextBox1.Text) Then
        Me.ListBox1.AddItem
        For c = 0 To 8
            If c + 1 = 1 Then
                 Me.ListBox1.List(ListBox1.ListCount - 1, c) = Format( _
                    Sheet1.Cells(i, c + 1).Value2, "dd/mm/yyyy")
            Else
                Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1)
            End If
        Next c
    End If
Next i
'Other code
For d = 1 To Me.ListBox1.ListCount - 1
Sum = Sum + Val(Me.ListBox1.List(d, 3))
Next d
Me.ListBox1.AddItem
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Total Price"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Sum & ".00"
Me.CommandButton1.Enabled = True
Me.ListBox1.Selected(0) = True

End Sub

Code: Select all

'Other code
For d = 1 To Me.ListBox1.ListCount - 1
    Sum = Sum + Val(Me.ListBox1.List(d, 2))
Next d
Me.ListBox1.AddItem
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = "Total Price"
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Format(Sum, "#,##0.00")
'Other code