0

I have a problem with my code. In short, I want to go through some tables which are filled with formulas and have the code do a hard copy of the day specified by the HelpDay variable.

Dim countTab As Long
Dim HelpDay As Date
Dim i As Long
------------------------------------------------------
Sub DailyData()

Application.ScreenUpdating = False

Sheets("Input").Select
countTab = Range("D34").Value  'sets the amount of loops
HelpDay = Range("D5").Value    'sets the day

Sheets("InputData").Select
Range("A1").Select

For i = 0 To countTab
Cells.Find(What:=HelpDay, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 3).Activate
Range(ActiveCell, ActiveCell.Offset(0, 19)).Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Next
Sheets("Input").Select
MsgBox "All daily data saved in the tables. In case of incorrect entry, either open the old version and start again or adjust manually in the tables."

End Sub

When I try to run this code i get a "Runtime Error 91" and the debugger shows the cells.find as the problem.

However, if i add

MsgBox "Tables copied: " & i

just at the end of the loop suddendly the runtime error doesn´t appear anymore (but now i have to click the Ok-Button after each loop).

I already checked the formatting of all effected cells and tried some of the solutions in other threads but I cant get the code to work properly.

Please let me know if you have an idea for me to try.

Cheers

Razz
  • 1
  • 1
  • 1
    Asked numerous times on SO: If `Find` doesn't find anything, it returns `Nothing`, and you can't do anything (eg use `Activate`) on `Nothing`. See for example https://stackoverflow.com/questions/29878964/find-dynamic-variable-code-giving-runtime-error-91. Btw: You should avoid to use `Activate` and `Select`, see https://stackoverflow.com/q/10714251/7599798 – FunThomas Mar 25 '22 at 11:55
  • @FunThomas Thanks for the comments, but sadly it does not answer my question. While I do get why the error appears I still do not understand how it can return `Nothing`. In each table there is the excact Value as `HelpDay`. So why does the code suddenly state that it can not find the value. I should also add that the code does seem to start as intended, but after the 5th loop it returns the error. – Razz Mar 25 '22 at 13:12
  • 1
    `Find` returns the cell (as a Range) where it found your value. If nothing is found, it returns `Nothing` - it's as simple as that. If it returns Nothing, it didn't find anything. You need to check the logic of your code and your data. – FunThomas Mar 25 '22 at 13:21
  • Probably should be using `Find/FindNext` instead of your current loop setup. – BigBen Mar 25 '22 at 13:35
  • Alright, I seem to have found a solution. Instead of going through a loop and searching in each table I just let the code find the correct value in the first table (since this is somehow done reliably) and just copy and paste the entire row using `ActiveCell.EntireRow.Copy` and my already existing "paste" code. This might not be the most elegant solution, but it reliably produces the expected result. @FunThomas Thanks again for your comments. Cheers ~ – Razz Mar 25 '22 at 13:42

0 Answers0