Using Functions in VBA to bring a value to a variable without using the worksheet

[Freshpetguy]

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

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, this one has me stumped

[snb]

How could we know the range of 'SKUinfo' ?

sub M_snb()
    MsgBox getobject("U:\Files\Maintenance File.xls").sheets("SKUinfo").columns(1).find(activecell.offset(,-1),,,1).offset(,15).value
end sub

[Freshpetguy]

let me see if I can help a little.
SKUinfo is just a named range, as are the others that are in the equations. I wouldn't think that would be an issue given they now have variables assigned to them. As I look at what you've written I see that you are bringing the answer back to a message box. Can I use a variable name instead...... For example: Batchname. and how would that change the getobject statement? I'm trying to dissect what you're doing in the statement so I understand, and thank you for your patience in explaining in advance.

[snb]

What is the result if you run/test the code ?

Sub M_snb() 
    MsgBox getobject("U:\Files\Maintenance File.xls").Range("SKUinfo").columns(1).find(activecell.offset(,-1),,,1).offset(,15).value 
End Sub

[Freshpetguy]

What is the result if you run/test the code ?

Sub M_snb() 
    MsgBox getobject("U:\Files\Maintenance File.xls").Range("SKUinfo").columns(1).find(activecell.offset(,-1),,,1).offset(,15).value 
End Sub

[Freshpetguy]

I am impressed! it did work, I had to fiddle with a couple of filenames and paths that I had changed so I didn't have original documentation on here, but it works, and that is fantastic! But I'm still wondering how to get this into a variable and not something that I have to pull from a message box.
You've done you're part, and now its time for me to learn a bit more. Thank you by the way, I saw the little extra reading note, and I will take a look at that and check out your practical applications here. Thank you for your help, it's a good start, and now I'm off to read a bit, per your suggestion.
Take care and thanks,

[Paul_Hossler]

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

Opt 1 - .Evaluate might work, but I'm not sure of the specific syntax for your strings. You could build your strings in pieces and check them out

Opt 2 -- If it were me, I'd consider a strictly (or at least mostly) a VBA solution and not emulate the worksheet formulas

Paul

[Freshpetguy]

Thank you Paul. The other option did work, but in the time that I spent making all of the sections work, the application was a bit to slow. I would say that I, like you, would prefer the execution of the formulas to run strictly VBA but it has been difficult to find something that will work. I'll give the evaluate method a try and play with the strings to get them working. Thank you for your thoughts and your efforts.
Steve

[snb]

sub M_snb()
   x=Getobject("U:\Files\Recipe File.xlsx").sheets("Fresh 2800").range("Fresh2800").cells(2,6)
   activecell.value=x*range("SchedInfo").cells(range("SKULookup").find(activecell.offset(-3),,,1).row,6) 
End Sub

[Freshpetguy]

sub M_snb()
   x=Getobject("U:\Files\Recipe File.xlsx").sheets("Fresh 2800").range("Fresh2800").cells(2,6)
   activecell.value=x*range("SchedInfo").cells(range("SKULookup").find(activecell.offset(-3),,,1).row,6) 
End Sub

[Freshpetguy]

Thank you for the equation. I thought I had it worked out and just ended up using the active formula. With that said, I'm going to give this a try and I'll follow up with how it goes.
Thank you again,
Steve