If you want to put a formula in a cell, then you want to use =match(). If you
want to use the worksheet function inside your code, then you could use
application.match.

I try to let excel do the heavy lifting for me. I don't want to keep track of
sheet names or workbook names. In fact, I'll often use the whole column and not
even try to find the last used cell in a column. (With nice distinct headers,
it's often not worth the time/effort.)

And watch your variables. Sometimes you used lastrowgoals and other times
lastrowused.

This kind of thing (lightly tested) might help:

Option Explicit
Public Sub getgoals()

'application.match(b8,OrdersWB,worksheet,"d3:d" & lastrowused,0)


Dim wbGoals As Workbook
Dim wbGoalsName As Variant
Dim OrdersWB As Workbook
Dim OrdersWBName As Variant

Dim RngToMatch As Range

Dim LastColGoals As Long
Dim LastRowGoals As Long
Dim NewCol As Long

wbGoalsName _
= Application.GetOpenFilename("select goals file - excel (*.xls), *.xls")
If wbGoalsName = False Then
Exit Sub
End If

OrdersWBName _
= Application.GetOpenFilename("select order file - excel (*.xls), *.xls")
If OrdersWBName = False Then
Exit Sub
End If

Set wbGoals = Workbooks.Open(Filename:=wbGoalsName)

Set OrdersWB = Workbooks.Open(Filename:=OrdersWBName)

With OrdersWB.Worksheets("West")
Set RngToMatch = .Range("d3", .Cells(.Rows.Count, "D").End(xlUp))
End With

With wbGoals.Worksheets("sheet1")
LastColGoals = .Cells(8, .Columns.Count).End(xlToLeft).Column
LastRowGoals = .Cells(.Rows.Count, 2).End(xlUp).Row
NewCol = LastColGoals + 1

.Cells(8, NewCol).Formula = _
"=match(b8," & RngToMatch.Address(external:=True) & ",0)"

.Cells(8, NewCol).AutoFill _
Destination:=.Range(.Cells(8, NewCol), .Cells(LastRowGoals, 8)), _
Type:=xlFillDefault

With .Range(.Cells(8, NewCol), .Cells(LastRowGoals, 8))
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End With
End Sub



nance wrote:

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

--
nance
------------------------------------------------------------------------
nance's Profile: http://www.excelforum.com/member.php...o&userid=29995
View this thread: http://www.excelforum.com/showthread...hreadid=496863

--

Dave Peterson