vreplace.vba 948 B

1234567891011121314151617181920212223242526
  1. Function vreplace(str As String, insep As String, outsep As String, rng As Range, idx As Integer) As String
  2. ' Takes a (sep) seperated string (str) like "banana,carrot,apple",
  3. ' replaces the elements using vlookup with values from range (rng), column (idx)
  4. ' and return as (sep) seperated string
  5. Dim strArray() As String
  6. Dim i As Integer
  7. Dim crtStr As String
  8. Dim coll As New Collection
  9. Set coll = New Collection
  10. strArray = (Split(str, insep))
  11. For i = LBound(strArray) To UBound(strArray)
  12. 'Adding the same value to a collection turns to an error.
  13. 'Handling the error will skip adding the string to the output
  14. crtStr = Application.VLookup(strArray(i), rng, idx, False)
  15. On Error GoTo Skip:
  16. coll.Add crtStr, crtStr
  17. vreplace = vreplace & outsep & crtStr
  18. Skip: Resume Continue:
  19. Continue:
  20. Next
  21. vreplace = Right(vreplace, Len(vreplace) - 1)
  22. End Function