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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
451
JLouis
J
M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
509
Mplz
M
U
  • Question Question
Copy data from different sheet to another sheet using activecell offset
Replies
3
Views
236
jolivanes
jolivanes
C
  • Solved
Hide Columns
Replies
9
Views
159
Joe4
Joe4
A
  • Solved
copy specific columns from listbox to userform without selection
2 3
Replies
21
Views
203
MikeVol
MikeVol

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

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