Complex Question. Index/Match then Copy/Paste to Summary Sheet

[ryguy7272]

I have a list of names and sales revenues associated with those names. I
have names in Column B of Sheet1 and Sales figures in Column C of Sheet1. I
am hoping to be able to find a way of entering an identifier, such as an “Xâ€
into Column D of Sheet1, then have Excel lookup the name, which is in Column
B on Sheet2. I think Index/Match would work. Now, that the Name in Column B
on Sheet1 is matched with the Name in Column B on Sheet2, find where the
value changes (i.e., the name of the director changes) and copy this array
plus one Column to the right (so Sheet2, Column B and Column C) because these
are the sales reps that report to the director. I’d like to take the result
and copy/paste to a Summary Sheet. Finally, the sales revenue is in Column C
of Sheet1. I would love to be able to copy this value to the Summary Sheet
too.

Pretty complicated, but I’m sure it can be done. I’ll be up late tonight,
working on the code for this. I’ll post back with some VBA, once I make a
little progress on this. I am just posting now to see if anyone here has
some generic code that may work in this situation. I know it’s a long shot,
but I’ve seem some AMAZING things here over the past couple of years.
Finally, if I can figure this out myself, I’ll post the results. I’m not
feeling very creative at this very moment though…

I would suspect, it would start like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("D")
Set t = Target
If Intersect(r, t) Is Nothing Then Exit Sub
If t.Value = "X" Then
Application.EnableEvents = False
'ALL CODE HERE...................................
Application.EnableEvents = True
End If
End Sub



Thanks,
Ryan---

 

[ryguy7272]

I tried this, mostly air-code:
Sub foo()
Dim i As Integer

LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Worksheets("Sheet1").Cells(i, 2) = Worksheets("Sheet2").Cells(i, 2)
Then
Worksheets("Sheet2").Cells(i, 2).Copy
Worksheets("SummarySheet").Cells(i, 2).Paste
Else
End If
Next i
End Sub

I suppose the macro fires, but nothings actually happens. What am I doing
wrong here? I would like to find the value in Sheet 1, Column B, that
matches each value in Sheet2, Column B, and copy paste these matches, as well
as two column to the right of each match, to a summary sheet.

Thanks,
Ryan---

 

[ryguy7272]

Please disregard this post. I am now thinking that I didn't communicate my
thoughts, or intentions, well at all. Please disregard. I will soon repost,
with a more clear, and more concise, description of the issue.

Thanks,
Ryan--

 

[ryguy7272]

Resolved!! When I sat down and REALLY thought about my needs
for this project, I was able to rework some things and eventually come up
with a solution. Everything is working now!! Thanks for the push!!

http://www.microsoft.com/office/com...5ccae1d0554&cat=&lang=en&cr=US&sloc=en-us&p=1

Thanks goes out to JLGWhiz!
Ryan--