Using VBA to find and select cells in a defined range

[Sandman1985]

Hi All,

Hope you're all well.

Have been playing with some VBA to try and create a search and select function and really struggling. My spreadsheet is basically a massive table and has dates in Row 8.
With my code below, I have got an input box to come up to enable a user to enter in a date and be taken to the column where that date exists - did try this with a fixed input cell too but failed there.
My dates start at G8 and finish at EAI8 so have tried to define my search range in the code. Each time I run it, i get the "Not Found" message box. If I use the regular Find function, it actually works fine.

Sub Search()
Dim Find As String
Dim Rng1 As Range
FindS = InputBox("Enter the date you want to search")
With Sheets("Scheduler View").Range("G8:EAI8")
Set Rng = .Find(What:=FindString, After:=Range("G8"))
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Not found"
End If
End With
End Sub

Not sure where I have gone wrong, but any help would be greatly appreciated.

Cheers,

Sandman

 

[rlv01]

Perhaps something like this.

Sub Search()
    Dim FindS As String
    Dim FindD As Date, I As Long
    Dim VA As Variant
    Dim Found As Boolean
   
    FindS = InputBox("Enter the date you want to search", "Required Date Format: mm/dd/yyy")
   
    If FindS = "" Then
        Exit Sub
    End If
   
    If Not IsDate(FindS) Then
        MsgBox "Error: '" & FindS & "' is not a valid date. " & vbCrLf _
        & "", vbOKOnly Or vbCritical, "Invalid Date"
        Exit Sub
    End If
   
    FindD = DateValue(FindS)
    FindS = Format(FindD, "m/d/yyyy")
   
    With Sheets("Scheduler View").Range("G8:EAI8")
        .NumberFormat = "m/d/yyyy" 'standardize format
        VA = .Value 'load array
       
        'search array
        Found = False
        For I = 1 To UBound(VA, 2)
            If VA(1, I) = FindS Then
                Found = True
                Exit For
            End If
        Next I
       
        'results
        If Not Found Then
            MsgBox FindS & " not found"
        Else
            .Offset(0, I - 1).Resize(1, 1).Select
        End If
    End With
End Sub

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[Sandman1985]

Perhaps something like this.

Sub Search()
    Dim FindS As String
    Dim FindD As Date, I As Long
    Dim VA As Variant
    Dim Found As Boolean
  
    FindS = InputBox("Enter the date you want to search", "Required Date Format: mm/dd/yyy")
  
    If FindS = "" Then
        Exit Sub
    End If
  
    If Not IsDate(FindS) Then
        MsgBox "Error: '" & FindS & "' is not a valid date. " & vbCrLf _
        & "", vbOKOnly Or vbCritical, "Invalid Date"
        Exit Sub
    End If
  
    FindD = DateValue(FindS)
    FindS = Format(FindD, "m/d/yyyy")
  
    With Sheets("Scheduler View").Range("G8:EAI8")
        .NumberFormat = "m/d/yyyy" 'standardize format
        VA = .Value 'load array
      
        'search array
        Found = False
        For I = 1 To UBound(VA, 2)
            If VA(1, I) = FindS Then
                Found = True
                Exit For
            End If
        Next I
      
        'results
        If Not Found Then
            MsgBox FindS & " not found"
        Else
            .Offset(0, I - 1).Resize(1, 1).Select
        End If
    End With
End Sub

(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

Thank you so much rlv01. Worked much better than what I was trying to do!
Appreciate it.