Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA Vlookup
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA Vlookup
"=VLOOKUP(" & BuddyL1 & ",buddyinfo,1,FALSE)"
-- Jim Cone Portland, Oregon USA "Tony" 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- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA Vlookup
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" wrote: "=VLOOKUP(" & BuddyL1 & ",buddyinfo,1,FALSE)" -- Jim Cone Portland, Oregon USA "Tony" 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- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA Vlookup
Use: Chr$(34) to display a quote mark.
-- Jim Cone Portland, Oregon USA "Tony" 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 "Jim Cone" wrote: "=VLOOKUP(" & BuddyL1 & ",buddyinfo,1,FALSE)" -- Jim Cone Portland, Oregon USA "Tony" 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
VLOOKUP Conversion from Excel 2003 to Excel 2007 | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |