none
the msgbox [b4] line shows contents from sheet1 and not "tables". RRS feed

  • Question

  • I'm not sure what is wrong with the syntax here. Probably something really basic, but I don't know. The BirthYear gets places in Worksheets("tables") e 18 but the msgbox shows the contents of b4 on the first sheet. Also, I'm not sure what the proper format should be for the vlookup line. Can someone help? Here is the code:

    Sub SocSecComp()

        Dim BirthYear, BirthMonth, BirthDay, NormalRetirementYear, NormalRetirementMonth, NormalRetirementDay, NormalRetirementAge As Integer
        Dim LookupRange As Range
        
     
        With Worksheets("Sheet1")
        'Calculate full Retirement Date
        BirthYear = Year(Range("DOB"))
        BirthMonth = Month(Range("DOB"))
        BirthDay = Day(Range("DOB"))
        End With


    With Worksheets("tables")
        .Range("e18").Value = BirthYear
        MsgBox [b4]
        LookupRange = Range("a5: c108")
        NormalRetirementAge = Application.WorksheetFunction.VLookup(Worksheets("tables").Range("e18"), Worksheets("tables").Range("LookupRange"), 3, True)
        MsgBox NormalRetirementAge
    End With

    Thursday, February 19, 2015 9:13 PM

Answers

  • The line

        Dim BirthYear, BirthMonth, BirthDay, NormalRetirementYear, NormalRetirementMonth, NormalRetirementDay, NormalRetirementAge As Integer

    declares NormalRetirementAge as an Integer and the other variables as Variant since you do not specify their data type. If you want all of them to be Integers, use

        Dim BirthYear As Integer, BirthMonth As Integer, BirthDay As Integer, NormalRetirementYear As Integer, _
            NormalRetirementMonth As Integer, NormalRetirementDay As Integer, NormalRetirementAge As Integer

    In the line

    BirthYear = Year(Range("DOB"))

    Range("DOB") does not (necessarily) refer to Worksheets("Sheet1"), but to the active sheet.

    Similarly, [b4] refers to B4 on the active sheet since you don't specify which sheet it belongs to.

    Since LookupRange is an object, you must use the keyword Set:

    Set LookupRange = .Range("a5:c108")

    Note the . before Range to specify that it belongs to the sheet mentioned in With ...

    So the code could look like

    Sub SocSecComp()
        Dim BirthYear As Integer, BirthMonth As Integer, BirthDay As Integer, _
            NormalRetirementYear As Integer, NormalRetirementMonth As Integer, _
            NormalRetirementDay As Integer, NormalRetirementAge As Integer
        Dim LookupRange As Range
     
        With Worksheets("Sheet1")
            'Calculate full Retirement Date
            BirthYear = Year(.Range("DOB"))
            BirthMonth = Month(.Range("DOB"))
            BirthDay = Day(.Range("DOB"))
        End With
    
        With Worksheets("tables")
            MsgBox .Range("B4")
            Set LookupRange = .Range("a5:c108")
            NormalRetirementAge = Application.WorksheetFunction.VLookup(BirthYear, LookupRange, 3, True)
            MsgBox NormalRetirementAge
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by DBW5455 Tuesday, February 24, 2015 2:40 PM
    Thursday, February 19, 2015 9:46 PM

All replies

  • The line

        Dim BirthYear, BirthMonth, BirthDay, NormalRetirementYear, NormalRetirementMonth, NormalRetirementDay, NormalRetirementAge As Integer

    declares NormalRetirementAge as an Integer and the other variables as Variant since you do not specify their data type. If you want all of them to be Integers, use

        Dim BirthYear As Integer, BirthMonth As Integer, BirthDay As Integer, NormalRetirementYear As Integer, _
            NormalRetirementMonth As Integer, NormalRetirementDay As Integer, NormalRetirementAge As Integer

    In the line

    BirthYear = Year(Range("DOB"))

    Range("DOB") does not (necessarily) refer to Worksheets("Sheet1"), but to the active sheet.

    Similarly, [b4] refers to B4 on the active sheet since you don't specify which sheet it belongs to.

    Since LookupRange is an object, you must use the keyword Set:

    Set LookupRange = .Range("a5:c108")

    Note the . before Range to specify that it belongs to the sheet mentioned in With ...

    So the code could look like

    Sub SocSecComp()
        Dim BirthYear As Integer, BirthMonth As Integer, BirthDay As Integer, _
            NormalRetirementYear As Integer, NormalRetirementMonth As Integer, _
            NormalRetirementDay As Integer, NormalRetirementAge As Integer
        Dim LookupRange As Range
     
        With Worksheets("Sheet1")
            'Calculate full Retirement Date
            BirthYear = Year(.Range("DOB"))
            BirthMonth = Month(.Range("DOB"))
            BirthDay = Day(.Range("DOB"))
        End With
    
        With Worksheets("tables")
            MsgBox .Range("B4")
            Set LookupRange = .Range("a5:c108")
            NormalRetirementAge = Application.WorksheetFunction.VLookup(BirthYear, LookupRange, 3, True)
            MsgBox NormalRetirementAge
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by DBW5455 Tuesday, February 24, 2015 2:40 PM
    Thursday, February 19, 2015 9:46 PM
  • Thank you very much. That worked.
    • Edited by DBW5455 Friday, February 20, 2015 3:54 PM
    Friday, February 20, 2015 3:53 PM