locked
Mulitple vlookup results in a cell by comma seperated RRS feed

  • 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
    Wednesday, March 9, 2011 3:16 AM

Answers

  • 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
    • Marked as answer by Bessie Zhao Wednesday, March 16, 2011 10:02 AM
    Wednesday, March 9, 2011 7:17 AM
  • 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.

     

     

     

    • Marked as answer by Bessie Zhao Wednesday, March 16, 2011 10:02 AM
    Wednesday, March 9, 2011 7:53 PM

All replies

  • 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
    • Marked as answer by Bessie Zhao Wednesday, March 16, 2011 10:02 AM
    Wednesday, March 9, 2011 7:17 AM
  • 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

    Wednesday, March 9, 2011 1:35 PM
  • 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

    Wednesday, March 9, 2011 1:36 PM
  • 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
    Wednesday, March 9, 2011 5:27 PM
  • What version of excel are you using?

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

     

     

    Wednesday, March 9, 2011 5:51 PM
  • 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
    Wednesday, March 9, 2011 7:42 PM
  • 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.

     

     

     

    • Marked as answer by Bessie Zhao Wednesday, March 16, 2011 10:02 AM
    Wednesday, March 9, 2011 7:53 PM