Help with Looping through a vlookup xl2010

[teach_me_VBA]

I'm new to coding VBA and have coded a good amount of what I'm trying to do, but there are a few things I'm having problems with coding. I think I might be able to figure out a few of the other issues I'm having with my code if I can first figure out how to loop through a vlookup to complete what I'm trying to do. I'm going to lay out my thought process with how I did my coding below:

First:
I wrote a series of functions to auto-fill cells based on a drop-down list I created. Below are the examples of 2 function codes I used for the multiple functions I created:

Function LockboxName(Prodnum)
LockboxName = Application.WorksheetFunction.VLookup(Prodnum, [RBSWaveData], 7, 0)
End Function

Function NumberofPayees(Prodnum)
NumberofPayees = Application.WorksheetFunction.VLookup(Prodnum, Range("RBSWaveData"), 99, 0)
End Function


Second:
I modified specific cell information to client specific request, for example:

="Worksource: 0000"&""&TCLockBox(B1)


Third:
Because I didn't want to ruin what I coded and was working correctly, I created a new tab titled "Payee Practice" & Module and the cell that I would like to begin adding the number of payees to is B14. From what I was able to figure out, I was able to come up with some code that works, but is not exactly correct.

Sub VlookupLoopExample()
Dim i, k As Long

With Sheets("payee practice")
i = 14
For k = 1 To 1
.Cells.Range("B14").FormulaR1C1 = "=VLOOKUP(R3C10,LB_510818,2)"
.Cells.Range("B15").FormulaR1C1 = "=VLOOKUP(R3C10,LB_510818,3)"
.Cells.Range("B16").FormulaR1C1 = "=VLOOKUP(R3C10,LB_510818,4)"
i = i + 4
Next k
End With
End Sub

Ideally, I would like to be able to loop through the range of accounts and beginning with cell B14, insert the total number of payees, varies by account, down column B and if the next cell is not blank, insert a new row for each new payee until the vlookup returns "". I think I'm good with inserting a new row:

ActiveCell.Offset(1).EntireRow.Insert

just need help with looping through the list of payees using a vlookup beginning with cell B14. Thanks.

 

[teach_me_VBA]

Below is some additional information that I hope helps show what I'm trying to do:

The first 2 columns is showing how the section initially looks. When I use the drop-down list and select account 12345, then the list is created as shown in the next 2 columns. And then finally, when I select a new account, 98765, then the results would look like the last 2 columns.

Account xxxxx Account 12345 Account 98765
Payees x Payees 7 Payees 4
Payee1 Payee1
Payee2 Payee2
Payee3 Payee3
Invalid Payees x Payee4 Payee4
Payee5
Payee6 Invalid Payees None
Payee7

Invalid Payees None


Below is what I coded first using the DO LOOP function, but was a circular reference that I had to manually stop.


Sub AddValidPayee2()

Dim validpayee As Variant
Dim ValPayee As String

Worksheets("payee practice").Activate

Range("B14").End(xlDown).Offset(1, 0).Select

Do
If ActiveCell <> "" Then
ActiveCell.EntireRow.Insert
End If

If Application.WorksheetFunction.VLookup(ActiveSheet.Range("j3"), [LB_510818], 2, 0) <> "" Then
ValPayee = Application.WorksheetFunction.VLookup(ActiveSheet.Range("j3"), [LB_510818], 2, 0)
ElseIf Application.WorksheetFunction.VLookup(ActiveSheet.Range("j3"), [LB_510818], 3, 0) <> "" Then
ValPayee = Application.WorksheetFunction.VLookup(ActiveSheet.Range("j3"), [LB_510818], 3, 0)
Else: ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveSheet.Range("j3"), [LB_510818], 4, 0)
ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveSheet.Range("j3"), [LB_510818], 4, 0)
End If
ActiveCell.Value = ValPayee
ActiveCell.Offset(1, 0).Value = validpayee
ActiveCell.Offset(2, 0).Value = validpayee

Loop Until ActiveCell.Value = ""
End Sub

Any help will be greatly appreciated. Thank you.