Hi
I have a userform with listbox and textbox. My textbox use to find the value in worksheet and fill to my listbox.
I have a total of 13 volumns. So trying this process, i got an error saying that "Could not set the value property, invalid property value " and point the cursor with yellow color to this codes .List(.ListCount - 1, 10) = i.Offset(0, 10).Value
when removing this codes, it is working properly. May i ask your help guys on how to fixed this or any idea. thanks.
here is my code:
Private Sub CmbSearch_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range
Set wb = ThisWorkbook
Set ws = wb.Sheets("TransactionDB")
If Me.TextBox1.Value <> "" Then
FindWhat = TextBox1.Value
Set SearchRange = ws.Range(ws.Cells(1, 1), ws.Cells(Rows.Count, 1).End(xlUp))
Set FoundCells = FindAll(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
If FoundCells Is Nothing Then
'Debug.Print "Value Not Found"
Else
For Each i In FoundCells
With Me.ListBox1
.AddItem i.Value
.List(.ListCount - 1, 1) = i.Offset(0, 1).Value
.List(.ListCount - 1, 2) = i.Offset(0, 2).Value
.List(.ListCount - 1, 3) = i.Offset(0, 3).Value
.List(.ListCount - 1, 4) = i.Offset(0, 4).Value
.List(.ListCount - 1, 5) = i.Offset(0, 5).Value
.List(.ListCount - 1, 6) = i.Offset(0, 6).Value
.List(.ListCount - 1, 7) = i.Offset(0, 7).Value
.List(.ListCount - 1, 8) = i.Offset(0, 8).Value
.List(.ListCount - 1, 9) = i.Offset(0, 9).Value
.List(.ListCount - 1, 10) = i.Offset(0, 10).Value
End With
Next i
End If
MsgBox "Records Save!", vbInformation
End If
End Sub