Looking up data from one place to another
 

I have a sheet that has a user in cells D3 to H3 and a Account ID in cell B10
to B20. I have another sheet(sheet2) that list all the Account ID's in
Column A, all the Users In column B and the 'Calls needed' in column C. I
want to update the intersecting cells with the Calls needed on sheet based on
the intersection of user ID and Account ID. Can someone help?

Looking up data from one place to another
 

Sub DDD()
Dim Usr As Range, Acct As Range
Dim rng As Range, cell As Range
Dim res As Variant, res1 As Variant
Dim rw As Long, col As Long
With Worksheets("Sheet1")
Set Usr = .Range("D3:H3")
Set Acct = .Range("B10:B20")
End With
With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
For Each cell In rng
res = Application.Match(cell.Value, Acct, 0)
res1 = Application.Match(cell.Offset(0, 1).Value, Usr, 0)
If Not IsError(res) And Not IsError(res1) Then
rw = Acct(res).Row
col = Usr(1, res1).Column
Worksheets("Sheet1").Cells(rw, col).Value = _
cell.Offset(0, 2).Value
End If
Next
End Sub

--
Regards,
Tom Ogilvy




"Richard Davis" wrote in message
...
I have a sheet that has a user in cells D3 to H3 and a Account ID in cell
B10
to B20. I have another sheet(sheet2) that list all the Account ID's in
Column A, all the Users In column B and the 'Calls needed' in column C. I
want to update the intersecting cells with the Calls needed on sheet based
on
the intersection of user ID and Account ID. Can someone help?

Looking up data from one place to another
 

Thanks Tom,

I hope I understand this when I get to the point where I have to program
that part, if not.....I will send another message. Thanks for the prompt
response.

"Tom Ogilvy" wrote:

Sub DDD()
Dim Usr As Range, Acct As Range
Dim rng As Range, cell As Range
Dim res As Variant, res1 As Variant
Dim rw As Long, col As Long
With Worksheets("Sheet1")
Set Usr = .Range("D3:H3")
Set Acct = .Range("B10:B20")
End With
With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
For Each cell In rng
res = Application.Match(cell.Value, Acct, 0)
res1 = Application.Match(cell.Offset(0, 1).Value, Usr, 0)
If Not IsError(res) And Not IsError(res1) Then
rw = Acct(res).Row
col = Usr(1, res1).Column
Worksheets("Sheet1").Cells(rw, col).Value = _
cell.Offset(0, 2).Value
End If
Next
End Sub

--
Regards,
Tom Ogilvy




"Richard Davis" wrote in message
...
I have a sheet that has a user in cells D3 to H3 and a Account ID in cell
B10
to B20. I have another sheet(sheet2) that list all the Account ID's in
Column A, all the Users In column B and the 'Calls needed' in column C. I
want to update the intersecting cells with the Calls needed on sheet based
on
the intersection of user ID and Account ID. Can someone help?