View Full Version : Solved: match 2 columns and display 3rd column value
Pats83
07-03-2013, 08:46 AM
Hi There,
I have 2 sheets Mpro and Employee each with 2 columns
Employee
MPRO | Trips
15 | 1
15 | 1
54 | 3
Mpro
MPRO | Trips
15 |
54 |
I require the code in VBA as an excel formula was slowing the program down.
What I need is where the Mpro.Mpro column = the Employee.Mpro column then the Mpro.Trips column should = Employee.Trips
The trips column for each MPRO will always be the same.
Thanks.
Pats83
07-03-2013, 09:29 AM
' Count the number of active rows in Excel
Dim Count As Long
Count = (Range(ActiveWorkbook.Sheets("Employee").Range("A3"), ActiveWorkbook.Sheets("Employee").Range("A65535").End(xlUp)).Count) + 2
' Loop through each row and put the total trips to UW from QC in the 11th column
Dim i As Integer, TripCount As Double
For i = 2 To Count
TripCount = Application.WorksheetFunction.VLookup(Sheets("Mpro").Range("A$2"), Sheets("Employee").Range("B$3:B$" & Count), 10)
'CountIfs(Sheets("Employee").Range("B$3:B$" & Count), Sheets("Employee").Range("B" & i), Sheets("Employee").Range("G$3:G$" & Count), "=QC", Sheets("Employee").Range("H$3:H$" & Count), "=UW")
Workbooks(WBName).Worksheets(WSName).Cells(i, 8) = TripCount
Next
This is what I am trying so far
A VLookup but I keep getting the error "Unable to get the VLookup property of the WorkSHeetFunction class"
Is there any way to do this if there are multiple instances of this in the one column.
Pats83
07-03-2013, 01:07 PM
Dim EmpRecCount As Long, StartDate As Long, EndDate As Long, DaysInProgress As Integer
Count = (Range(ActiveWorkbook.Sheets("Mpro").Range("A2"), ActiveWorkbook.Sheets("Mpro").Range("A65535").End(xlUp)).Count) + 1
EmpRecCount = (Range(ActiveWorkbook.Sheets("Employee").Range("A3"), ActiveWorkbook.Sheets("Employee").Range("A65535").End(xlUp)).Count) + 2
' Loop through each row and put the total trips from Employee to Mpro
Dim i As Integer
For i = 2 To Count
TripCount = Application.WorksheetFunction.Index(Sheets("Employee").Range("$A$3:$J$" & EmpRecCount), Application.WorksheetFunction.Match(Sheets("Mpro").Range("A" & i), Sheets("Employee").Range("$B$3:$B$" & EmpRecCount), 0), 10)
Workbooks(WBName).Worksheets(WSName).Cells(i, 8) = TripCount
Solved it with the vlookup
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.