Good day resident experts!
I found the code below that generates all possible combinations of 3 numbers to match a specified value. I'm dealing with a pool of less than 20 numbers. I've been trying to modify the code to return the name of a person associated with the number. The name list is offset -1 columns from the number list. I've tried using offset (0,-1) but to no avail. As an example, I made this sample.
Any help, as always, is much appreciated.
I found the code below that generates all possible combinations of 3 numbers to match a specified value. I'm dealing with a pool of less than 20 numbers. I've been trying to modify the code to return the name of a person associated with the number. The name list is offset -1 columns from the number list. I've tried using offset (0,-1) but to no avail. As an example, I made this sample.
CommandButton1 | |||||
Match | Name1 | Name2 | Name3 | ||
Bob | 220 | 350 | Tater | Max | Tess |
Tater | 210 | ||||
Lilly | 160 | ||||
Max | 85 | ||||
Gilly | 60 | ||||
Tess | 55 | ||||
Any help, as always, is much appreciated.
VBA Code:
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Dim Num1, Num2, Num3, summ As Long
Dim ForCnt1, ForCnt2, ForCnt3 As Integer
Dim ColNum As Integer
Dim OriginalVal, LoopCnt As Long
ActiveSheet.Range("H2:J1000").Select
Selection.ClearContents
ActiveSheet.Range("H1").Select
LoopCnt = Sheets("Sheet2").Range("F1").Value
ColNum = 3
OriginalVal = Sheets("Sheet2").Range("F2").Value
For ForCnt1 = 2 To LoopCnt
Num1 = Sheets("Sheet2").Range("C" & ForCnt1).Value
For ForCnt2 = ForCnt1 + 1 To LoopCnt
Num2 = Sheets("Sheet2").Range("C" & ForCnt2).Value
For ForCnt3 = ForCnt2 + 1 To LoopCnt
Num3 = Sheets("Sheet2").Range("C" & ForCnt3).Value
summ = Num1 + Num2 + Num3
If summ = OriginalVal Then
ActiveSheet.Range("H65536").End(xlUp).Offset(1, 0).Select
ActiveCell = Num1
ActiveCell.Offset(0, 1).Select
ActiveCell = Num2
ActiveCell.Offset(0, 1).Select
ActiveCell = Num3
End If
Next ForCnt3
Next ForCnt2
Next ForCnt1
End Sub