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

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

I am using the workbooks.open filename command to prompt for both files. I am stumbling on using the match function, as it seems to not 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 to use the application.match fuction, not match itself... but, will 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


Nance