Mulitple vlookup results in a cell by comma seperated

Question

Hello all,

How do I search a list of values from another worksheet, if found then copy the offset cell value and paste it to original sheet, differentiate by comma. I have gone thru threads, found some usefull info. but not getting the desired output. Expecting urgent help. Is there such type of VBA codes available like

Dim WSSeek As Worksheet

Dim wSsource As Worksheet

Dim r As Range

Dim rFound As Range

Dim rText As String

Dim x As Integer

Dim FisrtAddress As String

Set wSsource = ActiveWorkbook.Worksheets("Ageing")

Set r = wSsource.Range("B2")

Set WSSeek = ActiveWorkbook.Worksheets("BOM")

Do While Len(r.Text) > 0

x = Cells(r.Row, Columns.Count).End(xlToLeft).Column + 1

Set rFound = WSSeek.Range("C:C").Find(r.Text)

If Not rFound Is Nothing Then

FirstAddress = rFound.Address

Do

rText = rFound.Offset(0, 2) & " - " & rFound.Offset(0, 3) & " - " & _

rFound.Offset(0, 5) _

& " - " & rFound.Offset(0, 4)

Cells(r.Row, x).Value = rText & "," & rText or next found instances (differntiate by comma)

Set rFound = WSSeek.Range("C:C").FindNext(rFound)

Loop While Not rFound Is Nothing And rFound.Address <> FirstAddress

End If

Set r = r.Offset(1, 0)

Loop

End Sub

Answer 1

Ther are two usful functions in VBA SPLIT(), and JOIN()

 

If you have a line of CSV data you use SPLIT to sperate the values into an array like this

MyStr = "a,b,c,d,e,f,g,h,i,j"

MyStr = split(Mystr,",")

 

Join is the opposite from split put requires an array starting at 0

Sub JoinStr()

Dim MyArray()

Set rFound = Range("A1")
Set lastCell = rFound.End(xlToRight)
Set DataRange = Range(rFound, lastCell)

'put data into zero index array
ReDim MyArray(0 To (DataRange.Count - 1))
Index = 0
For Each itm In DataRange
   MyArray(Index) = itm
   Index = Index + 1
Next itm

myCSVString = Join(MyArray, ",")
End Sub

 

 

---

jdweng

Answer 2

I didn't read the OP, but...

Option Explicit
Sub testme()
    Dim myArr(-3 To 0) As String

    myArr(-3) = "This"
    myArr(-2) = "worked"
    myArr(-1) = "ok"
    myArr(0) = "!!!"

    MsgBox Join(myArr, " ")

End Sub

Seems to work fine.

Joel, Engineer wrote:

Ther are two usful functions in VBA SPLIT(), and JOIN()



If you have a line of CSV data you use SPLIT to sperate the values into an array like this

MyStr = "a,b,c,d,e,f,g,h,i,j"

MyStr = split(Mystr,",")



Join is the opposite from split put requires an array starting at 0

Sub JoinStr()

Dim MyArray()

Set rFound = Range("A1")
Set lastCell = rFound.End(xlToRight)
Set DataRange = Range(rFound, lastCell)

'put data into zero index array
ReDim MyArray(0 To (DataRange.Count - 1))
Index = 0
For Each itm In DataRange
   MyArray(Index) = itm
   Index = Index + 1
Next itm

myCSVString = Join(MyArray, ",")
End Sub





--
jdweng

--

Dave Peterson

Answer 3

ps.  I used xl2003 for testing.

Dave Peterson wrote:

I didn't read the OP, but...

Option Explicit
Sub testme()
    Dim myArr(-3 To 0) As String

    myArr(-3) = "This"
    myArr(-2) = "worked"
    myArr(-1) = "ok"
    myArr(0) = "!!!"

    MsgBox Join(myArr, " ")

End Sub

Seems to work fine.

Joel, Engineer wrote:

Ther are two usful functions in VBA SPLIT(), and JOIN()



If you have a line of CSV data you use SPLIT to sperate the values into an array like this

MyStr = "a,b,c,d,e,f,g,h,i,j"

MyStr = split(Mystr,",")



Join is the opposite from split put requires an array starting at 0

Sub JoinStr()

Dim MyArray()

Set rFound = Range("A1")
Set lastCell = rFound.End(xlToRight)
Set DataRange = Range(rFound, lastCell)

'put data into zero index array
ReDim MyArray(0 To (DataRange.Count - 1))
Index = 0
For Each itm In DataRange
   MyArray(Index) = itm
   Index = Index + 1
Next itm

myCSVString = Join(MyArray, ",")
End Sub





--
jdweng

--

Dave Peterson

--

Dave Peterson

Answer 4

Dave : The Join() function only works with an array starting at index zzero.  When you copy an range to an array like this MyArray = Myrange.values you get an array starting at index 1 and the Join() function will not work.  Do you know any other method than the one I used in my example in this posting.

---

jdweng

Answer 5

What version of excel are you using?

The code I posted worked fine in both xl2003 and xl2007.

 

 

Answer 6

DAve : I wasn't saying anything was wrong with your code.  Sorry if I confused you.  the problem is with these two statments

 

MyArray = Range("A1:Z1")

MyStr = Join(MyArray,",")

 

MyArray is created with the first index starting at zero.  Join only works with arrays startingat index 0.   what you did is the only thing that seem to work is to move the data into a zero index array using a for loop like this

For Each itm In DataRange
   MyArray(Index) = itm
   Index = Index + 1
Next itm

---

jdweng

Answer 7

My point is that it doesn't matter what the lower bound of the array is. 

Join worked with my example where the lower bound was -3.

I think that the problem is that when you use this line:

MyArray = Range("A1:Z1").value

(I like to be explicit and specify the .value property.)

You end up with a 2 dimensional array (1 row by 26 columns.

One way to convert that to a single dimension is to use something like:

Option Explicit
Sub testme()
   
    Dim myArray As Variant
    Dim myStr As String
   
    myArray = Range("A1:Z1").Value
   
    With Application
        myArray = .Transpose(.Transpose(myArray))
    End With
   
    myStr = Join(myArray)
   
    MsgBox myStr

End Sub

And that seems to work ok, too -- even though the lower bound for this array is 1.