vba select cell with same value as another cell *

[bluefeather8989]

i HAVE A NUMBER in cell AA10. I want to find the same number in range AI12:AO57 then offset 1 cell right. if that cell is "" then 1 else "". I want to do this without selecting cells if possible.

 

[MARK858]

Maybe...

Sub bluefeather()
    Dim FndRng As Range
    
    With Range("AI12:AO57")
        Set FndRng = .Find(Range("AA10").Value, .Cells(.Cells.Count), xlValues, , xlByRows, xlNext)
    End With

    If Not FndRng Is Nothing Then
        If FndRng.Offset(, 1) = "" Then
            FndRng.Offset(, 1).Value = 1
        Else
            FndRng.Offset(, 1).Value = ""
        End If
    End If

End Sub

 

[Peter_SSs]

Without sample data it is a bit hard to know just what might be in the range, whether there could be duplicates etc, but you could also test this.

Sub bluefeather_v2()
  Dim FndRng As Range
  
  Set FndRng = Range("AI12:AO57").Find(What:=Range("AA10").Value, LookAt:=xlWhole)
  If Not FndRng Is Nothing Then FndRng.Offset(, 1).Value = Mid(1, 1, -(FndRng.Offset(, 1).Value = ""))
End Sub

 

[MARK858]

If there were duplicates and you wanted the offset to apply to all the matching entries then my code adjusted would be as per the below (if you just wanted the 1st matching entry then it would still be my 1st code, Peter's would need adjusting as it would pick the 2nd matching entry if the 1st matching entry was in AI12)

Sub bluefeather2()
    Dim FndRng As Range, firstaddress As String
  
    Application.ScreenUpdating = False
  
    With Range("AI12:AO57")
        Set FndRng = .Find(Range("AA10").Value, .Cells(.Cells.Count), xlValues, , xlByRows, xlNext)
      
        If Not FndRng Is Nothing Then
            firstaddress = FndRng.Address
          
            Do
              
                If FndRng.Offset(, 1) = "" Then
                    FndRng.Offset(, 1).Value = 1
                Else
                    FndRng.Offset(, 1).Value = ""
                End If
                Set FndRng = .FindNext(FndRng)
             
            Loop While Not FndRng.Address = firstaddress
      
        End If
  
    End With

End Sub

 

[Peter_SSs]

If there were duplicates and you wanted ...

I will wait to see if such a circumstance can happen and, if so, what action should be taken before possibly modifying my code.

If FndRng Is Nothing Then Exit Do

Mark, that line is superfluous. Inside the Do Loop, FndRng will never be Nothing

 

[MARK858]

Mark, that line is superfluous. Inside the Do Loop, FndRng will never be Nothing

True code has been edited to remove it.