VBA concatenate all cells that meet a condition (vlookup value)

[Kamolga]

Hi,

Imagine I have names of employees in column A and the name of the line they are working on (line 1, 2 or 3) in column B.

What I try to have in C1 where the value is Line 1 is a comment with the name of all employees working on line 1 that day.
My issue is that I can have from 1 to 40 employees, so I do not know how to recall the employee identified as working in line 1

Dim EmployeeCell as Range

     For each EmployeeCell in Range ("B:B")
                         If EmployeeCell.offset(0,-1).value="Line 1"
                             [COLOR="#FF0000"]'Is there some way to add the content of that cell to an existing text (=string)?[/COLOR]         
                         End if
     Next EmployeeCell

I should then be abble to use that here I believe

    Range("C1").AddComment
    Range("C1").Comment.Visible = False

[COLOR="#008000"]'note that Chr(10) is just an enter between the cells and ideally I would have 1.First person enter 2.Second person enter 3.Third person so that we know in a sec how many people are working on the line. If too difficul just a string with all names would already be fine[/COLOR]
    Range("C1").Comment.Text Text:="??? & Chr(10) & ??? & Chr(10) & ??? & Chr(10) & ??? & Chr(10) & ""

Any suggestion for any part of the code welcome.

Thanks in advance.

 

[L. Howard]

Hi Kamolga,

How about posting a link to a sample work book (exact formatting, columns, rows etc.) along with what you want to happen and where you want it to happen. Specify cells columns and rows in your explanation. You can use one of the link posting utilities to do so. I use Drop Box, but there are others.

Howard

 

[Kamolga]

Hi Howard,

I try to get the comments of this sample https://onedrive.live.com/redir?page=view&resid=80A6697BD9B086F9!2092&authkey=!AAxSGKF7766dVgw, the one you have in C1. In this simplified sample, there are 4 people in Line 1, but could be 10 if another product had been planned. Hope it makes sense.

NB:You might have to download the file if you want to see the comments. The comment in C1 says

1.Mr John Smith
2.Mr Columbo Jr
3.Mrs Diane
4.Mrs Jessica

Link dropbox as well https://www.dropbox.com/s/o9qc083g1h7b1y5/VBA%20-%20Comment.xlsm?dl=0

 

[L. Howard]

Try this workbook example, using the code posted below.

https://www.dropbox.com/s/almi7zx6vvodgcw/Comment Text extracted from a column list DBx.xlsm?dl=0



I added a "First Entry" in the comment text box of how many entries for the selected Line. Followed by a list of those names.

(I added several more names for test purposes, so there are many duplicates, understanding the real list is probably a unique list.)


Here is the Change_Event macro that looks to do what you want.

With thanks to Claus of MS Public for some great help.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "C1" Or Target.Count > 1 Then Exit Sub

Dim LRow As Long, i As Long, n As Long, myCnt As Long
Dim varData As Variant, varTmp() As Variant
Dim strComment As String

LRow = Cells(Rows.Count, 1).End(xlUp).Row
varData = Range("A2:B" & LRow)
myCnt = Application.CountIf(Range("B2:B" & LRow), Target)

ReDim Preserve varTmp(myCnt - 1)

For i = LBound(varData) To UBound(varData)
    If varData(i, 2) = Target Then
        varTmp(n) = varData(i, 1)
        n = n + 1
    End If
Next

strComment = n & " Entries" & Chr(10) & Join(varTmp, Chr(10))

With Range("C1")
    .ClearComments
    .AddComment strComment
    .Comment.Shape.TextFrame.AutoSize = True
End With

End Sub

 

[Rick Rothstein]

Hi,

Imagine I have names of employees in column A and the name of the line they are working on (line 1, 2 or 3) in column B.

What I try to have in C1 where the value is Line 1 is a comment with the name of all employees working on line 1 that day.
My issue is that I can have from 1 to 40 employees, so I do not know how to recall the employee identified as working in line 1

You might be able to make use of the function I posted in my mini-blog here to do that...

LookUp Value and Concatenate All Found Results

 

[Kamolga]

Try this workbook example, using the code posted below.

https://www.dropbox.com/s/almi7zx6vvodgcw/Comment Text extracted from a column list DBx.xlsm?dl=0



I added a "First Entry" in the comment text box of how many entries for the selected Line. Followed by a list of those names.

(I added several more names for test purposes, so there are many duplicates, understanding the real list is probably a unique list.)


Here is the Change_Event macro that looks to do what you want.

With thanks to Claus of MS Public for some great help.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "C1" Or Target.Count > 1 Then Exit Sub

Dim LRow As Long, i As Long, n As Long, myCnt As Long
Dim varData As Variant, varTmp() As Variant
Dim strComment As String

LRow = Cells(Rows.Count, 1).End(xlUp).Row
varData = Range("A2:B" & LRow)
myCnt = Application.CountIf(Range("B2:B" & LRow), Target)

ReDim Preserve varTmp(myCnt - 1)

For i = LBound(varData) To UBound(varData)
    If varData(i, 2) = Target Then
        varTmp(n) = varData(i, 1)
        n = n + 1
    End If
Next

strComment = n & " Entries" & Chr(10) & Join(varTmp, Chr(10))

With Range("C1")
    .ClearComments
    .AddComment strComment
    .Comment.Shape.TextFrame.AutoSize = True
End With

End Sub

Thank you very very much indeed Howard (and Claus), I managed to adapt the code to my final needs: had basically a weekly Schedule x2 shift so C1 became a for each cell in a range (with name of the lines in offset) and added a if n=0 goto noname after clearcomments as I would have a 0 entry with the last name of the last comment. I was not a big fan of comments in Excel but here it gives planner a huge amount of information to take décisions at the right place without polluting the production planning.

In addition I will keep the macro as sample as this way of coding (variant, redim preserve,etc.)has much more potential to me

 

[Kamolga]

You might be able to make use of the function I posted in my mini-blog here to do that...

LookUp Value and Concatenate All Found Results

Thank you Rick, I keep the UDF in my recorded UDFs as this not the first and last time I meet a concatenate on condition issue. I know how to use UDFs as formula but not how to use them in VBA (yet); in essence I could use a blank cell and recall its value in the macro but I will study the approach in a couple of days.