Solved: Vlookup function in VBA Excel

Hello everyone. I have drafted a vba funtion for which purposes is to conduct the following:

a) lookup the year entered in a cell,
b) For years 2003 and 2004 conduct a vlookup search refering to a separate cell within "Sheet1" and pull up the values from "Sheet2", and
c) For years 2005 through 2011 present the actual year.

I am currently working on an extensive database that has multiple rows. The formula works for years 2005 through 2011 when I try to copy and paste the formula to cells below the active cell. However, the vlookup function for years 2003 and 2004 only work when I actually type the function to each cell. The vlookup function for years 2003 and 2004 does not work when I try to copy and paste the formula to cells below the active cell.

My goal is to have the formula and the vlookup funtion work and be able to copy and paste through the cells below the active cell.

Provided below, I have presented the vba function code

Please let me know what edits should be made to make this formula work.

Thank you in advance,

Dimitris

[VBA]Function rvulookup(InputDate As Date)
Dim DayNumber As Integer
DayNumber = Year(InputDate)
Select Case DayNumber
Case 2003
rvulookup = Application.VLookup(ActiveCell.Offset(0, -2), Worksheets("Sheet2").Range("A1:z16000"), 2, 1 = 2)
Case 2004
rvulookup = Application.VLookup(ActiveCell.Offset(0, -2), Worksheets("Sheet2").Range("A1:z16000"), 2, 1 = 2)
Case 2005
rvulookup = "2005"
Case 2006
rvulookup = "2006"
Case 2007
rvulookup = "2007"
Case 2008
rvulookup = "2008"
Case 2009
rvulookup = "2009"
Case 2010
rvulookup = "2010"
Case 2011
rvulookup = "2011"
End Select
End Function[/VBA]

Quote:

---

Originally Posted by dimitris798

Hello everyone. I have drafted a vba funtion for which purposes is to conduct the following:

a) lookup the year entered in a cell,
b) For years 2003 and 2004 conduct a vlookup search refering to a separate cell within "Sheet1" and pull up the values from "Sheet2", and
c) For years 2005 through 2011 present the actual year.

I am currently working on an extensive database that has multiple rows. The formula works for years 2005 through 2011 when I try to copy and paste the formula to cells below the active cell. However, the vlookup function for years 2003 and 2004 only work when I actually type the function to each cell. The vlookup function for years 2003 and 2004 does not work when I try to copy and paste the formula to cells below the active cell.

My goal is to have the formula and the vlookup funtion work and be able to copy and paste through the cells below the active cell.

Provided below, I have presented the vba function code

Please let me know what edits should be made to make this formula work.

Thank you in advance,

Dimitris

Function rvulookup(InputDate As Date)
Dim DayNumber As Integer
DayNumber = Year(InputDate)
Select Case DayNumber
Case 2003
rvulookup = Application.VLookup(ActiveCell.Offset(0, -2), Worksheets("Sheet2").Range("A1:z16000"), 2, 1 = 2)
Case 2004
rvulookup = Application.VLookup(ActiveCell.Offset(0, -2), Worksheets("Sheet2").Range("A1:z16000"), 2, 1 = 2)
Case 2005
rvulookup = "2005"
Case 2006
rvulookup = "2006"
Case 2007
rvulookup = "2007"
Case 2008
rvulookup = "2008"
Case 2009
rvulookup = "2009"
Case 2010
rvulookup = "2010"
Case 2011
rvulookup = "2011"
End Select
End Function

---

Don't put this in:

ActiveCell.Offset

Instead use Target and put this as the header instead of what you have currently:

[vba]
Function rvulookup(target as range, InputDate As Date)
[/vba]

Now make sure you put the cell reference as your first argument when you enter the formula in to excel.

Also you can condense this:

[vba]
Case 2005
rvulookup = "2005"
Case 2006
rvulookup = "2006"
Case 2007
rvulookup = "2007"
Case 2008
rvulookup = "2008"
Case 2009
rvulookup = "2009"
Case 2010
rvulookup = "2010"
Case 2011
rvulookup = "2011"
End Select
[/vba]

to:

[vba]
Case 2005 to 2011
rvulookup = DayNumber
End Select
[/vba]

In fact, even shorter, try this:

[vba]Function rvulookup(Target As Range, InputDate As Date)
Select Case Year(InputDate)
Case 2003 To 2004
rvulookup = Application.worksheetfunction.VLookup(Target, Worksheets("Sheet2").Range("A1:z16000"), 2, 1 = 2)
Case 2005 To 2011
rvulookup = Year(InputDate)
End Select
End Function
[/vba]

Blade Hunter:

Thank you for your response. The formula works great.

Thanks,

Dimitris

Happy to help :)