Sub to check data by client n add new symbols to client's list
Hi Max
Try the below and feedback
Sub Macro7()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rngTemp1 As Range, rngTemp2 As Range
Set ws1 = Worksheets("All")
Set ws2 = Worksheets("T")
For lngRow = 6 To ws1.Cells(Rows.Count, "A").End(xlUp).Row
If ws1.Range("A" & lngRow) = ws2.Range("C1") Then
Set rngTemp = ws2.Range("B19:B" & Cells(Rows.Count, "B").End(xlUp).Row)
If WorksheetFunction.CountIf(rngTemp, ws1.Range("B" & lngRow)) = 0 Then
Set rngTemp2 = ws2.Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
rngTemp2 = ws1.Range("B" & lngRow).Text
rngTemp2.Font.ColorIndex = 3: rngTemp2.Font.Bold = True
End If
End If
Next
End Sub
If this post helps click Yes
---------------
Jacob Skaria
"Max" wrote:
In sheet: All,
In A6:B6 down are ClientID/Symbol data, eg:
AA MT
AA JPM
BB HIG
AA PCS
BB GE
BB GE
BB AIG
etc
(there may be duplicate clientID/symbols listed, eg BB - GE above)
In another sheet: T,
C1 contains the clientID, eg: BB
In B19 down are client BB's existing symbols eg:
HIG
TXT
What I need is for a sub to go to All, gather all the unique symbols
associated with the clientID in T's C1 (ie: BB), then check these with the
symbols already listed in T (in B19 down) and add any new symbols into T's
col B (append below) in red/bold font
For the sample data above, I would expect to see GE & AIG written into T's
B21:B22 (the order for the 2 symbols is immaterial). Thanks
|