title: VBA I want to compare windows username with a named range and return the cell reference so I can replace with a new value
body: <p>in the below code I have a named ranges called "currentOwn" and "authHolders". </p>
  
  <p>What I am trying to do is use the function to find the windows username which works. I then want to compare this to the named range "currentOwn" and give me a cell reference within that range that I can then use to replace with another value. </p>
  
  <p>This is a system that allows a user to hand over keys to another user, I only want the range to include people who have key ownership, because there is only a fixed amount of keys.</p>
  
  <pre><code>Option Explicit
  
  Public Function WindowsUserName()
  
      ' Function to return the username of whoever is logged in
      Dim Position As Integer, FirstName As String, LastName As String, OldUsername As String
  
      OldUsername = LCase(Environ$("UserName"))
      Position = InStr(1, OldUsername, ".")
  
      If Position = 1 Or Position = Len(OldUsername) Then
          WindowsUserName = OldUsername
      Else
          FirstName = UCase(Mid(OldUsername, 1, 1)) &amp; Mid(OldUsername, 2, Position - 2)
          LastName = UCase(Mid(OldUsername, Position + 1, 1)) &amp; Mid(OldUsername, Position + 2, Len(OldUsername) - Position - 1)
          WindowsUserName = FirstName &amp; " " &amp; LastName
      End If
  
  End Function
  
  Private Sub CommandButton1_Click()
  
      Dim newHolder As Variant, currentHold As Variant, handOverTo As Variant, i As Integer, rng As Range
  
      currentHold = WindowsUserName
      newHolder = Sheets("tools").Range("K1")
  
      If Range("currentOwn").Find(newHolder, , Excel.xlValues) Is Nothing Then
          If Range("authHolder").Find(newHolder, , Excel.xlValues) Is Nothing Then
              MsgBox ("Not an authorised key holder")
          End If
      Else
  
          MsgBox ("This person currently holds a set of keys please select another user")
          Exit Sub
          End If
  
          For i = 1 To 500
  
              Set rng = Range("currentOwn")
  
              If rng.Cells(i).Find(currentHold, , Excel.xlValues) = True Then
                  MsgBox ("Found value on row " &amp; i)
                  Exit Sub
              End If
  
          Next i
  
  
  
  End Sub
  </code></pre>
  
  <p>the loop at the bottom is my attempt to try and get it to show me the cell reference. However this does not work I would like to use this value to set an Active cell then I can replace value with another.</p>
  
  <p>Regards,</p>
  
  <p>Hello I have now fixed this using the below instead of a loop thanks for your help, however I have a query on this code now how can I make this find the match but only select the one where the date column ("Match".offset,3)="blank"</p>
  
  <pre><code>If Trim(currentHold) &lt;&gt; "" Then
          With Sheets("currentHolders").Range("currentOwn")
              Set rng = .Find(What:=currentHold, _
                              After:=.Cells(.Cells.Count), _
                              LookIn:=xlValues, _
                              LookAt:=xlWhole, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, _
                              MatchCase:=False)
              If Not rng Is Nothing Then
                  Application.Goto rng, True
                  MsgBox (rng)
                  End If
          End With
      End If
  </code></pre>
  
  <p>Regards,</p>