I just tried searching for some help and think I found part of th
problem but, being the novice I am was hoping to get more specific hel
with my program.

My objective is to run the excel match function against 2 files whic
will change weekly.

I am using the workbooks.open filename command to prompt for bot
files. I am stumbling on using the match function, as it seems to no
allow me to use a variable for the file or sheet names.

my code is this so far:

public sub getgoals()

dim wbgoals as workbook
dim wbneworders as workbook
dim lastrowgoals as integer
dim lastcolgoals as integer
dim lastrowneworders as integer
dim lastcolneworders as integer
dim acctcol as integer
dim acctref as integer
dim newcol as integer

goalwb = application _
.getopenfilename(\"select goals file - excel (*.xls), *.xls\")


if goalwb < false then
workbooks.open filename:=goalwb
set wbgoals = activeworkbook

orderswb = application _
.getopenfilename(\"select order file - excel (*.xls), *.xls\")

if orderswb < false then
workbooks.open filename:=orderswb
set wborders = activeworkbook

wbgoals.activate

lastcolgoals = cells(8, 256).end(xltoleft).column
lastrowgoals = cells(65536, 2).end(xlup).row
newcol = lastcolgoals + 1

cells(8, newcol).select
activecell.formular1c1 = _
\"=match(b8,'[west variance.xls]west'!(\"d3:d\" &
lastrowused),0)\"
activecell.select
selection.autofill destination:=activecell.range(\"a1:a\"
lastrowused), type:= _
xlfilldefault
activecell.range(\"a1:a\" & lastrowused).copy
selection.pastespecial paste:=xlpastevaluesandnumberformats
operation:= _
xlnone, skipblanks:=false, transpose:=false

.....
.........
............

Based on the browsing I have done here so far, I am guessing I need t
use the application.match fuction, not match itself... but, wil
that allow me to set variables for the file name & worksheet name.. ?

this:
ActiveCell.FormulaR1C1 = _
"=MATCH(b8,'[WEST VARIANCE.xls]WEST'!("d3:d" &
lastrowused),0)"

would end up looking something like this: ??

activecell.formular1c1 = _
application.match(b8,wborders,worksheet,"d3:d" & lastrowused,0)



Thanks for your time


Nanc

--
nanc
-----------------------------------------------------------------------
nance's Profile: http://www.excelforum.com/member.php...fo&userid=2999
View this thread: http://www.excelforum.com/showthread.php?threadid=49686