Hi all,

I'm very new here (first post) and relatively new to using VBA. I am building a procedure to organize a report that I generate at work and I cannot figure out how to use variables to make my vlookup function. I'm not sure what else to say about the code other than that. I have left example code below:

    ' We start to build the vlookup function
    ' it may be possible to build this using references to get us to the first cell of the range
    Range("C5").Select
    ActiveCell.Offset(0, monthCount + 1).Select
    'from there use the call ActiveCell.Address to get the exact location of the cursor
    Dim startRange As String
    startRange = ActiveCell.Address
    'save that value as a variable and then move over and down
    ActiveCell.Offset(73, 1).Select
    'use the call again and save that value as another variable
    Dim endRange As String
    endRange = ActiveCell.Address
    'use these two variables as the range for the vlookup function
    Range("C5").Select
    ActiveCell.Formula = "=IFERROR(VLOOKUP(A5, " & startRange & ":" & endRange & ", 2, FALSE), "")"
I left comments in there so that you could see my thought process through this section of the code. Also, the variable <monthCount> is defined earlier in the code and works just fine. When I run the code it fails on the last line with a 1004 error. When I click debug on the prompt that line goes yellow, mousing over the variable returns the ranges I want them to return, but none the less the code does not work. If I set up a variable that is assigned to an input box and then assign ActiveCell.Formula to that variable and write the vlookup function manually the code will work.

Anyway, this seems like it is really simple but none of the searches I have completed have turned up a working answer. Any help is much appreciated, if you need more infor just respond back.

Thanks,

T