How to find duplicate cell address using VBA

Question

I am very familiar with VBA in excel. I'm not even 100% sure about how to insert the module correctly, so this will be a big help.

I created a worksheet that randomizes a number from 1 to 100.

B3  =RANDBETWEEN(C6,F6)

I have 13 members. Everyone guesses the number. The goal should be closest to the randomized number. (Guess the number between x and y. The coming “Prize” wins)

Participants are listed in A9: B21. (i.e., "Member # 1")

Their guesses are listed in C9: C21.

The difference between the randomized number and the guess is indicated in D9: D21

D9:D21  =IF(C9>$B$3,C9-$B$3,IF($B$3>C9,$B$3-C9,0))

Cells F9: F21 allow you to find out who won and does not count any guesses of less than 1 and more than 100.

F9:F21  =IF(C9<1,,IF(C9>100,,IF(D9=MIN($D$9:$D$21),A9&" Wins",)))

Unfortunately, every time I try to reference in cell C6 or F6 instead of 1 or 100, I get only the result 0.

F8 , , . , - .

F8  =IF(COUNTIF(F9:F21,"*")>1,"Tie Breaker Needed","")

. , , , . , , , , .

; №7 --- " №7",     №7 №10 --- , № 7 № 10.

VBA, ? VBA , , #NAME? .

, , , - .

, Excel -.

Excel

Option Explicit

Function LookupCSVResults(lookupValue As Integer, lookupRange As Range, resultsRange As Range) As String

    Dim s As String 'Results placeholder
    Dim sTmp As String  'Cell value placeholder
    Dim r As Long   'Row
    Dim c As Long   'Column
    Const strDelimiter = "|||"  'Makes InStr more robust

    s = strDelimiter
    For r = 1 To lookupRange.Rows.Count
        For c = 1 To lookupRange.Columns.Count
            If lookupRange.Cells(r, c).Value = lookupValue Then
                'I know it weird to use offset but it works even if the two ranges
                'are of different sizes and it the same way that SUMIF works
                sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
                If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                    s = s & sTmp & strDelimiter
                End If
            End If
        Next
    Next

    'Now make it look like CSV
    s = Replace(s, strDelimiter, ",")
    If Left(s, 1) = "," Then s = Mid(s, 2)
    If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)

    LookupCSVResults = s 'Return the function

End Function

Answer 1

UDF():

Option Explicit

Public Function ListWinners(people As Range, deltas As Range) As String
   Dim wf As WorksheetFunction
   Dim i As Long, delta As Long, msg As String
   Dim WinningValue As Long
   Set wf = Application.WorksheetFunction
   ListWinners = ""
   msg = " wins"
   WinningValue = wf.Min(deltas)
   For i = 1 To deltas.Rows.Count
      If deltas(i) = WinningValue Then
         If ListWinners = "" Then
            ListWinners = people(i)
         Else
            ListWinners = ListWinners & " & " & people(i)
            msg = " tie"
         End If
      End If
   Next i
   ListWinners = ListWinners & msg
End Function

:

=ListWinners(A9:A21,C9:C21)

.

EDIT # 1:

(UDF) :

• ALT-F11 VBE
• ALT-I ALT-M
• VBE

, UDF . Excel 2003 , , .xlsm, .xlsx

UDF:

• VBE,
• VBE

UDF Excel:

=ListWinners(A1:A100,B1:B100)

, .

http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

UDF, .

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

!