Hi riya787...
if I understand it well, you have a list values (tbl_1.id, tbl_2.name) which you want to store in an array and split these values for getting the table names in another array. I added some code here, the first Sub Get_Variable() gets all tables with (!) duplicates. The second Get_Variable_Uniques() gets the tables without (!) duplicates.
Code:
Option Explicit
Public variable_array() As String
Public table_array() As String
Public Sub Get_Variable()
Dim lngIndex As Long
Dim lngCurrent As Long
Dim lngCount As Long
Dim strValue As String
' Items...
lngCount = Data_Extraction.ListBox2.ListCount - 1
' Redim to Listbox size
ReDim variable_array(0 To lngCount)
ReDim table_array(0 To lngCount)
' Storing the tables in an array
For lngIndex = 0 To lngCount
' Value...
strValue = Data_Extraction.ListBox2.List(lngIndex, 0)
' Variables array...
variable_array(lngIndex) = strValue
table_array(lngIndex) = Split(strValue, ".")(0)
Next lngIndex
' Testing...
For lngIndex = LBound(table_array) To UBound(table_array)
MsgBox table_array(lngIndex)
Next lngIndex
End Sub
Public Sub Get_Variable_Uniques()
Dim lngIndex As Long
Dim lngCurrent As Long
Dim lngCount As Long
Dim strValue As String
Dim objUniques As Collection
' Errors...
On Error Resume Next
' Initialize...
lngCount = Data_Extraction.ListBox2.ListCount - 1
' Redim
ReDim variable_array(0 To lngCount)
ReDim table_array(0 To lngCount)
' Collection...
Set objUniques = New Collection
' Storing the tables in an array
For lngIndex = 0 To lngCount
' Value...
strValue = Data_Extraction.ListBox2.List(lngIndex, 0)
' Variables array...
variable_array(lngIndex) = strValue
' Clear...
Err.Clear
' Split...
strValue = Split(strValue, ".")(0)
' Add...
objUniques.Add strValue, strValue
' Errors...
If Not Err.Number <> 0 Then
' Data...
table_array(objUniques.Count - 1) = strValue
End If
Next lngIndex
' Resize...
If objUniques.Count > 0 Then
ReDim Preserve table_array(0 To objUniques.Count - 1)
End If
' Collection...
Set objUniques = Nothing
' Testing...
For lngIndex = LBound(table_array) To UBound(table_array)
MsgBox table_array(lngIndex)
Next lngIndex
End Sub
You may test the code (I have only done a quick test) and choose the appropriate one.
In the first Sub Get_Variable() there is no need to do two loops, as both arrays have the same size. Assuming that all value contains a dot, when Splitting with Spilt(), you may get the first element of the result from Split().
Filtering the duplicates in Get_Variable_Uniques() is a little bit more complex. For doing this, I used a collection, an object holding items with a unique key. If an already existing item is added to a collection, a runtime error is thrown. So, if the runtime error is ignored (that's why On Error Resume Next is on the top) but also checked per code, then the unique items can be filtered and added to the table array. The last step is to downsize the table array to the real count which corresponds to the count of collection items.
I have also some tipps for you: please indend the code when writing, this makes the code much more comfortable to read. If you declare variables, for example a counter, please use the appropriate type. For example
Dim index As Long is better than
Dim index As Variant as in this case you are using the variable for iterating through a loop.
Hope this helps.
Regards