0

I am trying to find the cell using the formula below but it errors out. The data is located on ws1 sheet and I have the run macro button on the Engine sheet. If I run the macro from the module while I have ws1 selected the macro runs without issue. If I run the macro using the button on the Engine sheet it errors out. I tried adding ws1.actiavte above this line of code but it does not fix the issue.

FYI C7=4/1/2022

Set myr = ws1.Range("A:A").Find(ThisWorkbook.Sheets("Engine").Range("C7").Value, after:=ws1.Cells(1, 1), searchdirection:=xlNext)

Adding LookIn:=xlFormulas resolved the issue. The range.find of a date required LookIn:=xlFormulas in order to work it appears.

Set myr = ws1.Range("A:A").Find(ThisWorkbook.Sheets("Engine").Range("C7").Value, after:=ws1.Cells(1, 1), LookIn:=xlFormulas, _ LookAt:=xlPart, searchdirection:=xlNext)

34653120
  • 95
  • 6
  • You should also specify the `LookIn` and `LookAt` parameters of `Find`. – BigBen Jul 13 '22 at 14:43
  • Also [test if the Find succeeded](https://stackoverflow.com/questions/1589939/how-to-detect-whether-vba-excel-found-something). – BigBen Jul 13 '22 at 14:44
  • I made this change and it still didnt work, additionally when i hover over myr it says nothing which i believe that the find did succeed `Set myr = ws1.Range("A:A").Find(ThisWorkbook.Sheets("Engine").Range("C7").Value, after:=ws1.Cells(1, 1), LookIn:=xlFormulas And xlValues, _ LookAt:=xlPart, searchdirection:=xlNext)` – 34653120 Jul 13 '22 at 14:47
  • `LookIn:=xlFormulas And xlValues` is not valid. Also looking up dates is tricky, see https://stackoverflow.com/a/45641402 – BigBen Jul 13 '22 at 14:54
  • 1
    Thanks BigBen, look in formulas worked and the link explains why. Much Appreciated! – 34653120 Jul 13 '22 at 15:03

1 Answers1

0

Adding LookIn:=xlFormulas resolved the issue. The range.find of a date required LookIn:=xlFormulas in order to work it appears.

Set myr = ws1.Range("A:A").Find(ThisWorkbook.Sheets("Engine").Range("C7").Value, after:=ws1.Cells(1, 1), LookIn:=xlFormulas, _ LookAt:=xlPart, searchdirection:=xlNext)

34653120
  • 95
  • 6