PDA

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