Return Name With Combination Formula

J

JLouis

Active Member
Joined
Jan 1, 2004
Messages
295
Office Version
  1. 365
Platform
  1. Windows
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.

CommandButton1
MatchName1Name2Name3
Bob220350TaterMaxTess
Tater210
Lilly160
Max85
Gilly60
Tess55


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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
I figured it out...

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
Dim Name1 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
'MsgBox Sheets("Sheet2").Range("C" & ForCnt1).Offset(0, -1).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
              
       ' Set Name1 = Sheets("Sheet2").Range("C" & ForCnt1).Offset(0, -1)
              
                                        
                        If summ = OriginalVal Then
                            ActiveSheet.Range("H65536").End(xlUp).Offset(1, 0).Select
                            ActiveCell = Sheets("Sheet2").Range("C" & ForCnt1).Offset(0, -1).Value
                            ActiveCell.Offset(0, 1).Select
                            ActiveCell = Sheets("Sheet2").Range("C" & ForCnt2).Offset(0, -1).Value
                            ActiveCell.Offset(0, 1).Select
                            ActiveCell = Sheets("Sheet2").Range("C" & ForCnt3).Offset(0, -1).Value
                        End If

        Next ForCnt3
    Next ForCnt2
Next ForCnt1

End Sub
 
Upvote 0
Glad you figured it out!

Just one tip for you on declaring variables. This does not do what you think it does:
VBA Code: 
Dim Num1, Num2, Num3, summ As Long
In that case, only "summ" is declared to be a Long Integer. Your other three variables will be sent to Variant.
In VBA, each variable needs to be declared explicitly, like this:
VBA Code: 
Dim Num1 As Long, Num2 As Long, Num3 As Long, summ As Long
or this:
VBA Code: 
Dim Num1 As Long
Dim Num2 As Long
Dim Num3 As Long
Dim summ As Long
 
Upvote 0
Thank you so much. Things like this really help.

While I have your attention...

I adopted my code to a different worksheet and it is missing one of the 7 sets of names from the data set. One of the criteria is listed in C2 and it the total number of numbers in the data set. In this case it is 13. For some reason I added a "+1" to the number (now incorrectly showing 14) and the code worked. I don't know why.

Would you take a peak at the code below and see if anything jumps out at you? I appreciate your time and assistance.

VBA Code: 
Sub NewSum600()

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Dim Num1 As Long, Num2 As Long, Num3 As Long, summ As Long
Dim ForCnt1, ForCnt2, ForCnt3 As Integer
Dim ColNum As Integer
Dim OriginalVal, LoopCnt As Long

ActiveSheet.Range("d2").Select

LoopCnt = Sheets("CalcMe").Range("b1").Value
MsgBox Sheets("CalcMe").Range("b1").Value
ColNum = 3

OriginalVal = Sheets("CalcMe").Range("C2").Value
MsgBox Sheets("CalcMe").Range("C2").Value
For ForCnt1 = 2 To LoopCnt
    Num1 = Sheets("CalcMe").Range("B" & ForCnt1).Value
'MsgBox Sheets("CalcMe").Range("B" & ForCnt1).Offset(0, -1).Value

    For ForCnt2 = ForCnt1 + 1 To LoopCnt
    Num2 = Sheets("CalcMe").Range("B" & ForCnt2).Value
   
        For ForCnt3 = ForCnt2 + 1 To LoopCnt
        Num3 = Sheets("CalcMe").Range("B" & ForCnt3).Value
       
              summ = Num1 + Num2 + Num3
             
       ' Set Name1 = Sheets("Sheet2").Range("C" & ForCnt1).Offset(0, -1)
             
                                       
                        If summ = OriginalVal Then
                            ActiveSheet.Range("D65536").End(xlUp).Offset(1, 0).Select
                            ActiveCell = Sheets("CalcMe").Range("B" & ForCnt1).Offset(0, -1).Value
                            ActiveCell.Offset(0, 1).Select
                            ActiveCell = Sheets("CalcMe").Range("B" & ForCnt2).Offset(0, -1).Value
                            ActiveCell.Offset(0, 1).Select
                            ActiveCell = Sheets("CalcMe").Range("B" & ForCnt3).Offset(0, -1).Value
                        End If

        Next ForCnt3
    Next ForCnt2
Next ForCnt1

End Sub
 
Last edited:
Upvote 0
JLouis said:
Thank you so much. Things like this really help.

While I have your attention...

I adopted my code to a different worksheet and it is missing one of the 7 sets of names from the data set. One of the criteria is listed in C2 and it the total number of numbers in the data set. In this case it is 13. For some reason I added a "+1" to the number (now incorrectly showing 14) and the code worked. I don't know why.

Would you take a peak at the code below and see if anything jumps out at you? I appreciate your time and assistance.

VBA Code: 
Sub NewSum600()

ActiveSheet.Unprotect
Application.ScreenUpdating = False

Dim Num1 As Long, Num2 As Long, Num3 As Long, summ As Long
Dim ForCnt1, ForCnt2, ForCnt3 As Integer
Dim ColNum As Integer
Dim OriginalVal, LoopCnt As Long

ActiveSheet.Range("d2").Select

LoopCnt = Sheets("CalcMe").Range("b1").Value
MsgBox Sheets("CalcMe").Range("b1").Value
ColNum = 3

OriginalVal = Sheets("CalcMe").Range("C2").Value
MsgBox Sheets("CalcMe").Range("C2").Value
For ForCnt1 = 2 To LoopCnt
    Num1 = Sheets("CalcMe").Range("B" & ForCnt1).Value
'MsgBox Sheets("CalcMe").Range("B" & ForCnt1).Offset(0, -1).Value

    For ForCnt2 = ForCnt1 + 1 To LoopCnt
    Num2 = Sheets("CalcMe").Range("B" & ForCnt2).Value
  
        For ForCnt3 = ForCnt2 + 1 To LoopCnt
        Num3 = Sheets("CalcMe").Range("B" & ForCnt3).Value
      
              summ = Num1 + Num2 + Num3
            
       ' Set Name1 = Sheets("Sheet2").Range("C" & ForCnt1).Offset(0, -1)
            
                                      
                        If summ = OriginalVal Then
                            ActiveSheet.Range("D65536").End(xlUp).Offset(1, 0).Select
                            ActiveCell = Sheets("CalcMe").Range("B" & ForCnt1).Offset(0, -1).Value
                            ActiveCell.Offset(0, 1).Select
                            ActiveCell = Sheets("CalcMe").Range("B" & ForCnt2).Offset(0, -1).Value
                            ActiveCell.Offset(0, 1).Select
                            ActiveCell = Sheets("CalcMe").Range("B" & ForCnt3).Offset(0, -1).Value
                        End If

        Next ForCnt3
    Next ForCnt2
Next ForCnt1

End Sub
Click to expand...
Since that is a new question, it would probably be best to ask it in a new thread, and be sure to include an image of your data, as without that, the code is hard to discern/debug.

A few other things though. Note what I said about declaring each variable. I see you fixed the one I used as an example but not these other two:
VBA Code: 
Dim ForCnt1, ForCnt2, ForCnt3 As Integer
...
Dim OriginalVal, LoopCnt As Long

Also note that the use of "ActiveCell" and select in your code is usually not necessary, and actually slows your code down.
This part:
VBA Code: 
                        If summ = OriginalVal Then
                            r = ActiveSheet.Range("D65536").End(xlUp).Offset(1, 0).Select
                            ActiveCell = Sheets("CalcMe").Range("B" & ForCnt1).Offset(0, -1).Value
                            ActiveCell.Offset(0, 1).Select
                            ActiveCell = Sheets("CalcMe").Range("B" & ForCnt2).Offset(0, -1).Value
                            ActiveCell.Offset(0, 1).Select
                            ActiveCell = Sheets("CalcMe").Range("B" & ForCnt3).Offset(0, -1).Value
                        End If
can be simplified to this:
(first, up in your variable declarations):
VBA Code: 
                         Dim r as Long
then:
VBA Code: 
                        If summ = OriginalVal Then
                            ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Row
                            Cells(r, "D").Value = Sheets("CalcMe").Range("B" & ForCnt1).Offset(0, -1).Value
                            Cells(r, "E").Value = Sheets("CalcMe").Range("B" & ForCnt2).Offset(0, -1).Value
                            Cells(r, "F").Value = Sheets("CalcMe").Range("B" & ForCnt3).Offset(0, -1).Value
                        End If
 
Upvote 0
I was wondering about that.

This is such great feedback. I'm a business guy that dabbles in Excel and this type of instruction is quite helpful. I'm amazed you folks here spend your valuable time helping someone like me. Kudos to you and everyone else on the site.
 
Upvote 0
You are welcome.
Glad we are able to help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Second Loop in Macro
Replies
4
Views
467
JLouis
J
B
  • Solved
More Efficient Looping
2
Replies
19
Views
244
bhsoundman
B
SanjayGMusafir
  • Solved
Go to last Cell of Current Table
2
Replies
17
Views
202
SanjayGMusafir
SanjayGMusafir
M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
588
Mplz
M
U
  • Question Question
Copy data from different sheet to another sheet using activecell offset
Replies
3
Views
259
jolivanes
jolivanes

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

Share this page

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