can you post a workbook with a sample of your data, with an example of a situation where the vlookup works and one where the #N/A error occurs.
Sub NormalizeInputs()
Dim rg As Range
Dim s As String, sep As String, temp As String
Dim v As Variant, vData As Variant
Dim i As Long, j As Long, k As Long, n As Long, nSubstrings As Long
Dim bSorted As Boolean
sep = ", " 'Separator between elements
Set rg = Selection
Set rg = Intersect(rg, rg.Worksheet.UsedRange) 'If user selects an entire column, don't bother going beyond the used portion of the worksheet
vData = rg.Value
n = rg.Rows.Count
For i = 1 To n
If vData(i, 1) <> "" And Not IsNumeric(vData(i, 1)) Then
v = Split(vData(i, 1), sep)
nSubstrings = UBound(v)
If nSubstrings > 0 Then
For j = 1 To nSubstrings
bSorted = True
For k = nSubstrings To 1 Step -1
If v(k) < v(k - 1) Then
bSorted = False
temp = v(k - 1)
v(k - 1) = v(k)
v(k) = temp
End If
Next
If bSorted = True Then Exit For
Next
s = ""
For k = 0 To nSubstrings
s = s & sep & v(k)
Next
vData(i, 1) = Mid(s, Len(sep) + 1)
Set v = Nothing
End If
End If
Next
rg.Value = vData
End Sub
DummyRecQ28289149.xlsm
Sub NormalizeInputs()
Dim rg As Range
Dim s As String, sep As String, temp As String
Dim v As Variant, vData As Variant
Dim i As Long, j As Long, k As Long, n As Long, nSubstrings As Long
Dim bSorted As Boolean
sep = ", " 'Separator between elements
Set rg = Selection
Set rg = Intersect(rg, rg.Worksheet.UsedRange) 'If user selects an entire column, don't bother going beyond the used portion of the worksheet
If rg.Cells.Count > 1 Then
vData = rg.Value
Else
ReDim vData(1 To 1, 1 To 1)
vData(1, 1) = rg.Value
End If
n = rg.Rows.Count
For i = 1 To n
If vData(i, 1) <> "" And Not IsNumeric(vData(i, 1)) Then
v = Split(vData(i, 1), sep)
nSubstrings = UBound(v)
If nSubstrings > 0 Then
For j = 1 To nSubstrings
bSorted = True
For k = nSubstrings To 1 Step -1
If v(k) < v(k - 1) Then
bSorted = False
temp = v(k - 1)
v(k - 1) = v(k)
v(k) = temp
End If
Next
If bSorted = True Then Exit For
Next
s = ""
For k = 0 To nSubstrings
s = s & sep & v(k)
Next
vData(i, 1) = Mid(s, Len(sep) + 1)
Set v = Nothing
End If
End If
Next
rg.Value = vData
End Sub
Title | # Comments | Views | Activity |
---|---|---|---|
Excel - Scroll Speed | 3 | 25 | |
Using EXCEL to count the number of Rows | 4 | 46 | |
How to Auto-fill data in Database; VBA Code Mixed Combining Two User Forms | 4 | 29 | |
copying horizontal cells values vertically in Excel | 2 | 9 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!