Results 1 to 7 of 7

Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

Threaded View

  1. #1
    Registered User
    Join Date
    04-12-2017
    Location
    Pryor, Oklahoma
    MS-Off Ver
    2016
    Posts
    7

    Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

    So I have a worksheet where, on the "Data Entry" tab one will enter in data for a specified date, and then when a button containing the macro "Uptade_Archive" is pressed it updates another tab titled "Archive." The Archive Tab has a bunch of listed dates going down Column A starting with A3 being "Jan-17" , A4 is "Feb 17" , A5 is "Mar-17" , etc. My "Data Entry" sheet has the key date in cell A14. I want my code to take the value in cell A14, copy the data that is next to it ("A14:M14"), find that same date from A14 and find the row containing that date in the "Archive" tab, then paste the data in that row. My code currently looks as follows:

    Sub Update_Archive()
    
        Dim n1 As String
        Dim n2 As String
        
        'Capturing the date for the archive entry; resides in a hidden row in the Data Entry Sheet
        n1 = Sheets("Data Entry").Range("A14").Value  'A14 is equal to B3. If B3 changes, A14 changes.
        
        'n2 is the next data entry date needed
        n2 = Sheets("Data Entry").Range("A14").Value + 1
        
        Application.ScreenUpdating = False
        
        'This action copies the hidden cells on the Data Entry sheet that contain the modified (kg->metric tons) data entered
        Sheets("Data Entry").Range("A14:M14").Copy
            
        'This block of code matches the data entry date with an archive data entry date
        Sheets("Archive").Select
        Columns("A:A").Select
        Selection.Find(What:=n1, After:=ActiveCell, LookIn:=xlValues, LookAt _
            :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
            
        'pasting the copied entry data into the archive
        ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
        Application.CutCopyMode = False
        
        ActiveWorkbook.RefreshAll
        
        'Clearing the data entry form
        Sheets("Data Entry").Select
        Range("B6:D8").Select
        Selection.ClearContents
        
        'Updating the data entry date needed
        Sheets("Data Entry").Range("B3") = n2
        
        
        Sheets("Data Entry").Range("A1").Select
        ActiveWorkbook.Save
        
        MsgBox ("Archive and Plots Updated.")
      End Sub
    Whenever I run through the code step-by-step, It stops at the Selection.Find() step and gives me "Run-time error '91': Object variable or With block variable not set"
    I'm not sure what exactly is wrong here, n1 is dimmed as a String and is set to the value of A14.

    Please help!
    Last edited by Leith Ross; 04-12-2017 at 08:00 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Runtime error '1004': Paste special method of range class error.
    By Daryl Zer0 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-05-2014, 01:44 AM
  2. [SOLVED] VBA Error: Runtime Error 1004: AutoFilter method of Range class failed
    By jl22stac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 07:27 PM
  3. Runtime error '1004' - Method 'Range' of object '_worksheet' falied
    By vijaykumarnachapalli in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2013, 02:45 PM
  4. [SOLVED] Runtime error PasteSpecial method of Range class failed
    By russ50 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2012, 02:27 AM
  5. "Runtime error '-2147417848 (80010108)' Method 'find' of object 'Range' failed"
    By excelworker_1 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 06-14-2012, 09:53 AM
  6. Runtime error .. 20028018: Method value of object range failed
    By petca059 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-11-2010, 04:40 AM
  7. [SOLVED] Object range method runtime error
    By Neal Zimm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2005, 10:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1