How can i use Vlookup as a result in a concatenate string variable

[Colin D]

Hi All,

I'm a newbie to VBA so maybe this is a simple question, but I can’t figure out how to concatenate 3 textboxes with a VLookup result value coming from an external worksheet with a named range called "Lookup".
The Vlookup search value is entered in a Listbox ( SelectFw) which is used to search for the answer in column 3 , 4 and 5 of the external worksheet.

The found value will populate my 3 textboxes, but if i select my next "listbox" value the current value of the textboxes will be overwritten.

I tried to loop through Vlookup and use concatenate to combine the result values but this isn’t working at all.
I think I’m on the wrong track here so can anyone point me in the right direction??

The code below is the code I use, which gives only one result.

many thanks in advance

Private Sub CalenderLabel_Click()

Dim i As Integer
Dim j As Integer
Dim arrItems() As String

ReDim arrItems(0 To SelectFw.ColumnCount - 1)
    For j = 0 To SelectFw.ListCount - 1
        If SelectFw.Selected(j) Then
            For i = 0 To SelectFw.ColumnCount - 1
                arrItems(i) = SelectFw.Column(i, j)
                        
            'Lookup values based on SelectFw
                With Me
                    .Reg3 = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 3, 0)
                    .Reg4 = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 4, 0)
                    .Reg6 = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 6, 0)
                End With
            Next i
        End If
    Next j
  
 End Sub

 

[ndsutherland]

I'm not great at this, and I'm not entirely sure what you are trying to do, but try something like:

Private Sub CalenderLabel_Click()

Dim i As Integer
Dim j As Integer
Dim reg3Items() As String
Dim reg4Items() As String
Dim reg6rItems() As String


For j = 0 To SelectFw.ListCount - 1
    If SelectFw.Selected(j) Then
        For i = 0 To SelectFw.ColumnCount - 1
            ReDim Preserve reg3Items(0 To UBound(reg3Items) + 1)
            reg3Items(UBound(reg3Items)) = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 3, 0)
            ReDim Preserve reg4Items(0 To UBound(reg4Items) + 1)
            reg4Items(UBound(reg4Items)) = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 4, 0)
            ReDim Preserve reg6Items(0 To UBound(reg6Items) + 1)
            reg6Items(UBound(reg6Items)) = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 6, 0)
            
        Next i
    End If
Next j


With Me
    .Reg3 = Join(reg3Items, ", ")
    .Reg4 = Join(reg4Items, ", ")
    .Reg6 = Join(reg6Items, ", ")
End With


End Sub

 

[Colin D]

Hi Nd,

Thanks for your reply to my post and helping me in the right direction
Your code looks like the solution I need, but there is a problem with UBound because it gives "Subscript out of range"
Therefore I hope you can tell mee if UBound is an declared Variable because I can't find this in my locals view and it looks like it is not recognized.
Any Idea??

Many Thanks Colin

 

[ndsutherland]

Perhaps

Dim i As Integer
Dim j As Integer
Dim reg3Items() As String
Dim reg4Items() As String
Dim reg6rItems() As String


For j = 0 To SelectFw.ListCount - 1
    If SelectFw.Selected(j) Then
        For i = 0 To SelectFw.ColumnCount - 1
            [COLOR=#ff0000]If ubound(reg3Items) <> 0 Then [/COLOR]ReDim Preserve reg3Items(0 To UBound(reg3Items) + 1)
            reg3Items(UBound(reg3Items)) = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 3, 0)
            [COLOR=#ff0000]If UBound(reg4Items) <> 0 Then [/COLOR]ReDim Preserve reg4Items(0 To UBound(reg4Items) + 1)
            reg4Items(UBound(reg4Items)) = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 4, 0)
            [COLOR=#ff0000]If UBound(reg6Items) <> 0 Then [/COLOR]ReDim Preserve reg6Items(0 To UBound(reg6Items) + 1)
            reg6Items(UBound(reg6Items)) = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 6, 0)
            
        Next i
    End If
Next j


With Me
    .Reg3 = Join(reg3Items, ", ")
    .Reg4 = Join(reg4Items, ", ")
    .Reg6 = Join(reg6Items, ", ")
End With


End Sub

UBound means the highest index in an array (LBound is the lowest)

 

[Colin D]

Hi ND,

I'm affraid that this won't work either, and I why.
In my first post I wasn't clear about the thing that I don't use an array but the values are selected from a worksheet. (my bad)
The worksheet is used to convert Fiscal Weeks into Calender weeks, so I first check the selected value(s) in the listbox with the lookup function in the worksheet.
Next the values (3columns) found in the worksheet with lookup has to be returned into the textboxes.
The code that i first posted works for that.
But if I multiselect items in the listbox I want to return multiple value's in the textboxes separated by a comma.

It Seems that the code you send me refers to an array so obviously this won't work.

Is it ineviteble that I have to use an array for this?? or is there an other solution??

Thanks I appreciate your help on this.

Greets

Colin

 

[ndsutherland]

I think we are on the same page, but I set up the array incorrectly. Try this

Private Sub CalenderLabel_Click()Dim i As Integer
Dim j As Integer
Dim u As Integer
Dim reg3Items() As String
Dim reg4Items() As String
Dim reg6rItems() As String


u = 0
For j = 0 To SelectFw.ListCount - 1
    If SelectFw.Selected(j) Then
        For i = 0 To SelectFw.ColumnCount - 1
            ReDim Preserve reg3Items(u)
            reg3Items(u) = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 3, 0)
            ReDim Preserve reg4Items(u)
            reg4Items(u) = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 4, 0)
            ReDim Preserve reg6Items(u)
            reg6Items(u) = Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 6, 0)
        Next i
        u = u + 1
    End If
Next j


With Me
    .Reg3 = Join(reg3Items, ", ")
    .Reg4 = Join(reg4Items, ", ")
    .Reg6 = Join(reg6Items, ", ")
End With


End Sub

 

[Colin D]

Hi ND,

Thanks for your code, this works fine and is a very flexible solution, in the mean time I got up with this.

 'Lookup values based on first control
                With Me
                    .Reg3 = Reg3.Value & Application.WorksheetFunction.VLookup((Me.SelectFw.Column(i, j)), Range("Lookup"), 3, 0) & ", "

I repeat this for every textbox.
This was something I haven't thought of before, only problem with both codes is that my output will be displayed as:

WEEK: 29, 30, 31, 32,

MONTH: July, July, July, August

Year: 2015, 2015, 2015, 2015

This moment I only use reg3 as value for my filter, so the solution work but isn't perfect. maybe I will inprove that later.

I appriciate your effort and the time you took to help me, If I can return the favor, let me know.

Greetings,

Colin

 

[ndsutherland]

How do you want the output displayed? Join() won't put a comma at the end.