VBA Formula Help

[GTyson2]

The Following is a code I wrote to try and Lookup information from one
workbook and copy and paste that exact information into another workbook. But
I'm having issues with my Formula for Z. I'm trying to use the Match funtion
in excel since it gives you the row number, and my column is going to be
static. Can someone help me out here?

Sub Match()
x = 4
y = 2
b = 14


Dim fName As String
fName = Application.GetOpenFilename()
Workbooks.Open Filename:=fName
Sheets("Accounting_Teams").Select
Sheets("Accounting_Teams").Copy Before:=Workbooks( _
"Tracker-Test.xls").Sheets(1)


Do While Cells(x, y).Value <> ""
z = Formula = "=Match(Cells(X,
Y),Sheets(""Accounting_Teams"").Columns(""C:C""),0)"
Sheets("Accounting_Teams").Select
Cells(z, 20).Select
Selection.Copy
Sheets("Sheet1").Select
Cells(x, b).Select
ActiveSheet.Paste

x = x + 1

Loop

Applications.CutCopyMode = False
Sheets("Accounting_Teams").Select
ActiveWindow.SelectedSheets.Delete

End Sub

 

[GTyson2]

Sorry that one was old, Replace the Z code with this
z = Application.WorksheetFunction.Match(Cells(X, Y),
Sheets("Accounting_Teams").Columns("C:C"), 0)
That's what I have now and it's not working

 

[Dave Peterson]

I'm confused by what you're doing, but maybe I have it right????

It looks like you're opening a workbook, copying a sheet to an already open
workbook.

Then cycling through the cells in column B (B4 to the last used cell in column
B) in that newly pasted sheet.

You're looking for a match for the value in B4 (then B5, B6, ...) in column C of
the same sheet. If there is a match, then copy the value in column 14 on that
matching row into column 20 of the cell that we're looping through.

Am I close?????

If yes, then this compiled, but it's untested.

Option Explicit
Sub myMatch()

Dim res As Variant
Dim fName As Variant 'could be boolean
Dim TempWkbk As Workbook
Dim TrackWkbk As Workbook
Dim AcctWks As Worksheet
Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range

'Thisworkbook or Activeworkbook???
Set TrackWkbk = Workbooks("Tracker-test.xls")

fName = Application.GetOpenFilename()
If fName = False Then
'user hit cancel
Exit Sub
End If

Set TempWkbk = Workbooks.Open(Filename:=fName, ReadOnly:=True)

TempWkbk.Worksheets("Accounting_Teams").Copy _
Before:=TrackWkbk.Sheets(1)

Set AcctWks = TrackWkbk.Sheets(1) 'the newly pasted sheet

TempWkbk.Close savechanges:=False

With AcctWks
Set myRng = .Range("B4", .Cells(.Rows.Count, "B").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = "" Then
'do nothing
Else
res = Application.Match(myCell.Value, .Range("C:C"), 0)

If IsError(res) Then
'no match, ignore
Else
.Cells(res, 20).Copy _
Destination:=.Cells(myCell.Row, 14)
End If
End If
Next myCell
End With

End Sub

 

[GTyson2]

You have the Idea right. I just try to input it, but it's not copying the
cells. It does open the other workbook and copy the Sheet for me, then Closes
that workbook wich is great. But as I said it's no longer pasting the cells.

 

[Dave Peterson]

You're gonna have to explain in plain words what you want it to do--including
details.

 

[GTyson2]

I have two workbooks. The Frist workbook I have (in the macro is named
"test-tracker.xls") has a list of all our vendors, and the Vendors are split,
so one vendor will appear more than once. The second workbook (This workbooks
name changes all the time) has the list of the vendors, and when the vendors
were paid.
What I want it to do, is from the Frist workbook, match the vendors unique
ID to the second book and copy when the vendor was paid from the Second
workbook to the First workbook. I need the copy and paste function so it will
copy the cell formatting with it.

So in other words.

I'm matching Vendor ID's in B:B on Test-Tracker.xls to the Vendor ID's in
C:C on the other Workbook.xls. Once the ID's are matched, I'm trying to pull
the information from the check column (within Workbook.xls) wich is column T
and put that information to the check column in Test-Tracker.xls wich is
column N.

And due to Vendors being listed more than once in Test-Tracker.xls I can't
simply sort and then copy and paste.

Thanks for trying to help. Greatly Appreciated

 

[Dave Peterson]

You don't really work with data in a workbook--you (or at least I) work with
data on a worksheet.

Since your code is in Test-Tracker.xls, we can use ThisWorkbook.

#1. What worksheet holds the data in column B of that test-tracker.xls
worksheet?
#2. Where does the data start (B1 or B2 or B4)????

Maybe...

Option Explicit
Sub myMatch()

Dim res As Variant
Dim fName As Variant
Dim TempWkbk As Workbook
Dim TrackWks As Worksheet
Dim AcctWks As Worksheet
Dim AcctRng As Range
Dim myCell As Range
Dim myRng As Range

Set TrackWks = ThisWorkbook.Worksheets("SomeSheetNameHere")

fName = Application.GetOpenFilename()
If fName = False Then
'user hit cancel
Exit Sub
End If

Set TempWkbk = Workbooks.Open(Filename:=fName, ReadOnly:=True)

TempWkbk.Worksheets("Accounting_Teams").Copy _
Before:=ThisWorkbook.Sheets(1)

Set AcctWks = ThisWorkbook.Sheets(1) 'the newly pasted sheet

Set AcctRng = AcctWks.Range("C:C")

TempWkbk.Close savechanges:=False

With TrackWks
Set myRng = .Range("B4", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
If myCell.Value = "" Then
'do nothing
Else
res = Application.Match(myCell.Value, AcctRng, 0)

If IsError(res) Then
'no match, ignore
Else
AcctWks.Cells(res, "T").Copy _
Destination:=TrackWks.Cells(myCell.Row, "N")
End If
End If
Next myCell

'do you want to keep that accounting worksheet?
Application.DisplayAlerts = False
AcctWks.Delete
Application.DisplayAlerts = True

End Sub