PDA

View Full Version : Solved: vlookup from another worksheet



allison
04-03-2008, 11:48 AM
I hope that I can explain this well.

In the same workbook, I have:
Worksheet "Monthly Data" contains employee names.
Worksheet "Teams" contains team names (and employee names). There is a named range "TEAM" that has what I need.Dim FinalRow As Long
Dim SrcBook As Workbook

Application.ScreenUpdating = False

Set SrcBook = ThisWorkbook
SrcBook.Activate
Sheets("Monthly Data").Select

With ActiveSheet
FinalRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


ActiveCell.FormulaR1C1 = "Team"
Range("H2").Select
Sheets("Teams").Select
ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC[-6],Teams!Teams,2,FALSE)"
Range("H2").Select
Sheets("Monthly Data").Select
Selection.AutoFill Destination:=Range("H2:H" & FinalRow)
Range("H2:H" & FinalRow).Select
Columns("H:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:C").Select
Range("C1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.NumberFormat = "hh:mm:ss"

Application.ScreenUpdating = True


End Sub

I am trying to do a vlookup by the employee name on Monthly Data to get the team name they are associated with.

When I run this code (I know that it's done with the recorder, but I'm still learning...), I get nothing in the field for the result.

My thought is that it's happening because I'm not selecting the Teams sheet at the correct point, but I've tried it in a few different places with still poor results.

Any suggestions to correct the error would be very helpful.

Thanks in advance.

Simon Lloyd
04-03-2008, 12:10 PM
You may be getting nothing because you have a + in your formula:


ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC[-6],Teams!Teams,2,FALSE)"
but i think it should look like this:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],Teams!Teams,2,FALSE)"

allison
04-03-2008, 12:14 PM
Took that out and still nothing. It was actually putting the VLOOKUP formula in a cell in the TEAMS worksheet (and returning a #NAME) and returning nothing in the Monthly Data worksheet.

Simon Lloyd
04-03-2008, 12:26 PM
Thats because you are putting the formula in the Teams sheet:

Sheets("Teams").Select
ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC[-6],Teams!Teams,2,FALSE)"

you select the teams sheet then say that the activecell on that sheet has the formula you devised!

allison
04-03-2008, 12:41 PM
Okay....I wish I had an excuse for pulling that stupid move...but no such luck.

Thanks for the help!

Simon Lloyd
04-03-2008, 02:36 PM
Allison, believe me i Have made worse moves! ;)