VLookup Method for Comma Delimited Field

D

DoveArrow

I'm very new to working with Visual Basic in Excel, so I'm hoping
someone can help me out.

I found this neat little video tutorial on YouTube explaining how to
create a data validation field which allows users to store multiple
items in a single field, separated by a comma.


Using this code, I've created a spreadsheet called "Course Sections."
On this spreadsheet is a column called "Faculty Name." Each of the
fields in this column are tied to a data validation table stored on a
separate spreadsheet, called "Faculty."

Now here's where I'm running up against a wall. In addition to storing
the faculty person's name, the "Faculty" spreadsheet also lists a
faculty ID number for each person. I'd like to take that faculty ID
number and add it to a second column on the "Course Sections"
spreadsheet, so that it would look something like this:

Faculty Spreadsheet:
Faculty Name; Faculty ID
Mickey Mouse; 10111
Donald Duck; 10112
Uncle Scrooge; 10113
Launchpad McQuack; 10114

Course Sections Spreadsheet:
Section; Faculty; Faculty ID
ENG-101; Mickey Mouse; 10111
ENG-102; Donald Duck, Uncle Scrooge; 10112, 10113
ENG-103; Donald Duck, Launchpad McQuack, Mickey Mouse; 10112, 10114,
10111

Using the code from the tutorial as a guide (see below), I'm assuming
I would need to do something like the following:

1) Perform a lookup to find the Faculty ID on the "Faculty"
spreadsheet, based off the newVal string.
2) Store that Faculty ID in a string. (Let's call it newID.)
3) Append that ID number to the data currently stored in the "Course
Sections" spreadsheet.

Now steps 2 and 3 I don't think would be too hard. In fact, I imagine
they would look something like this.

Dim newID as String
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value & ", " & newID

The problem is, I don't even know how to begin coding step 1. Anyone
have any bright ideas?

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
 
D

Don Guillett

I'm very new to working with Visual Basic in Excel, so I'm hoping
someone can help me out.

I found this neat little video tutorial on YouTube explaining how to
create a data validation field which allows users to store multiple
items in a single field, separated by a comma.


Using this code, I've created a spreadsheet called "Course Sections."
On this spreadsheet is a column called "Faculty Name." Each of the
fields in this column are tied to a data validation table stored on a
separate spreadsheet, called "Faculty."

Now here's where I'm running up against a wall. In addition to storing
the faculty person's name, the "Faculty" spreadsheet also lists a
faculty ID number for each person. I'd like to take that faculty ID
number and add it to a second column on the "Course Sections"
spreadsheet, so that it would look something like this:

Faculty Spreadsheet:
Faculty Name; Faculty ID
Mickey Mouse; 10111
Donald Duck; 10112
Uncle Scrooge; 10113
Launchpad McQuack; 10114

Course Sections Spreadsheet:
Section; Faculty; Faculty ID
ENG-101; Mickey Mouse; 10111
ENG-102; Donald Duck, Uncle Scrooge; 10112, 10113
ENG-103; Donald Duck, Launchpad McQuack, Mickey Mouse; 10112, 10114,
10111

Using the code from the tutorial as a guide (see below), I'm assuming
I would need to do something like the following:

1) Perform a lookup to find the Faculty ID on the "Faculty"
spreadsheet, based off the newVal string.
2) Store that Faculty ID in a string. (Let's call it newID.)
3) Append that ID number to the data currently stored in the "Course
Sections" spreadsheet.

Now steps 2 and 3  I don't think would be too hard. In fact, I imagine
they would look something like this.

Dim newID as String
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value & ", " & newID

The problem is, I don't even know how to begin coding step 1. Anyone
have any bright ideas?

Option Explicit
' Developed by Contextures Inc.
'www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 3 Then
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
      Target.Value = oldVal _
        & ", " & newVal
      End If
    End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

Send me this msg and your file and I'll take a look.
dguillett1 @gmail.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top