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

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

Dave -

thank you for a quick and detailed response.

I will be working with your code in the next day to see how it
functions and what changes I need to make, but wanted to acknowledge
your response.

Your style of coding is different than what I have been doing. Being
self-taught I still stumble with objects methods etc. I get the
'gist' of your style, however, but will need to study it.

Re the variables- yes, after I posted I realized I had very messy code.
Result of long hours of work and frustration. I was expecting a
response of 'what the heck are you doing'.

Again, thank you and I will try to respond with an update soon.


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