Results 1 to 2 of 2

Thread: Instead of 3 cells collections -3 columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2017
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Instead of 3 cells collections -3 columns

    Wonder if someone can help me. I have the following code where collections are made on 3 cells H2 I2 J2 -if I want the collections to be on 3 columns instead of 3 cells what code must change.The collections get checked on column A1-15 and B1-15 respectively.So the collections must be on 3 columns values instead of 3 cells column H and I and I.Can this be done ? Any help appreciated -sorry the range will be 3 columns thats done but it should still do the collection -think I got my terminology mixed up there. So the range must be 3 columns instead of 3 cells.Thanks for any help

    Code:
    Sub Test()
    Sheets("Sheet1").Activate
      Dim a(), c As New Collection, d As New Collection, i As Long, j As Long, strKey As String, x As String, y As String, z As String, v1, v2, v3
      Sheets("Sheet1").Select
      a = Range("A1").CurrentRegion.Value
      x = Range("H2").Value                       ' >>>>>> 3 cells H2 I2 J2
      y = Range("I2").Value
      z = Range("J2").Value
      For i = 2 To UBound(a, 1)
          strKey = CStr(a(i, 2))
          On Error Resume Next
             c.Add Key:=strKey, Item:=New Collection
          On Error GoTo 0
          c(strKey).Add a(i, 1)
      Next i
      On Error Resume Next
         Set v1 = c(x)
         Set v1 = c(y)
         Set v1 = c(z)
         If Err.Number <> 0 Then Exit Sub
      On Error GoTo 0
      For Each v1 In c(x)
          For Each v2 In c(y)
              For Each v3 In c(z)
                  d.Add Array(v1, v2, v3)
              Next v3
          Next v2
      Next v1
      ReDim a(1 To d.Count, 1 To 3)
      i = 0
      For Each v1 In d
          i = i + 1
          a(i, 1) = v1(0)
          a(i, 2) = v1(1)
          a(i, 3) = v1(2)
      Next v1
      Range("N18").Resize(UBound(a, 1), UBound(a, 2)).Value = a
    End Sub
    Last edited by Excelnewbie001; Sep 26th, 2017 at 11:23 AM.

  2. #2
    Board Regular
    Join Date
    Jan 2017
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Instead of 3 cells collections -3 columns

    The photo below the code macro worked out 3 ranges -in the code H2 I2 J2......although my sample was set F2 H2 G2. So my question is basically what must be changed so that the code will do the 3 columns......either F2 H2 G2 or the code as per macro I2 H2 J2 so that the whole column F H G or column H I J will be used.Hope this is a clearer
    Last edited by Excelnewbie001; Sep 26th, 2017 at 01:15 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •