Need code

S

Scottmk

Hi,

I have a workbook that ranks securities on a point system. I also have
a workbook that lists each security and who holds it. That workbook
has the following code which enables the user to type in the ticker and
it returns the person(s) name(s) who have it. I was hoping that I could
use a code in the first workbook that selects the worst half of the
securites and runs them through my other workbook and creates a report
of clients names and bad securites.

The code in the second workbook:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v(1) As Variant
Dim lRow As Long

If Target.Address = "$G$4" Then
Application.EnableEvents = False
v(1) = Target.Address
'get next free column in row 5
lRow = Cells(Rows.Count, "h").End(xlUp).Row
' clear used cell row 5 to ??? column H
If lRow > 5 Then
Cells(5, "h").Resize(lRow - 4).ClearContents
Else
Cells(5, "h").ClearContents
End If
' find & list all matches
Do
v(0) = Cells.Find(Target, after:=Range(v(1))).Address
If v(0) = Target.Address Or v(0) = v(1) Then
Exit Do
End If
lRow = Cells(Rows.Count, "h").End(xlUp).Row + 1
If lRow < 5 Then
lRow = 5
End If
Cells(lRow, "h").Value = Range(v(0)).Offset(, 1).Value
v(1) = v(0)
Loop
End If
Application.EnableEvents = True
End Sub

I guess I was wondering if this were possible, and then I could provide
more details if someone were willing to help. Thank you
 
S

Scottmk

Here is a more "wordy" explanation of what I am trying to do:
After I run the sort by decending order in my first workbook, I need t
select the worst half of ticker symbols in a column. Then, I need t
open another workbook (the one with the previous code). Then, one b
one, run each ticker symbol through my second workbook. Let me explai
more what this second workbook does: If I type a ticker symbol into
cell and hit enter --> underneath it spits out all client names tha
have it. Then, if I type in another ticker into the same cell, it wil
erase and give me new output. So, somehow I need to copy and past
inbetween inputs the ticker and the output. This would be the report.
Thank
 
Top