Instead of 3 cells collections -3 columns

Excelnewbie001

Board Regular
Joined
Jan 25, 2017
Messages
79
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:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
TKgQIlG.png
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top