1

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Jacco9205
  • 11
  • 1
  • 1
    Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary that you show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading [ask] might help you to improve your question. – Pᴇʜ May 21 '21 at 09:45
  • Please do some resach on `Range.Find` or `Application.WorksheetFunction.Match` and show your attmpet solving your issue. – Pᴇʜ May 21 '21 at 09:46
  • Thanks! I'm very new to this VBA and I already looked for some solutions but I didn't find any yet. This is what I 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``` – Jacco9205 May 21 '21 at 10:01
  • Please always put your code into the question itself [edit]. Because in comments they are not really readable. Also what went wrong with your code? – Pᴇʜ May 21 '21 at 10:03
  • Thanks! I'm new to this forum so thanks for letting me know! – Jacco9205 May 21 '21 at 10:46

1 Answers1

2

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 1
    nicely done... Just one thing. It is advisable to use all relevant parameters of `.Find`. Excel has an uncanny habit of using the previous settings when using `.Find` :D – Siddharth Rout May 21 '21 at 10:27
  • I tried this but I get the message "date not found". The date in ```Sheets("Rekenblad").Range("B29")``` is the same as in ```Sheets("archief").Range("G1")``` – Jacco9205 May 21 '21 at 10:51
  • @SiddharthRout Correct, I didn't see it. – Pᴇʜ May 21 '21 at 11:21
  • @Jacco9205 See my edit. And check the parameters of `.Find`. – Pᴇʜ May 21 '21 at 11:25