+ Reply to Thread
Results 1 to 2 of 2

Range Failed Error for Find()

Hybrid View

  1. #1
    Registered User
    Join Date
    05-28-2015
    Location
    Houston, TX
    MS-Off Ver
    2010
    Posts
    7

    Range Failed Error for Find()

    Hello all,

    I am getting an error with my code: 1004 Method 'Range' of object '_Worksheet' failed at the lines indicated below. The code still doesn't work when I set j = 11 & k = 40, but the code DOES work if I set the range to A11:A40. Any ideas as how to remedy this? I need to be able to change a lot of variables with the macro, including the range being searched, which is why I have a mess of ranges specified as cells.

    Points for helping!

    Sub Macro1()
        Application.ScreenUpdating = False
        
        Dim i, j, k, m, n As Integer
        Dim wb1, wb2 As Workbook
        Dim ws1a, ws1b, ws2 As Worksheet
        Dim Txt As String
        Dim s, t As Range
        
        Set wb1 = ThisWorkbook
        Set ws1a = wb1.Sheets("Data Locations")
        Set ws1b = wb1.Sheets(ws1a.Cells(8, 2).Value)
        Set wb2 = Workbooks(ws1a.Cells(3, 2).Value)
        Set ws2 = wb2.Sheets(ws1a.Cells(4, 2).Value)
        j = ws1a.Cells(13, 3).Value
    '    j = 11
        k = ws1a.Cells(13, 4).Value
    '    k = 40
        m = ws1a.Cells(11, 4).Value
        n = ws1a.Cells(15, 4).Value
    
        For i = 2 To 100
            Application.Goto ws1b.Cells(i, 1)
            Txt = ActiveCell.Value
    '        Set s = ws2.Range("A11:A40").Find(Txt)
            Set s = ws2.Range(Cells(j, 1), Cells(k, 1)).Find(Txt)  <----
                If s Is Nothing Then
                Else
                    Application.Goto ws2.Range(Cells(j, 1), Cells(k, 1)) <----
    '                Application.Goto ws2.Range("A11:A40")
    '                ws2.Range("A11:A40").Find(Txt).Activate
                    ws2.Range(Cells(j, 1), Cells(k, 1)).Find(Txt).Activate <----
                    Set t = ActiveCell.Offset(0, m - 1)
                    Application.Goto ws1b.Cells(i, n)
                    ws1b.Cells(i, n).Value = t.Value
                End If
        Next i
        Application.Goto ws1b.Cells(2, n)
        Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Range Failed Error for Find()

    You need a worksheet reference for Cells.
            Set s = ws2.Range(ws2.Cells(j, 1), ws2.Cells(k, 1)).Find(Txt)
    If posting code please use code tags, see here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 11-15-2013, 05:03 PM
  2. VBA Code...error = run time error 1004 autofilter method of range class failed
    By Dariusd7 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2013, 04:49 PM
  3. [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
  4. "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
  5. Range Class Failed Error
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2012, 04:21 PM
  6. [SOLVED] Name a dynamic range - Run-time error 1004 Method 'Range' of object'_Worksheet' failed
    By DavidBW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2012, 08:00 AM
  7. Find Info code giving object failed error
    By Leah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2009, 04:24 PM
  8. Range Question / error 1004: method Range of object Worksheet has failed
    By Paul in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2005, 10:06 AM

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