VBA - example with arrays, checkboxes etc.

[vbalad]

Hi all,

I am a beginner in VBA.
In the example attached ( there are an unspecified number of codes and groups)
I need:
1. how to collect the codes belonging to a given group into an array or arrays, but into a range or ranges .
2. make columns for the groups in the new sheets created
3 . I need to assign a macro to the button "Check Checkboxes for Group" that will check the checkboxes in column A for a given group, as well as the possibility of selecting the group after entering in the InputBox

It seems to me that I need for point 1. an option to an array or arrays, because if I need to in new sheets columns for groups, I would like that within the workbook the group membership of the codes is remembered

Regards

 

[James006]

Hi,

In my humble opinion, mastering VBA Arrays and VBA Class Modules does require a little bit of patience and practice ....

Look for basic examples first ... before diving into a full-fledged project ...

Good Luck to you

 

[vbalad]

Hi,

Thank you for your answer. I'm a beginner in VBA, but I'm not a beginner in programming (I am Java developer, so I know what arrays, objects ...).

I need some VBA tips on how to approach the topic above, because I need to solve it, and I don't have much time.

I've already made some attempts - I know how to extract unique values from column C

I found this forum because I bought a book by Syrstad Tracy, Jelen Bill Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

Regards

 

[vbalad]

Among other things, I need something similar to the function I found here

How to vlookup to return multiple values in one cell in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

I think it may be useful, but not as a range in one cell separated by a comma, but as an array, so that it works, as I wrote, not only on limited ranges

Regards

 

[shinigamilight]

Among other things, I need something similar to the function I found here

How to vlookup to return multiple values in one cell in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

I think it may be useful, but not as a range in one cell separated by a comma, but as an array, so that it works, as I wrote, not only on limited ranges

View attachment 84722

Regards

There's a join function in vba which works like textjoin, but it has a problem that it only works on single dimensional arrays.

 

[vbalad]

There's a join function in vba which works like textjoin, but it has a problem that it only works on single dimensional arrays.

I don't seem to need two-dimensional arrays as input, the input is code data from column B and group data from column C, which are one-dimensional. However, I can be wrong.

I'm also thinking of passing the name of the group through the InputBox and connecting it to the button, because the point is that the user can mark the checkboxes for the group right away.

I'm interested, is it better to use building your own objects here? Besides... what is the equivalent of a structure called a map (key -> value) in VBA?

 

[Domenic]

I'm not entirely clear as to what you want to do, but hopefully you can use the following code as an example. Note that the code assumes that the workbook running the code contains the data, and that the data is contained in Sheet1. Change the sheet name accordingly.

Option Explicit

Sub test()

    Const TextCompare As Long = 1

    Dim wsSource As Worksheet
    Set wsSource = ThisWorkbook.Worksheets("Sheet1") 'change the sheet name accordingly
   
    Dim lastRow As Long
    With wsSource
        lastRow = .Cells(.Rows.count, "C").End(xlUp).Row
    End With
   
    If lastRow < 2 Then
        MsgBox "No data found!", vbExclamation
        Exit Sub
    End If
   
    Dim Data As Variant
    Data = wsSource.Range("B2:C" & lastRow).Value
   
    'create a dictionary to store key/value pair
    'group stored as key, and array containing corresponding codes stored as value
    Dim dicCodesByGroup As Object
    Set dicCodesByGroup = CreateObject("Scripting.Dictionary")
    dicCodesByGroup.CompareMode = TextCompare 'case-insensitive comparison
   
    'get a list of unique groups
    Dim i As Long
    For i = LBound(Data, 1) To UBound(Data, 1)
        dicCodesByGroup(Data(i, 2)) = ""
    Next i
   
    'for each group in the dictionary, get an array of corresponding codes, and add it to the dictionary
    Dim key As Variant
    Dim codes As Variant
    For Each key In dicCodesByGroup
        codes = getCodesByGroup(Data, key)
         dicCodesByGroup(key) = codes
    Next key
   
    'for each group in the dictionary, get the corresponding array, etc
    Dim key2 As Variant
    Dim group As String
    Dim codes2 As Variant
    For Each key2 In dicCodesByGroup
        group = key2
        codes2 = dicCodesByGroup(key2)
       
        'here's where you might pass group and codes2 to a function for further processing, for example:
        'to write the group in cell E2
                'Range("E2").Value = group
        'to write the corresponding array to a vertical range of cells, starting at cell F2
                'Range("F2").Resize(UBound(codes2, 1)).Value = Application.Transpose(codes2)
         'to write the corresponding array to a horizontal range of cells, starting at G2
                 'Range("G2").Resize(, UBound(codes2, 1)).Value = codes2

    Next key2

End Sub

Private Function getCodesByGroup(ByRef Data As Variant, ByVal group As String) As Variant

    ReDim codes(1 To UBound(Data, 1)) As Long
   
    Dim count As Long
    Dim i As Long
   
    count = 0
    For i = LBound(Data, 1) To UBound(Data, 1)
        If LCase(Data(i, 2)) = LCase(group) Then 'case-insensitive matching
            count = count + 1
            codes(count) = Data(i, 1)
        End If
    Next i
   
    If count > 0 Then
        ReDim Preserve codes(1 To count)
    End If
   
    getCodesByGroup = codes()
   
End Function

Hope this helps!

 

[James006]

Is this matter solved ?

@Domenic
Sorry but test only seems to produce only one key from dictionary ...

 

[Domenic]

@Domenic
Sorry but test only seems to produce only one key from dictionary ...

Can you provide a small sample of the data you're using? And, if you've amended the code in any way, can you also post the code?

 

[James006]

Have just used the data posted in message #1 ... and not amended anything in the code ... just tried to run it ...