How to merge two variables into one in VB 6? 1

[Niki_S]

I have two arrays as below and I want to merge them into one. How can I do that?

Dim vArray() As Variant
Dim vArray1() As Variant

vArray = oXLSheet.Range("E12:E140").Value
vArray1 = oXLSheet.Range("U12:U140").Value

 cboOperation.Clear
   For lngRow = LBound(vArray, 1) To UBound(vArray, 1)
    If Not IsEmpty(vArray(lngRow, 1)) Then
            cboOperation.AddItem vArray(lngRow, 1)
    End If
   Next lngRow

For lngRow = LBound(vArray1, 1) To UBound(vArray1, 1)
    If Not IsEmpty(vArray1(lngRow, 1)) Then
            cboOperation.AddItem vArray1(lngRow, 1)
    End If
   Next lngRow

Thank You.

 

[strongm]

>I want to merge them into one

Vertically or horizontally? I ask this because if the former I've already supplied a cut-down VB implementation of Excel's VSTACK function for you in your thread222-1818982 which does exactly this.

 

[Niki_S]

I tried it but it it not working. Is there have any other solution?

 

[strongm]

> it not working

In what way is it not working? For example, the following version of your code does exactly what I'd expect it to do on my PC - populate a combobox with a vertical merging of multiple single-column ranges in a 1D array:

[COLOR=blue]Dim vArray As Variant
With oXLSheet
    vArray = vbVStack(.Range("E12:E140"), .Range("U12:U140"))
    cboOperation.Clear
    For lp = 1 To UBound(vArray)
        If vArray(lp) <> "" Then cboOperation.AddItem vArray(lp)
    Next
End With[/color]

 

[Niki_S]

I go this error in this highlighted one as Sub or function not defined,

 With oXLSheet
    vArray5 = [highlight #FCE94F]vbVStack[/highlight](.Range("E12:E140"), .Range("U12:U140"))
    cboOperation.Clear
    For lp = 1 To UBound(vArray5)
        If vArray(lp) <> "" Then cboOperation.AddItem vArray5(lp)
    Next
 End With

How can I fix this error?

Thank you

 

[strongm]

You need the code I posted in your other thread that I linked above.

 

[Andrzejek]

Niki_S, if what you show here is really your code, then it looks to me you are getting lost in your own coding. Missing functions (vbVStack), but also checking elements from one array (vArray) before using elements from different array (vArray[red]5[/red]). You are already up to 5 different / same arrays?

And, just to be safe and cover the entire array, I would do:[tt]
For lp = [blue]LBound(vArray)[/blue] To UBound(vArray)[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>LBound(vArray) To UBound(vArray)

Whilst this is generally true, in this case it isn't necessary. The lbound of the array returned from an Excel range is always 1; it does not honour Option Explicit (or ReDims prior to assignment)

 

[strongm]

Niki_S, also, since you seem to have moved on to vArray5, you may want to change

[tt] If vArray(lp)
[/tt]
to

[tt] If vArray5(lp)[/tt]

 

[Niki_S]

It's working now....thank you so much you both for the helps. I have to ask a one little thing.
How can I ignore duplicate values when add data to my combobox?

Thank you

 

[Niki_S]

And also for another part I applied this theory and without "Step 4" I can get both ranges and with "step 4" it shows me only the first range. I want to use "step 4" because there have unwanted data in my excel sheet.

With oXLSheet
    SMV3 = vbVstack(.Range("H12:H138"), .Range("Q12:Q138"))
    cboSMV.Clear
    For lp = 1 To UBound(SMV3) Step 4
        If SMV3(lp) <> "" Then cboSMV.AddItem SMV3(lp)
    Next
    End With

How can I use use "step 4" and get both ranges?

Thank you

 

[strongm]

>"step 4" it shows me only the first range
Nope. It is showing both ranges - it is just that the stacked ranges don't align with the Step

Consider the below:

 

[strongm]

>How can I ignore duplicate values

Ok, so you keep adding requirements. I know you've mentioned them in other threads, but then the ranges you refer to keep changing in both size, number, and data content so difficult to keep track of what you are actually wanting.

Let's try and get closer to a specific requirement:

[ul]
[li]Do you just want no duplicates in the combobox? Or[/li]
[li]Do you want no duplicates in the array (that feeds the combobox). Do you use that array for anything else (or did you talk about it because you had decided it was the only way to populate the combobox from the spreadsheet?). Or[/li]
[li]Do you actually want to remove duplicates from the underlying spreadsheet ranges? [/li]
[/ul]

A wider point - seems like your spreadsheet has a lot of data you don't want or need. It is often better to carry out data cleansing before trying to work with the data (although I appreciate that this may not always be possible, e.g., if others are using data that you consider junk/unneeded)

 

[Niki_S]

Thank you. I got your point, and how can I get only the highlighted values in the two ranges?

Thank you.

 

[Niki_S]

Yes I just want no duplicates in my combobox.

 

[Andrzejek]

no duplicates in my combobox.

With oXLSheet
    SMV3 = vbVstack(.Range("H12:H138"), .Range("Q12:Q138"))
    cboSMV.Clear
    For lp = 1 To UBound(SMV3) [green]'Step 4[/green]
        If SMV3(lp) <> "" Then [green]
            'Loop thru the items in cboSMV to see if
            'SMV3(lp) is already there.
            'If SMV3(lp) NOT found in cboSMV Then[/green]
                cboSMV.AddItem SMV3(lp)
            [green]'End If[/green]
        End If
    Next
End With

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>no duplicates in my combobox
The minor changes to the code I provided yesterday (eliminates empty strings and duplicates)

[COLOR=blue]    Dim vArray As Variant
    [b]Dim deduper As New Dictionary [COLOR=green]'requires reference to Microsoft Scripting Runtime to be added[/color][/b]
    With oXLSheet
        vArray = vbVStack(.Range("E12:E140"), .Range("U12:U140"))
        cboOperation.Clear
        [b]On Error Resume Next[/b]
            For lp = 1 To UBound(vArray)
                If vArray(lp) <> "" Then
                    [b]deduper.Add vArray(lp), vArray(lp)[/b]
                    [b]If Err = 0 Then[/b] cboOperation.AddItem vArray(lp)
                    [b]Err.Clear[/b]
                [b]End If[/b]
            Next
        [b]On Error GoTo 0[/b]
    End With[/color]

 

[Andrzejek]

strongm.
Niki_S is not dealing with [tt]vArray[/tt] any more, she (?) is using [tt]SMV3[/tt] now
Hard to keep up with the changes....

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Oh, I know, I know - but best to just show changes from my example than trying to incorporate changes I didn't originate. The OP can merge my stuff back into theirs ...

 

[Niki_S]

Thank you strongm... It is ok now.
I want to know is there anything else to use for "step 4"?