Using a function to return a value in VBA without using the worksheet

Question

Good afternoon One and All,

I am relatively new to VBA and I am trying to use the Worksheet function, specifically the index function to look up information in the code and bring back a value. I would love to do this without having to assign it to a cell in the worksheet using R1C1. Is there a way to do Vlookups, or Indexex without having to make assignments in the worksheet? The examples below works as an equation placed within the sheet, but I'd like to get the same answer without having to use the sheet. The first formula looks up a Batch number The second formula is finding the name of the first ingredient in the batch, based on the SKU in the worksheet.

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'U:\Files\Maintenance File.xls'!SKUinfo,16,FALSE)"

ActiveCell.FormulaR1C1 = _
"=INDEX('U:\Files\[Recipe File.xlsx]Fresh 2800'!Fresh2800,2,6)*(INDEX(SchedInfo,MATCH(RC[-3],SKULookup,0),6))"

Any help would be appreciated, and thank you for all your help in advance.

mr_plum · Accepted Answer · 2014-04-23 17:24:50Z

2

The answer to this Microsoft forum post describes what you want. Use the Application object:

ans = Application.VLookup(arg1, MyRange, arg3, arg4)

answered Apr 23, 2014 at 17:24

mr_plum

2,4372 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using a function to return a value in VBA without using the worksheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related