Error 1004 setting up a range

[Pyrgos]

Hi all. I cannot understand what is wrong here.

Dim Counter As Long
Dim Counter2 As Long
Dim AssociateList As Range


'Check Number of Lines with Reports Filled


Counter = 1
Do Until ThisWorkbook.Sheets("Tracker").Cells(Counter, 2).Value = ""
Counter = Counter + 1
Loop


'Check Number of Lines in Associates' List
Counter2 = 2
Do Until ThisWorkbook.Sheets("Lists").Cells(Counter2, 1).Value = ""
Counter2 = Counter2 + 1
Loop


Set AssociateList = ThisWorkbook.Sheets("Lists").Range(Cells(2, 1), Cells(Counter2, 2)) 'ERROR 1004
AssociateList = ThisWorkbook.Sheets("Lists").Range(Cells(2, 1), Cells(Counter2, 2))
    
If TrackForm.OptionApproved = True Then
yadda yadda

I already tried several other solutions. Could't find anything. I suspect I am missing something obvious, but after so much searching, could not realise what. Any help please?

 

[Tom Urtis]

The problem resulted from an incomplete reference to the specified parent sheet object you had in mind.

Try substituting this

Set AssociateList = ThisWorkbook.Sheets("Lists").Range(Cells(2, 1), Cells(Counter2, 2))

with this:

With ThisWorkbook.Sheets("Lists")
Set AssociateList = .Range(.Cells(2, 1), .Cells(Counter2, 2))
End With


and also, substitute this:

AssociateList = ThisWorkbook.Sheets("Lists").Range(Cells(2, 1), Cells(Counter2, 2))


with this:

With ThisWorkbook.Sheets("Lists")
AssociateList = .Range(.Cells(2, 1), .Cells(Counter2, 2))
End With


Those adjustments worked for me. As an aside, there are more efficient ways to identify a last row, but let's first get this error solved, which I think will be with the suggestions per above.

 

[Pyrgos]

Thanks for your help Tom.

The way I set it now, it erases the values from AssociateList range. When I get to the Vlookup, Excel simply has nothing to look upon and generates another run-time error 1004. Any ideas?

I also changed the code for the end of list. I used the other because of old habits. I am very open for any code tweaking though!

Private Sub ApproveReject_Click()
'***********************************
'***   TRACKS REVIEW Q E-MAIL    ***
'***    SENDS E-MAIL TO SUP(?)   ***
'***********************************
Dim Counter As Long
Dim Counter2 As Long
Dim AssociateList As Range


'Check Number of Lines with Reports Filled


ThisWorkbook.Sheets("Tracker").Activate
ThisWorkbook.Sheets("Tracker").Range("B1").Select
Counter = ThisWorkbook.Sheets("Tracker").Range(Selection, Selection.End(xlDown)).Count


ThisWorkbook.Sheets("Lists").Activate
ThisWorkbook.Sheets("Lists").Range("A1").Select
Counter2 = ThisWorkbook.Sheets("Lists").Range(Selection, Selection.End(xlDown)).Count


With ThisWorkbook.Sheets("Lists")
Set AssociateList = .Range(Cells(2, 1), Cells(Counter2, 2))
End With


With ThisWorkbook.Sheets("Lists")
AssociateList = .Range(Cells(2, 1), Cells(Counter2, 2))
End With


If TrackForm.OptionApproved = True Then
    With ThisWorkbook.Sheets("Tracker")
        .Cells(Counter, 2).Value = TrackForm.AssociateBox.Value
        .Cells(Counter, 6).Value = TrackForm.CaseIDBox.Value
    End With
        ThisWorkbook.Sheets("Tracker").Cells(Counter, 3).Value = Application.WorksheetFunction.VLookup(TrackForm.AssociateBox.Value, AssociateList, 2, False)
... and so on

 

[Pyrgos]

Found a solution after more investigation. Apparently it had to do with declaring a range.

I replaced Dim AssociateList As Range by Dim AssociateList As Variant. I removed

With ThisWorkbook.Sheets("Lists")
Set AssociateList = .Range(Cells(2, 1), Cells(Counter2, 2))
End With

It worked!

 

[Jonmo1]

You didn't exactly follow Tom's suggestion.
Look Very Closely at the difference between what Tom posted and what you posted after Tom's post.

With ThisWorkbook.Sheets("Lists")
    Set AssociateList = .Range(.Cells(2, 1), .Cells(Counter2, 2))
End With
 
With ThisWorkbook.Sheets("Lists")
    AssociateList = .Range(.Cells(2, 1), .Cells(Counter2, 2))
End With

With ThisWorkbook.Sheets("Lists")
    Set AssociateList = .Range(Cells(2, 1), Cells(Counter2, 2))
End With
 
With ThisWorkbook.Sheets("Lists")
    AssociateList = .Range(Cells(2, 1), Cells(Counter2, 2))
End With

 

[Pyrgos]

I get you! Thanks very much for that, I really appreciate it, jonmo1. Part of the "hands on" learning curve, I don't like copy & pasting. That way I learn better!

Let me know if you see anything worth improving. I do value feedback and corrections! :D