VBA Help Required

Hellow Experts,

I Have userform and Small Sample Data i need help to load data from 2nd match as i have inv num 10001 4times so i want to load one by one into userform till 4th value while clicking next button.

File is attached.

WiseOwl ExcelFinance ExxcutiveAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try something like this...

Place the following codes on UserForm Module. For details, refer to the attached and click the button on Sheet1 to show the userform.

Dim x, y()
Dim NextCnt As Long, PrevCnt As Long
Dim ws As Worksheet

Private Sub cmdNext_Click()
If NextCnt = 0 Then
    NextCnt = 2
    NextCnt = NextCnt + 1
End If

If NextCnt > UBound(y) Then
    MsgBox "You are already on the last record.", vbExclamation
    Exit Sub
End If

Me.txtInv = y(NextCnt, 1)
Me.txtRef = y(NextCnt, 2)
Me.txtParty = y(NextCnt, 3)
Me.txtReceivable = y(NextCnt, 4)
PrevCnt = NextCnt
End Sub

Private Sub cmdPrev_Click()
If PrevCnt > 0 Then
    PrevCnt = PrevCnt - 1
End If

If PrevCnt = 0 Then
    MsgBox "You are already on the first record.", vbExclamation
    Exit Sub
End If

If PrevCnt > UBound(y) Then
    MsgBox "You are already on the last record.", vbExclamation
    Exit Sub
End If

Me.txtInv = y(PrevCnt, 1)
Me.txtRef = y(PrevCnt, 2)
Me.txtParty = y(PrevCnt, 3)
Me.txtReceivable = y(PrevCnt, 4)

End Sub

Private Sub CommandButton1_Click()
'declare the variables
Dim FindRow1
Dim i As Long, j As Long
Dim cRow1 As String
Dim lr As Long, RecCnt As Long
Dim wb As Workbook

If Not IsNumeric(Me.txtSearch.Value) Then
    MsgBox "Please enter the Numeric INV Ref.", vbExclamation
    Exit Sub
End If
Set wb = ThisWorkbook

Set ws = Sheets("Sheet1")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
RecCnt = Application.CountIf(ws.Range("A3:A" & lr), Me.txtSearch.Value)

If RecCnt = 0 Then
    MsgBox "No matching record found!", vbExclamation
    Exit Sub
End If

x = ws.Range("A3:D" & lr).Value
ReDim y(1 To RecCnt, 1 To 4)

For i = 1 To UBound(x, 1)
    If Val(x(i, 1)) = Val(Me.txtSearch.Value) Then
        j = j + 1
        y(j, 1) = x(i, 1)
        y(j, 2) = x(i, 2)
        y(j, 3) = x(i, 3)
        y(j, 4) = x(i, 4)
    End If
Next i
srchCnt = 1
NextCnt = 0
PrevCnt = 1

Me.txtInv = y(1, 1)
Me.txtRef = y(1, 2)
Me.txtParty = y(1, 3)
Me.txtReceivable = y(1, 4)
Me.txtCnt = UBound(x, 1)
Me.txtSrchCnt = RecCnt
End Sub

Sub ClearTextBoxes()
Dim ctl As Control
For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Then ctl.Value = ""
Next ctl
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WiseOwl ExcelFinance ExxcutiveAuthor Commented:
its Working Thanks !!! I will take Further Help in This Regards afterward.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.