Find a date in other sheet

Question

I extract a date from sheet "A" to find in a sheet "B".

I have the same type of date in each sheet (type 7) and the date exists.

With the code below I have

Error 91 : Object variable or With undefined block variable

Sub SearchDate()
    lastColTraining = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
    lastLetterTraining = Split(Cells(1, lastColTraining).Address, "$")(1)
    Set allTraining = Range("K3:" & lastLetterTraining & "7")
    For Each training In allTraining.Columns
        trainingDate = training.Rows(4)
        With Worksheets("B")
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            allDate = .Range("A2:A" & lastRow)
            firstRowDate = allDate.Find(What:=trainingDate, After:=.Range("A" & lastRow)).Row
        End With
    Next training
End Sub

I scoured many forums and tried different solutions without finding an answer.

If I understand your response, I would have to add .value to trainingDate = training.Rows (4)"? — Christophe C., Nov 20 '19 at 10:16
Which line errors? You are probably not finding the search term. — SJR, Nov 20 '19 at 10:21
`allDate = .Range("A2:A" & lastRow)` should be `Set allDate = .Range("A2:A" & lastRow)` — Siddharth Rout, Nov 20 '19 at 10:24
Indeed, the error is located at the line firstRowDate = .... Yet the date sought is present in the sheet "B" — Christophe C., Nov 20 '19 at 10:24
That is becuase it could not find the relevant cell. You need to check if the `.Find` was successful before finding the row using `If Not FoundCell is nothing` before using `FoundCell.Row` You may want to see [THIS](http://www.siddharthrout.com/index.php/2018/01/05/find-and-findnext-in-excel-vba/) — Siddharth Rout, Nov 20 '19 at 10:26

JvdV · Answer 1 · 2019-11-20T10:44:58.243

There are a few things going wrong here:

First: Declare your variables, it's even best to use Option Explicit on top of your module to actually make you not forget any. Otherwise VBA will try to make an educated guess which will be a Variant type of the date type.

Second I would try to avoid ActiveSheet but instead use a CodeName. For example Sheet1.Range("..."). This post on SO can clarify a thing or two on this matter.

Third, UsedRange is not the most reliable way to return a last used column. Instead I would go with something like:

With Sheet1 'The explicit sheet reference from the first point
    lastColTraining = .Cells(1, .Columns.Count).End(xlToLeft).Column
End with

Fourth: You don't really need the column letter to refer to the column. There are other ways, for example using .Cells within a range. You could use:

With Sheet1 'The explicit sheet reference from the first point
    lastColTraining = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set allTraining = .Range(.Cells(3,11),.Cells(lastColTraining,11))`
End with

Fifth: If you have a Range object, you want to (most likely) Set it as a Range object. Otherwise (as per my first point) Excel will make an educated guess and in your case will return an array when you write: allDate = .Range("A2:A" & lastRow), instead use: Set allDate = .Range("A2:A" & lastRow)

Sixth: As per @SiddharthRout his comment, you'll recieve an error once your value isn't found. You can test that first trying to Set a FoundRange and check if it's not nothing.

Considering all the above, your code would run smoother using:

Option Explicit

Sub SearchDate()

Dim lastColTraining As Long, lastRow As Long, firstRowDate
Dim allTraining As Range, training As Range, allDate As Range, FoundCell As Range
Dim trainingDate As Variant

With Sheet1 'Change according to your sheets CodeName
    lastColTraining = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set allTraining = .Range(.Cells(3, 11), .Cells(7, lastColTraining))
    For Each training In allTraining.Columns
        trainingDate = training.Rows(4)
        With Worksheets("B")
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
            Set allDate = .Range("A2:A" & lastRow)
            Set FoundCell = allDate.Find(What:=trainingDate, AFter:=.Range("A" & lastRow))
            If Not FoundCell Is Nothing Then firstRowDate = FoundCell.Row
        End With
   Next training
End With

End Sub

I'm just not sure what you want with trainingDate = training.Rows(4). If you just interested in the 7th row of each column, then refer to that Range instead. Neither am I sure what your goal is with the code, but hopefully you can get it to work now.

Nicely explained. One more thing `firstRowDate = allDate.Find(What:=trainingDate, After:=.Range("A" & lastRow)).Row` will give error if the `.Find` doesn't return a range object — Siddharth Rout, Nov 20 '19 at 10:34
Good addition @SiddharthRout. Didn't think of that at first since OP said his lookup value is 100% surely there. But it most likely isn't (in the form/data type) he is expecting it to be in — JvdV, Nov 20 '19 at 10:46
Thank you for all your advice. I just applied but the problem is still not solved. — Christophe C., Nov 20 '19 at 15:57

Find a date in other sheet

1 Answers1