Excel VBA Vlookup

[Tony]

I am using the following code to take a value from a master sheet (BuddyL1)
and do a VLOOKUP in the same master worksheet named range (Buddyinfo) and
pick up the First name Initial). This is the cell value I receive when I
enter the cell where the formula eventually resides:

=VLOOKUP(BuddyL1,BuddyInfo,1,FALSE)

This is what I have so far which comes back with a #NAME error:

Private Sub CommandButton2_Click()
Dim wsMaster As Worksheet
Dim wsPh As Worksheet
Dim LRow As Long 'Start Row
Dim I As Integer
Dim iRow As Long 'Input Row
Dim LoopCol As Long 'Loop Column
Dim LoopRow As Long 'Loop Row
Dim Mcnt As Long ' Manager Count
Dim FirstRun As Boolean
Dim BuddyF1 As String
Dim BuddyF2 As String
Dim BuddyF3 As String
Dim BuddyL1 As String
Dim BuddyL2 As String
Dim BuddyL3 As String

'Change the following 2 lines to reflect the worksheets where the data is
Set wsMaster = Workbooks("Collections Master
Database.xls").Worksheets("Master")
Set wsPh = Workbooks("Collections Master Database.xls").Worksheets("Buddy HC
List")
FirstRun = True
LoopCol = 1
LoopRow = 3
Mcnt = 3 ' Master Row Start
iRow = 3 ' Source row start
While Not IsEmpty(Worksheets("Master").Cells(LoopRow, LoopCol))
With wsMaster
If wsMaster.Range("y" & iRow + 0) = "Admin" Then
wsPh.Range("a" & Mcnt) = wsMaster.Range("d" & iRow + 0)
wsPh.Range("b" & Mcnt) = wsMaster.Range("F" & iRow + 0)
BuddyL1 = wsMaster.Range("u" & iRow + 0)
BuddyL2 = wsMaster.Range("v" & iRow + 0)
BuddyL3 = wsMaster.Range("w" & iRow + 0)
wsPh.Range("c" & Mcnt) = wsMaster.Range("b" & iRow + 0) & ".
" & wsMaster.Range("A" & iRow + 0) & " (Admin)"
wsPh.Range("d" & Mcnt) = wsMaster.Range("h" & iRow + 0)
wsPh.Range("e" & Mcnt) = "=VLOOKUP(buddyL1,buddyinfo,1,FALSE)"
wsPh.Range("f" & Mcnt) = BuddyL2 & " ." & BuddyL1
wsPh.Range("g" & Mcnt) = BuddyL3 & ". " & BuddyL1
Mcnt = Mcnt + 1
iRow = iRow + 1
LoopRow = LoopRow + 1
Else
iRow = iRow + 1
LoopRow = LoopRow + 1
End If

 

[Jim Cone]

"=VLOOKUP(" & BuddyL1 & ",buddyinfo,1,FALSE)"
--
Jim Cone
Portland, Oregon USA



"Tony" <[email protected]>
wrote in message
I am using the following code to take a value from a master sheet (BuddyL1)
and do a VLOOKUP in the same master worksheet named range (Buddyinfo)
and pick up the First name Initial). This is the cell value I receive when I
enter the cell where the formula eventually resides:
=VLOOKUP(BuddyL1,BuddyInfo,1,FALSE)
This is what I have so far which comes back with a #NAME error:

-snip-
wsPh.Range("e" & Mcnt) = "=VLOOKUP(buddyL1,buddyinfo,1,FALSE)"
-snip-

 

[Tony]

Almost ther. I am getting the formula:

=VLOOKUP(Dungate,BuddyInfo,1,FALSE)

which returns #NAME? All I need now is to add the quote to the lookup value:

=VLOOKUP("Dungate",BuddyInfo,1,FALSE)

Tony

 

[Jim Cone]

Use: Chr$(34) to display a quote mark.
--
Jim Cone
Portland, Oregon USA



"Tony"
<[email protected]>
wrote in message
....
Almost there. I am getting the formula:
=VLOOKUP(Dungate,BuddyInfo,1,FALSE)
which returns #NAME? All I need now is to add the quote to the lookup value:
=VLOOKUP("Dungate",BuddyInfo,1,FALSE)
Tony