dimitris798
09-28-2010, 02:44 PM
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
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