Styjun

I am looking to return a department value based off a name value. The name list is imported from a third party vendor, which randomizes the order each time, so I cannot set a department value to a specified cell. I have a range of data (dim rng) which has a name and column A and a department in column B. I would like the department associated with the name to be returned with the same on the new sheet. I have attached my code but am getting application defined or object-defined error and I am not sure why.

Sub find()

Dim namerng As Range
Dim rng As Range
Dim depti As Integer
Dim dept As String

Set namerng = Range("A1:A250") 'range of names
Set rng = Sheet1.Range("A1:B250") 'range of names in col A and dept in col B

 For depti = 3 To 250
 dept = "=IfError(Vlookup(namerng,rng,2,FALSE), "")"
 Range("B" & depti) = dept

 Next

End Sub

It looks like you're missing some code to try and fulfill what you're wanting, so some of the below is an assumption.

Sub find()

 Dim namerng As Range 'List of Randomly Organized Names Only
 Dim Lookuprng As Range 'List of Names and Departments
 Dim sh1 As Worksheet
 Dim sh2 As Worksheet

 Set sh1 = ThisWorkbook.Worksheets(1) 'List of Randomly Organized Names Only
 Set sh2 = ThisWorkbook.Worksheets(2) 'List of Names and Departments

 'List of Names and Departments
 sh1.Activate
 Set Lookuprng = sh1.Range(Cells(1, 1), (Cells(Rows.Count, 2).End(xlUp)))

 'List of Randomly Organized Names Only ******NEED TO SPECIFY WHERE THESE NAMES ARE COMING FROM, I used Sheet1
 sh2.Activate
 Set namerng = sh2.Range(Cells(1, 1), (Cells(Rows.Count, 1).End(xlUp)))

 'Put correct department with name
 namerng.Offset(0, 1) = Application.VLookup(namerng, Lookuprng, 2, False)

End Sub

Based on your description of the issue and the problem you want to solve, I believe that the following code should work as needed.

Sub find()

Dim rng As Range
Dim depti As Integer
Dim dept As String

Set rng = Sheet1.Range("A1:B250") 'range of names in col A and dept in col B

 For depti = 3 To 250
 dept = "=IfError(Vlookup(A" & depti & ",'" & Sheet1.Name & "'!" & rng.Address & ",2,FALSE), """")"
 Range("B" & depti) = dept
 Next

End Sub

Notes

I eliminated the namerng variable, because we don't need it for the updated equation. Since VLookup only takes a single value as the first param, that value should be dependent on the value of the loop variable, same as the destination range is.

I also escaped the quotation marks, so that the function would be valid, and added in the sheet1.name, so that the range address will point to the correct sheet (with single quotation marks, in case the sheet name has a space in it).

A cautionary note: the way this is coded, the equation will be inserted on whichever sheet is the active one at the time of the code run. This means that if Sheet1 is active, the departments will get overwritten with a circular-formula.