CODEWITHSUNDEEP
excelvba
Jacco9205
Jacco9205

Reputation: 11

Find and select cell with a certain date in a row with dates

I'm trying to find and select a cell in a row of dates in a worksheet called archief. The range of the row with dates is Sheets("archief").Range("B2:HT2"). The input for the date I'm looking for is in Sheets("Rekenblad").Range("B29").

After finding the cell in the row of dates, I need to select the cell 1 beneath it. So for example the date I'm looking for is in B2, then I need to select B3.

After that I want to paste something that I copied before.

How can I do this using VBA? This is the code that I have tried so far.

Sub FindCell()
    Dim RowDates As Range
    Dim InputCell As Range
    Set InputCell = Sheets("Rekenblad").Range("B29")
    Set RowDates = Sheets("archief").Range("B2:HT2").Find(What:=InputCell, _
                                                    SearchOrder:=xlByColumns).Select
End Sub

Upvotes: 1

Views: 742

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You almost had it you just need to remove the .Select.

You might benefit from reading How to avoid using Select in Excel VBA.

Sub FindCell()
    Dim InputCell As Range
    Set InputCell = Sheets("Rekenblad").Range("B29")
    
    Dim RowDates As Range
    Set RowDates = Sheets("archief").Range("B2:HT2").Find(What:=InputCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchByte:=False)

    If RowDates Is Nothing Then ' check if find did find something
        MsgBox "Date not found."
        Exit Sub 'nothing was found so we need to abort
    End If
    
    Dim NextDateCell As Range
    Set NextDateCell = RowDates.Offset(RowOffset:=1) 'offset moves from RowDates one down

End Sub

Don't ever use Select unless you want the user to move there.

When using the Range.Find method make sure you specify at least the following parameters:

The settings for LookIn, LookAt, SearchOrder, and MatchByteare saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Upvotes: 2

Related Questions