Creating a loop with an IF variable -- Run-time error '1004'

Question

I'm trying to create a loop based on data in Column L. Data in Column L is formatted as Text and contains dates. If a match is found, the entire Row has to be highlighted in yellow.

Sub Forn()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Dim strSearchText As String
    strSearchText = Format(DateAdd("m", 2, Now()), "yyyymm") 
    Dim rngSearchArea As Range
    Set rngSearchArea = ws.Range(Range("L10"), ws.Range("L" & ws.Range("L:L").Cells.Count).End(xlUp))
    
    Dim strFirstFound As String
    Dim rngCurrentFound As Range
    Set rngCurrentFound = ws.Range("L10")
    Set rngCurrentFound = rngSearchArea.Find(What:=strSearchText, After:=rngCurrentFound, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=True, SearchFormat:=False)
            
             If rngCurrentFound Is Nothing Then
        MsgBox "INGEN TREFF"
        Exit Sub
    End If
    
    rngCurrentFound.Resize(1, 16).Offset(0, -11).Interior.ColorIndex = 6
    
    strFirstFound = rngCurrentFound.Address

Dim rngSource As Range
Dim rngNextFound As Range

Do
     Set rngNextFound = rngSearchArea.FindNext(rngCurrentFound)
     If rngNextFound.Row > rngCurrentFound.Row Then
        rngCurrentFound.Resize(1, 16).Offset(0, -11).Interior.ColorIndex = 6
    Else
         Set rngSource = ws.Range(rngCurrentFound, ws.UsedRange.Cells(ws.UsedRange.Cells.Count))
         
    End If

    Set rngCurrentFound = rngSearchArea.FindNext(rngCurrentFound)
    
Loop While rngCurrentFound.Address <> strFirstFound

End Sub

I'm getting Run-time error '1004': Method 'Range' of object '_Worksheet' failed.

This exact same code worked for me yesterday and I'm at a complete loss as to what needs to be changed here.

UPD: the error is caused by launching the Macro from PERSONAL.XLSB --

Set ws = ThisWorkbook.Worksheets(1)

no longer works as needed.

Set rngSearchArea = ws.Range(Range("L10"), ws.Range("L" & ws.Range("L:L").Cells.Count).End(xlUp)) — smgrd
– smgrd, Commented Aug 6, 2020 at 11:34
Have you changed the order of sheets? You're looking at Worksheets(1) rather than a named sheet. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Aug 6, 2020 at 11:34
ThisWorkbook is the Workbook where the code lives. If you move code to Personal, it will access the first sheet of Personal - which probably don't exists. — FunThomas
– FunThomas, Commented Aug 6, 2020 at 11:49
Your missing a ws. It should be this: Set rngSearchArea = ws.Range(ws.Range("L10"), ws.Range("L" & ws.Range("L:L").Cells.Count).End(xlUp)). You forgot to qualify the worksheet for Range("L10") — MGP
– MGP, Commented Aug 6, 2020 at 11:51

FunThomas · Accepted Answer · 2020-08-06 12:17:37Z

3

Using ThisWorkbook in a macro tells VBA that you want to access the workbook where the code is stored. If you move your code to the Personal.xlsb, it will access Personal.xlsb and it's sheets - and that's probably not what you want.

You put a macro in the Personal usually to have it available all the time, e.g. via a Keyboard shortcut.

If you want the code to work on the currently visible workbook, replace ThisWorkbook simply by ActiveWorkbook - but you need to ensure that it makes sense to run the macro on that workbook.

If you want to execute it always on the same workbook, it makes no sense to have it in the personal - just keep it in the workbook itself.

If you want to select the workbook first, add a fileopen-dialog to your code, open the workbook using Workbooks.Open and use the workbook-reference returned by the open-command.

answered Aug 6, 2020 at 12:17

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a loop with an IF variable -- Run-time error '1004'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related