CODEWITHSUNDEEP
vbaexcel
TonyP
TonyP

Reputation: 333

Object required error in Set =

I am trying to build some code that inserts a name and email address in to the next open row from the next row in the loop. I am getting an error in Set = Number of Emails line and I am not sure why. 

Sub Email_Copy()
    Dim Data As Worksheet
        Set Data = ActiveWorkbook.Sheets("Data")
    Dim Email_Database As Worksheet
        Set Email_Database = ActiveWorkbook.Sheets("Email_Database")
    Dim Number_Of_Emails As Integer
        Set Number_Of_Emails = Data.Range("L6002")
    Dim Total_Emails As Long
        Set Total_Emails = Email_Database.Range("D2")
    Dim X As Long

    For X = 1 To Number_Of_Emails
     Set Email_Database.Range("B3").Offset(Total_Emails, 0) = Data.Range("D3").Offset(Number_Of_Emails, 0)

End Sub

Upvotes: 0

Views: 687

Answers (1)

findwindow
findwindow

Reputation: 3153

You declared Number_Of_Emails As Integer but then tried to set it to a range so it's a type mismatch. You can simply do

Number_Of_Emails = Data.Range("L6002").value

Edit: to plagarize Scott Holtzman's comment verbatim

Set is only required with Object Type variables (hence the error). A Range variable is an Object Type. An Integer, being a number, is not an object type. (String is not on object either). Worksheet is an Object Type.

Upvotes: 2

Related Questions