Using match in vba.. with various files

[nance]

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

[Dave Peterson]

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

[nance]

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.