0

I am receiving an Error 91 "Object variable or With block variable not set" when using .Find(What:-.

I want to find the column index number in the "overview" sheet by seaching for the value in Cells(2,2) from the "dailysheet".

I get the error on lnCol = line. I think it something to do with the formatting or setting the "checkdate" variable.

Any help would be great appreciated!

Sub checkingdate_Click()

Dim overview As Worksheet
Dim dailysheet As Worksheet
Dim datecheck As Range
Dim checkdate As Date
Dim lnRow As Long
Dim lnCol As Long

Set overview = ThisWorkbook.Worksheets("overview")
Set dailysheet = ThisWorkbook.Worksheets("dailysheet")
Set datecheck = dailysheet.Cells(2, 2)

lnRow = 5

overview.Rows("5").EntireRow.Hidden = False 'Adjust potentially

With datecheck
    .NumberFormat = "dd/mm/yyyy"
    '.NumberFormat = "@"
End With

With overview.Rows("5")
    .NumberFormat = "dd/mm/yyyy"
    '.NumberFormat = "@"
End With

checkdate = dailysheet.Cells(2, 2).Value

MsgBox datecheck.Value
MsgBox checkdate

lnCol = overview.Cells(lnRow, 1).EntireRow.Find(What:=checkdate, LookIn:=xlValues, LookAt:=xlWhole,        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

MsgBox lnCol

'=====
' END
'=====
overview.Rows("5").EntireRow.Hidden = True 'Adjust potentially

With overview.Rows("5")
    .NumberFormat = "dd"
End With

With overview.Columns("B:ABO")
    .ColumnWidth = 4.57
End With

End Sub

EDIT: Found a solution by changing the variable checkdate to dailysheet.Cells(2,2).Formula rather than .Values and changed to LookIn:=xlFormulas rather than LookIn:=xlValues. I also changed the .NumberFormat of overview.Rows("5"), so that the dates became serial numbers, thereby becoming searchable from the serial number from "checkdate".

Edited portion of the code is below:

With overview.Rows("5")
    '.NumberFormat = "dd/mm/yyyy"
    .NumberFormat = "@"
End With

checkdate = dailysheet.Cells(2, 2).Formula

MsgBox datecheck.Value
MsgBox checkdate

lnCol = overview.Cells(lnRow, 1).EntireRow.Find(What:=checkdate,      LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
Xequ
  • 1
  • 1
  • What is the value of checkdate when it gets to this line and does that date exist in the row you're looking in? – Dave Oct 10 '19 at 11:51
  • Hi @Dave It is 09/01/2020 - which is the correct date that I am testing on- Type: Date – Xequ Oct 10 '19 at 11:54
  • You'll find a similar thing here https://stackoverflow.com/questions/11917841/vba-error-object-variable-or-with-variable-not-set If you are sure your date exists in the row you are searching, double-check that your local date format is not switching days/months – Dave Oct 10 '19 at 11:58
  • @Dave I think that this may be the problem, but I am unsure how to solve it... – Xequ Oct 10 '19 at 12:16
  • Try `LookIn:=xlFormulas` instead. – Domenic Oct 10 '19 at 13:31
  • Thanks @Domenic It has worked! I have now edited my question with my solution. I also changed the row of dates I am searching within to text so that they would become serial numbers. – Xequ Oct 10 '19 at 13:43

1 Answers1

0

Found a solution by changing the variable checkdate to dailysheet.Cells(2,2).Formula rather than .Values and changed to LookIn:=xlFormulas rather than LookIn:=xlValues. I also changed the .NumberFormat of overview.Rows("5"), so that the dates became serial numbers, thereby becoming searchable from the serial number from "checkdate".

Edited portion of the code is below:

With overview.Rows("5")
    '.NumberFormat = "dd/mm/yyyy"
    .NumberFormat = "@"
End With

checkdate = dailysheet.Cells(2, 2).Formula

MsgBox datecheck.Value
MsgBox checkdate

lnCol = overview.Cells(lnRow, 1).EntireRow.Find(What:=checkdate,      LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
Xequ
  • 1
  • 1