ComboBox Selection

**riya787** · 2012-03-22, 06:23 AM

Hi maninweb, I have written the code to get the joining condition for the particular table, it is joined to but i am not getting the desired output

Code:

Option Explicit
Public join_array(25)
Public SourceWB as WorkBook 



Public Sub get_join()


Dim lngIndex As Long
Dim temp As String
Dim temp1 As String
Dim Rowcount As Long
Dim Index As Long
Dim strValue As String
Dim i As Long
Dim j As Long


Set SourceWB = Workbooks.Open("C:\Data.xlsx", False, True)


With SourceWB.Worksheets("condition")




    Rowcount = .Cells(.Rows.Count, "B").End(xlUp).Row
      
    'Testing...
    MsgBox "Rowcount"
    
    'Loop...
    For lngIndex = 0 To UBound(table_array)
        
        strValue = table_array(lngIndex)
        
MsgBox strValue
    
        For Index = 1 To Rowcount
        
            If Not strValue <> .Cells(Index, 1).Value Then
    
                temp = CStr(Index)
                temp1 = .Cells(temp, 2).Value
                MsgBox temp1
                
                
                For i = 0 To UBound(table_array)
    
                            
                            If temp1 = table_array(i) Then
                             
                             join_array(j) = Application.WorksheetFunction.VLookup(temp1, Range("A:C"), 3, False)
                                
                             MsgBox join_array(j)
                             
                             j = j + 1
                                
                            End If
                        
                Next i
                
        
            End If
    
        
        Next Index
    
    
    Next lngIndex
    
End With


End Sub

In the first loop i am getting all the tables which the user selects in the userform.

**riya787** · 2012-03-22, 06:43 AM

Sorry about my previous post...
Hi maninweb, I have written the code to get the joining condition for the particular table, it is joined to but i am not getting the desired output

Code:

Option Explicit
Public join_array(25)
Public SourceWB as WorkBook 



Public Sub get_join()


Dim lngIndex As Long
Dim temp As String
Dim temp1 As String
Dim Rowcount As Long
Dim Index As Long
Dim strValue As String
Dim i As Long
Dim j As Long


Set SourceWB = Workbooks.Open("C:\Data.xlsx", False, True)


With SourceWB.Worksheets("condition")




    Rowcount = .Cells(.Rows.Count, "B").End(xlUp).Row
      
    'Testing...
    MsgBox "Rowcount"
    
    'Loop...
    For lngIndex = 0 To UBound(table_array)
        
        strValue = table_array(lngIndex)
        
        MsgBox strValue
    
        For Index = 1 To Rowcount
        
            If Not strValue <> .Cells(Index, 1).Value Then
    
                temp = CStr(Index)
                temp1 = .Cells(temp, 2).Value
                MsgBox temp1
                
                
                For i = 0 To UBound(table_array)
    
                            
                            If temp1 = table_array(i) Then
                             
                             join_array(j) = Application.WorksheetFunction.VLookup(temp1, Range("A:C"), 3, False)
                                
                             MsgBox join_array(j)
                             
                             j = j + 1
                                
                            End If
                        
                Next i
                
        
            End If
    
        
        Next Index
    
    
    Next lngIndex
    
End With


End Sub

I am storing all the information from a different workbook. I have attached the workbook along with this mail.
In the first loop i am getting all the tables which the user selects in the userform.
In the second loop, i am getting all the table names which have a joining condition with the value from the first loop.
In the third loop, i am checking whether it is present in the table_array and if it is then i am getting the corresponding joining condition for that table. In this step i have used the Vlookup but i am not getting the joining condition.
Could you please help me in this

**maninweb** · 2012-03-22, 11:56 AM

Hi riya787...

can you please tell me, what the desired output should be from your sample file?

For example, if table_array() contains the two values "tbl_2" and "tbl_3", what shout be the values of the join_array() if you do it manually?

The VLookup function works like the one in Excel, so as the column A in the sheet "conditions" has duplicate entries, you only get the first found entry.

Regards :-)

**riya787** · 2012-03-22, 07:08 PM

Hi maninweb...

I checked instead of VLookup , i used .cells and have passed the index value of the table_array element, with that i am getting my desired output. Thanks again...

**riya787** · 2012-03-29, 07:18 AM

Hi maninweb,

In my current Joining Conditon sheet, in the first column A I want tbl_1 to get the first priority and then tbl_2 should get the second priority, depending upon on what the user has choosen and which tables have been stored in the table array.Like for eg:
If the user has selected variables from tbl_1,tbl_2,tbl_5 which is getting stored in the table_array then tbl_1 should get the first priority so that all the join conditions for tbl_1 should be displayed.I have attached the sheet, could you please go through it.

**maninweb** · 2012-03-29, 09:35 AM

Hi riya787...

it is a litle bit difficult for me to follow up this project as I don't know the current code changes and state of your project.
Is it possible for you to attach the file with the code here?

If it is not possible, for example because you do not want to make the file public, I can offer you to write me a PM and I
will tell you my e-Mail and I can see what's possible to do for you.

Regards :-)