Assign values from attributes to names in Excel

Question

This is the table to show how the names are written in the fort column.

I need to identify the column/attribute's values (the 5 values in italics) and assign it to that name.

I made a command-click button named Values that I want to output the name of the row/faculty reviewed and the columns that were indicated with an "XXXXX". I want to go through each cell and if an XXXXX is present, print the column's value next to the name of the row's faculty.

This is the code I was previously using:

Private Sub loop_through_table()

  Dim cell As Range ' loop through cells, check for names
  Dim col As Range 'loop through Columns, check for XXXXX
  Dim lr As Long ' last active row
  Dim ws As Worksheet: Set ws = Sheets("Sheet4")
  Dim res As String ' will store result
  Dim i As Long 'for loop counter
  Dim maxL As Long ' rightmost last active column


  lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  maxL = ws.Cells(4, Columns.Count).End(xlToLeft).Column

  For i = 5 To lr - 1 ' skip the initials, hence Step 2
    res = "" ' reset of result
    Set cell = ws.Cells(i, 1)
    res = cell + ":"

    For Each col In ws.Range(Cells(i + 1, 2), Cells(i + 1, maxL))
        If col = "XXXXX" Then 'if we found xxxxx
            If Right(res, 1) = ":" Then 'we don't want comma on first argument
                res = res + " " + ws.Cells(3, col.Column)
            Else
                res = res + ", " + ws.Cells(3, col.Column)
            End If
        End If
    Next col

    ws.Cells(i, maxL + 3) = res 'print result to rightmost column + 2

  Next i

End Sub

So the output would be:

 First Last: Physical, CyberSecurity

 First2 Last2: Mathematical, Artificial Intelligence

and so on...

Could I also print by specific faculty instead of printing every row?

There are 4 attributes but the photo only includes 2 (foundations and technologies) but each attribute has numerous values/keywords (cyber-security, mathematical, etc.). There are 22 keywords/phrases/values in this sheet but I will have a different amount for each sheet I am creating. — Michael, Aug 26 '18 at 16:38
What have you tried? You could have the search terms (attributes) in an array. Loop that array using Range.Find method to locate the current array value, e.g. foundations, in the specified row Rows(1).Find(arr(i))... then you offset 1 row down, and loop until 1 before the column where the next search term was found. Rinse and repeat except for last item where you stop looping when hit an empty cell perhaps? — QHarr, Aug 26 '18 at 18:00
I updated my picture to be clearer in what I have now. I will explain what I want to create. For example, if I made my desired table and then sent it out to the people listed in the rows, the person in row 6 (S. Acharya) could then modify the table to check/color/choose the column values their studies which relate to the columns, Physical(B6), Computational(D6), and Cyber-Security(E6) in this example. I want the chosen cells to be tied\linked to the faculty member so when the faculty name is an input the chosen values are written out as the output ("Physical, Computational, Cyber-Security") — Michael, Aug 26 '18 at 18:59
We can help you with code you have written but we need to see where you are stuck. It can be difficult if you are new to VBA - in which case, break the problem down into stages and try to code the first stage. Update your question to reflect the first state and include code and where stuck... or with which ever stage you get stuck at. — QHarr, Aug 26 '18 at 19:18

score 0 · Answer 1 · answered Aug 27 '18 at 09:48

0

This will work, presuming your data starts at A5 and always maintains the same format

Private Sub loop_through_table()

  Dim cell As Range ' loop through cells, check for names
  Dim col As Range 'loop through Columns, check for XXXXX
  Dim lr As Long ' last active row
  Dim ws As Worksheet: Set ws = Sheets("Your Sheet Name") ' << CHANGE ME!!
  Dim res As String ' will store result
  Dim i As Long 'for loop counter
  Dim maxL As Long ' rightmost last active column


  lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  maxL = ws.Cells(4, Columns.Count).End(xlToLeft).Column

  For i = 5 To lr - 1 Step 2 ' skip the initials, hence Step 2
    res = "" ' reset of result
    Set cell = ws.Cells(i, 1)
    res = cell + " - " + cell.Offset(1, 0) + ":" 'add last name + initialis

    For Each col In ws.Range(Cells(i + 1, 2), Cells(i + 1, maxL))
        If col = "XXXXX" Then 'if we found xxxxx
            If Right(res, 1) = ":" Then 'we don't want comma on first argument
                res = res + " " + ws.Cells(3, col.Column)
            Else
                res = res + ", " + ws.Cells(3, col.Column)
            End If
        End If
    Next col

    ws.Cells(i, maxL + 2) = res 'print result to rightmost column + 2

  Next i

End Sub

I ran a test (with slightly modified data / ranges in code) and it works as expected.

answered Aug 27 '18 at 09:48

Samuel Hulla

6,617
7
36
70

Since I have multiple same last names I decided to write each name in full in the rows. So your example would then have row 5 say L. King and row 6 would say C. Chaplin. So it should be easier but how does that change the code? and can I print it below the table, not next to it? – Michael Aug 27 '18 at 17:00
I changed my format and updated what I need assistance with. – Michael Aug 30 '18 at 13:36
What edits did you make to the code? Because I can't get your example to print the chosen values. It only prints "King - L.:" – Michael Aug 30 '18 at 14:16
@Michael Uhh perhaps it is a tiny bit misleading. The picture of the result is for my own (changed) data. The code should be fitted for the format of the data you proposed in the question – Samuel Hulla Aug 30 '18 at 14:20
I was able to get the correct modification so thank you! Another question would be instead of "XXXXX" we had a field where a string needed to be inputted and displayed alongside the other values. This could be '123456' or 'cow' how can I modify my code to include those individually/original inputs in my outputted string? – Michael Aug 30 '18 at 16:02

Assign values from attributes to names in Excel

1 Answers1