Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Formula Help
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 Befo=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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Formula Help
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 "GTyson2" wrote: 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 Befo=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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Formula Help
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 _ Befo=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 wrote: 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 "GTyson2" wrote: 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 Befo=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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Formula Help
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" wrote: 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 _ Befo=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 wrote: 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 "GTyson2" wrote: 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 Befo=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 -- Dave Peterson . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Formula Help
You're gonna have to explain in plain words what you want it to do--including
details. GTyson2 wrote: 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" wrote: 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 _ Befo=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 wrote: 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 "GTyson2" wrote: 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 Befo=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 -- Dave Peterson . -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Formula Help
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" wrote: You're gonna have to explain in plain words what you want it to do--including details. GTyson2 wrote: 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" wrote: 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 _ Befo=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 wrote: 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 "GTyson2" wrote: 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 Befo=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 -- Dave Peterson . -- Dave Peterson . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Formula Help
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 _ Befo=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 GTyson2 wrote: 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" wrote: You're gonna have to explain in plain words what you want it to do--including details. GTyson2 wrote: 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" wrote: 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 _ Befo=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 wrote: 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 "GTyson2" wrote: 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 Befo=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 -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|