0

I am currently struggling with VBA Run-Time Error 91 "Object variable or With block variable not set". I have checked the answers to the other questions on this topic and also checked the Microsoft Docs page. The main error appears to be not having set an object. I checked and as far as I can see have set all my objects.

I am trying to copy data from one sheet into a newly created one. For this, I also check what is the first free row in the newly created sheet, as I am running this in a loop.

Sub CopyData()

Dim wbNewWorkbook As Workbook
Dim intFreeRow As Integer

'Open a new workbook
Set wbNewWorkbook = Workbooks.Add
DoEvents

Debug.Print wbNewWorkbook.Name

'Find first free row NewWorkbook
intFreeRow = wbNewWorkbook.Worksheets(1).Cells.Find _
    (What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlRows) _
    .Row + 1
DoEvents

'More code here
End Sub

I get the error message when when trying to find the first free row.

The Debug.Print provides me with the name "Book 1", therefore I believe the 'wbNewWorkbook' should have been successfully set. Hence, I do not understand why I get the error message.

My suspicion is that the Cells.Find returns 0 as the newly created workbook is still new and therefore row 1 should be free. However, as I am running this in a loop, I do not just want to set the FreeRow to 1, but change it with each iteration of the loop. I also don't understand why this would trigger Run-Time Error 91.

Any help in where I am going wrong is massively appreciated.

lcd257
  • 1
  • See the use of `Application.WorksheetFunction.CountA(.Cells) <> 0 Then` in the accepted answer to the duplicate target. Also, don't use `Integer`, use `Long`. – BigBen Apr 07 '20 at 14:24
  • its' because there is no `Range` caught by `Find`. But since `wbNewWorkbook` is a brand new workbook, all its sheets will have row 1 as their first free one, hence just set `intFreeRow = 1` – HTH Apr 07 '20 at 14:24

0 Answers0