Defining Variables

[Jenawade]

My macro opens a couple of different workbooks and enters a formula or two just to collect the results and use them in the workbook where the macro resides. Below is one of them.

    Windows("1.10.2.2.xls").Activate
    Range("K22").Formula = "=MAX(E4:E" & NoOfRowsA & ")"
    Range("L22").Formula = "=VLOOKUP(K22,E4:I" & NoOfRowsA & ",5,0)"
    CurrCost = Range("L22").Value
    CurrDate = Range("K22").Value

How can I eliminate the Range().Formula for the above? I don't know how to word this, but I'd like to (put/calculate?) the data right in the variable, something like this:

    CurrCost = "=VLOOKUP(K22,E4:I" & NoOfRowsA & ",5,0)"
    CurrDate = "=MAX(E4:E" & NoOfRowsA & ")"

 

[pgc01]

Hi Jen

Try (both not tested):

CurrCost = Evaluate("=VLOOKUP(K22,E4:I" & NoOfRowsA & ",5,0)")

or use the WorksheetFunction

CurrCost = Application.WorksheetFunction.VLookup(Range("K22"), Range("E4:I" & NoOfRowsA), 5, 0)

 

[Jenawade]

Hi Jen

Try (both not tested):

CurrCost = Evaluate("=VLOOKUP(K22,E4:I" & NoOfRowsA & ",5,0)")

or use the WorksheetFunction

CurrCost = Application.WorksheetFunction.VLookup(Range("K22"), Range("E4:I" & NoOfRowsA), 5, 0)

Thanks for the quick response! I'm making a lot of big changes to the entire macro today so I'm just going to copy/paste the first one right in for now and test it tomorrow.