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!

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

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

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?

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?

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?

On Fri, 4 Aug 2006 14:29:01 -0700, Shannon <[email protected]>
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

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 <[email protected]>
> 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
>