Alphabetizing/Find-Replacing

[gijoey24]

Hey guys,

So this one is really getting in the weeds. I'm using the following code to convert a series of names in a cell delimited by commas to codes the names represent, with spaces and commas delimiting, still as text in a single cell.

3 Part Question:

1. As is, after I run the script I'm getting an extra " " in front of the result, and I can't see where I've screwed that up?
2. After I've converted the names to codes, I want to alphabetize the codes. Definitely want the alphabetization to occur after the code conversion, because the names and codes don't line up alphabetically, so I want to use the codes as the values to alphabetize.
3. After I've done that, I want to set up a second button to do pretty much the same function, except find and remove (replace w/ "", what have you) from a cell containing the codes we've just put together. So I enter names, hit button, and it converts to codes per below, then removes those codes from a cell which may contain them.

I'm at the early end of my VBA experience and as much as it kills me to post this because I want to solve it myself, I've got to get this finished for a project I'm working on. I'm banging my head against the wall here- any chance somebody's got an idea?


Sub Button2ForamIndex_Click()


Dim KeyCells As Range


' The variable KeyCells contains the cells that will
' cause an alert when they are changed.

Set KeyCells = Range("Q13")

For Each editedCell In KeyCells.Cells
If Not Application.Intersect(KeyCells, editedCell) Is Nothing Then

Dim splitcells() As String
splitcells = Split(editedCell.Value, ",")
editedCell.ClearContents
For Each splitCell In splitcells
Dim trimCell As String
trimCell = Trim(splitCell)
Set ForamLookupTable = Worksheets("References").Range("U:V")
Set GenusCodeLookupTable = Worksheets("References").Range("V:V")
Set FamilyLookupTable = Worksheets("References").Range("T:X")

If (Not IsEmpty(trimCell)) Then
If Not IsError(Application.VLookup(trimCell, ForamLookupTable, 2, False)) Then
editedCell.Value = editedCell.Value & "," & Application.VLookup(trimCell, ForamLookupTable, 2, False)
ElseIf Not IsError(Application.VLookup(trimCell, GenusCodeLookupTable, 1, False)) Then
editedCell.Value = editedCell.Value & "," & Application.VLookup(trimCell, GenusCodeLookupTable, 1, False)
ElseIf Not IsError(Application.VLookup(trimCell, FamilyLookupTable, 4, False)) Then
editedCell.Value = editedCell.Value & "," & Application.VLookup(trimCell, FamilyLookupTable, 5, False)
Else
editedCell.Value = editedCell.Value & "," & trimCell
End If
End If
Next
End If


If Left(editedCell.Value, 1) = "," Then
If Len(editedCell.Value) > 1 Then
editedCell.Value = Right(editedCell.Value, Len(editedCell.Value) - 1)
End If
End If
Next
End Sub

 

[gijoey24]

OK- Figured out the spacing issue, which was unbelievably stupid on my part. Just needed to remove 2 spaces at the end instead of one, so the bottom looks like this:

If Left(editedCell.Value, 1) = "," Then
If Len(editedCell.Value) > 1 Then
editedCell.Value = Right(editedCell.Value, Len(editedCell.Value) - 2)
End If
End If

So now just to the Alphabetizing and Find/Removing...