So I've been working with this code you've provided, and I've managed to get it pulling in 4 columns of data now. Originally It was creating a really long list of every match possible from those 4 rows, but after changing this part around:
Code:
For Each c1 In rngSelection.Columns(1).Cells
If Not IsEmpty(c1.Value) Then
For Each c2 In rngSelection.Columns(2).Cells
If Not IsEmpty(c2.Value) Then
For Each c3 In rngSelection.Columns(3).Cells
If Not IsEmpty(c3.Value) Then
For Each c4 In rngSelection.Columns(4).Cells
If Not IsEmpty(c1.Value) Then
I can get it to stop generating the last column, so I end up with something that looks like this
[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]8/29/2017[/TD]
[TD="width: 64"] Zachary[/TD]
[TD="width: 64"]Test1[/TD]
[TD="width: 64"]Test1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD]Test2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD]Test3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD]Test4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD]Test5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD]Test6[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/29/2017[/TD]
[TD] Zachary[/TD]
[TD]Test2[/TD]
[TD]Test1[/TD]
[/TR]
</tbody>[/TABLE]
ect.
My question is, is there a change I'm missing that can tie c1,c3,and c4 together so those three columns are only looking at c2?
I'm trying to get something that looks like this and repeats for every name:
[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]8/29/2017[/TD]
[TD="width: 64"] Zachary[/TD]
[TD="width: 64"]Test1[/TD]
[TD="width: 64"]Test1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/30/2017[/TD]
[TD] Zachary[/TD]
[TD]Test2[/TD]
[TD]Test2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8/31/2017[/TD]
[TD] Zachary[/TD]
[TD]Test3[/TD]
[TD]Test3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9/1/2017[/TD]
[TD] Zachary[/TD]
[TD]Test4[/TD]
[TD]Test4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9/2/2017[/TD]
[TD] Zachary[/TD]
[TD]Test5[/TD]
[TD]Test5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9/3/2017[/TD]
[TD] Zachary[/TD]
[TD]Test6[/TD]
[TD]Test6[/TD]
[/TR]
</tbody>[/TABLE]
Here's the changes I made to the code so far
Code:
Public Sub CrossJoinSelection()
Dim avntCartesian() As Variant
Dim wksOutput As Worksheet
Dim rngSelection As Range
Dim lngCounter As Long
Dim c1 As Range
Dim c2 As Range
Dim c3 As Range
Dim c4 As Range
On Error GoTo ErrHandler
If Not TypeOf Selection Is Range Then
MsgBox "Selection must be a range.", vbExclamation
GoTo ExitProc
End If
Set rngSelection = Intersect(Selection, Selection.Parent.UsedRange)
If Not rngSelection Is Nothing Then
lngCounter = lngCounter + 1
ReDim Preserve avntCartesian(1 To 4, 1 To lngCounter)
avntCartesian(1, lngCounter) = c1.Value
avntCartesian(3, lngCounter) = c3.Value
avntCartesian(4, lngCounter) = c4.Value
avntCartesian(2, lngCounter) = c2.Value
End If
Next c4
End If
Next c3
End If
Next c2
End If
Next c1
End If
If lngCounter > 0 Then
avntCartesian = TranposeArray(avntCartesian)
Set wksOutput = ThisWorkbook.Sheets.Add
wksOutput.Range("C13:F13").Value = Array("Column1", "Column2", "Column3", "Column4")
wksOutput.Range("C13:F13").Resize(lngCounter).Value = avntCartesian
Else
MsgBox "No values found in selection.", vbExclamation
End If
ExitProc:
Set rngSelection = Nothing
Set wksOutput = Nothing
Set c1 = Nothing
Set c2 = Nothing
Set c3 = Nothing
Set c4 = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitProc
End Sub
Private Function TranposeArray(avntSource() As Variant) As Variant()
Dim avntTarget() As Variant
Dim intLower1 As Integer
Dim intUpper1 As Integer
Dim lngLower2 As Long
Dim lngUpper2 As Long
Dim i As Integer
Dim j As Long
intLower1 = LBound(avntSource, 1)
intUpper1 = UBound(avntSource, 1)
lngLower2 = LBound(avntSource, 2)
lngUpper2 = UBound(avntSource, 2)
ReDim avntTarget(lngLower2 To lngUpper2, intLower1 To intUpper1)
For j = lngLower2 To lngUpper2
For i = intLower1 To intUpper1
avntTarget(j, i) = avntSource(i, j)
Next i
Next j
TranposeArray = avntTarget
End Function