CODEWITHSUNDEEP
vbaexcelexceptionruntime-error
1up
1up

Reputation: 116

Run-time error '424' Object required

Writing a Macro to run a loop which runs a VLOOKUP across each cell in an column. I'm getting a "Run-time error '424' Object required" error but can't pin down the main issue. Any insight or help is appreciated.

Two Issues: * Run-time error '424' Object required * Loop continues past last cell with Data until wrap-around or Excel Limit is hit before stopping. Using the dot operators for .ActiveCell and .Value aren't helping. enter image description here

Sub New_contact_info()

serverName = Cells.Range("AS:AS")
contactInfo = Application.WorksheetFunction.VLookup(serverName, Worksheets("All Active Assets").Range("A:C"), 3, False)

Cells.Range("AM:AM") = contactInfo

For Each cell In serverName
If serverName <> "" Then
serverName.ValueOffset(0, -5) = contactInfo
End If

Next cell

End Sub

Upvotes: 1

Views: 1527

Answers (1)

TBiel
TBiel

Reputation: 46

You have to set the range serverName using the "Set" keyword. Give this a try: 

Sub New_contact_info()
  dim serverName as Range

  set serverName = Cells.Range("AS:AS")
  contactInfo = Application.WorksheetFunction.VLookup(serverName,  Worksheets("All Active Assets").Range("A:C"), 3, False)

  Cells.Range("AM:AM").value = contactInfo

  For Each cell In serverName
    If serverName.value <> "" Then
      serverName.Value.Offset(0, -5) = contactInfo
    End If

  Next cell

End Sub

Upvotes: 1

Related Questions