excel - Can I create a variable that saves the result of the formula? -

here example code set range equal string variable, , save file name based on formulas result.

dim vformula3 string  'set formula string variable vformula3 = "=index($a$7:$az$7,match(" & chr(34) & "voyage number" & chr(34) & ",$a$6:$az$6,0))"   'then put formula cell range 'set sheet range here if needed range("a3") = vformula3  'save code goes here     'blah blah blah save files. 

is possible instead rewrite formula set variable (eg vformula1) instead result of formula?

is there dim vformula formula approach take?

as mentioned in comments of last question, application.evaluate method. alternately, more direct method might worksheetfunction object.

you missing parent worksheet references in formula; not idea when using method of evaluating formula within vba parent references may lost due roving activesheet property.

dim vformula3 string, vresult variant  vformula3 = "index('sheet one'!$a$7:$az$7, " & _             "match(""voyage number"", 'sheet one'!$a$6:$az$6, 0))" debug.print application.evaluate(vformula3) vresult = application.evaluate(vformula3) debug.print vresult  'hlookup better formula worksheets("sheet one")     debug.print application.hlookup("voyage number", .range("a6:az7"), 2, false)     debug.print worksheetfunction.hlookup("voyage number", .range("a6:az7"), 2, false)     vresult = application.hlookup("voyage number", .range("a6:az7"), 2, false)     debug.print vresult end 

note quotes within quoted string need doubled up.

i've offered alternate formula using hlookup function. seems better suited trying do.

the debug.print results go vbe's immediate window (tap [ctrl]+g show).