Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match data to find intersection
Can anyone help? In SheetA I have a date in ColA, name in ColB, hours in
ColC. In SheetB I have a table of dates and names. I need to find the intersection in this table of the date and name from SheetA and place the hours in that cell. I have a list of names in SheetA so I need to loop through that checking for an intersection in SheetB of name and date. If there is no intersection (the name doesn't exist in SheetB) I need to add a new row for that name. I've tried using match, intersection, and index but can't seem to get anything to work just right! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match data to find intersection
This is the code I've done to accomplish this. I actually tried it out using
two sheets in the same workbook and got it working perfectly. Now that I've moved it to two seperate workbooks I'm getting an error that I'm sure has to do with the way I'm calling a workbook as a variable. Any advice? Private Sub butWeeklyLabor_Click() Dim WLH As Variant Dim ws1 As Worksheet Dim ws2 As Worksheet Dim dat As Range Dim col As Long Dim num As Range Dim rw As Long Dim hours As Range Dim cell As Range Dim Lr3 As Long Dim IntR As Long Dim Lr4 As Long Dim wb As Workbook ChDir "C:\DOCUMENTS AND SETTINGS\sbauer\Desktop\Burney" WLH = Application.GetOpenFilename() If WLH = False Then End Else Set wb = Workbooks.Open(WLH) End If Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = Workbooks(wb).Worksheets("Sheet1") Lr3 = LastRow(ws1) Lr4 = LastRow(ws2) - 2 For i = 8 To Lr3 Set dat = ws1.Cells(i, 1) Set num = ws1.Cells(i, 2) 'Find column that matches date col = ws2.Rows(4).Find(dat).Column 'Find row that matches employee number rw = ws2.Columns(1).Find(num).Row Set hours = ws1.Cells(i, 6) 'Put hours in intersection cell ws2.Range("A1:I" & Lr4).Cells(rw, col).Value = hours Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Found Solution
As I suspected, the problem was in the syntax. Here is what is working for me:
Private Sub butWeeklyLabor_Click() Dim WLH As Variant Dim ws1 As Worksheet Dim ws2 As Worksheet Dim dat As Range Dim col As Long Dim num As Range Dim rw As Long Dim hours As Range Dim cell As Range Dim Lr3 As Long Dim IntR As Long Dim Lr4 As Long Dim wb As Workbook ChDir "C:\DOCUMENTS AND SETTINGS\sbauer\Desktop\Burney" WLH = Application.GetOpenFilename() If WLH = False Then End Else Set wb = Workbooks.Open(WLH) End If Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = wb.Worksheets("Sheet1") Lr3 = LastRow(ws1) Lr4 = LastRow(ws2) - 2 For i = 8 To Lr3 Set dat = ws1.Cells(i, 1) Set num = ws1.Cells(i, 2) 'Find column that matches date col = ws2.Rows(4).Find(dat).Column 'Find row that matches employee number rw = ws2.Columns(1).Find(num).Row Set hours = ws1.Cells(i, 6) 'Put hours in intersection cell ws2.Range("A1:I" & Lr4).Cells(rw, col).Value = hours Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Problem
I thought I had this solved but apparently when rw searches for a number it
matches through the number of digits of num. Example: num is 20, rw matches to 2025 because the first two digits are 20. Any ideas how I can get it to examine the whole cell before settling on a match? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Problem
On Fri, 4 Aug 2006 14:29:01 -0700, Shannon
wrote: I thought I had this solved but apparently when rw searches for a number it matches through the number of digits of num. Example: num is 20, rw matches to 2025 because the first two digits are 20. Any ideas how I can get it to examine the whole cell before settling on a match? This came through as a separate message not attached to any thread. So it's hard to tell what you are talking about. Is this a feature of discussions.microsoft.com? If so, it will limit the responses you might get. --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Problem
I don't know why there is a problem. I'm seeing all my posts in the right
thread. But the code I was referring to is below. The problem I'm having is frankly very strange. The formula for variable col was working perfectly when I stopped working on this project several days ago, but now it seems to be matching things incorrectly. Example: when dat=8/2/06, col is matching to 7/28/06 without creating an error (it was previously returning an error and then going to Err2). Similarly, when num=20, rw is matching to 2003. Any help would be greatly appreciated. Private Sub butWeeklyLabor_Click() Dim WLH As Variant Dim ws1 As Worksheet Dim ws2 As Worksheet Dim dat As Range Dim col As Long Dim num As Range Dim rw As Long Dim hours As Range Dim cell As Range Dim Lr3 As Long Dim IntR As Long Dim Lr4 As Long Dim wb As Workbook ChDir "C:\DOCUMENTS AND SETTINGS\sbauer\Desktop\Burney" WLH = Application.GetOpenFilename() If WLH = False Then End Else Set wb = Workbooks.Open(WLH) End If Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = wb.Worksheets("Sheet1") Lr3 = LastRow(ws1) Lr4 = LastRow(ws2) - 2 For i = 8 To Lr3 Set dat = ws1.Cells(i, 1) Set num = ws1.Cells(i, 2) 'Find column that matches date col = ws2.Rows(4).Find(dat).Column On Error GoTo Err1 'Find row that matches employee number rw = ws2.Columns(1).Find(num).Row Set hours = ws1.Cells(i, 6) 'Put hours in intersection cell ws2.Range("A1:I" & Lr4).Cells(rw, col).Value = hours Next Exit Sub Err1: MsgBox dat & " is not in this file" Resume Next End Sub "Ron Rosenfeld" wrote: On Fri, 4 Aug 2006 14:29:01 -0700, Shannon wrote: I thought I had this solved but apparently when rw searches for a number it matches through the number of digits of num. Example: num is 20, rw matches to 2025 because the first two digits are 20. Any ideas how I can get it to examine the whole cell before settling on a match? This came through as a separate message not attached to any thread. So it's hard to tell what you are talking about. Is this a feature of discussions.microsoft.com? If so, it will limit the responses you might get. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find a value at an intersection? | Excel Discussion (Misc queries) | |||
How to find intersection points between lines using Excel? | Excel Programming | |||
I NEED A FUNCTION TO FIND IF THERE IS AN INTERSECTION BETWEEN 2 DATE RANGES | Excel Worksheet Functions | |||
Find intersection point using lables? | Excel Programming | |||
Find Intersection | Excel Programming |