CODEWITHSUNDEEP
excelvlookupvba
Aanshi
Aanshi

Reputation: 93

EXCEL VBA -Getting #NAME?

I am trying to get user' id fro another excel file. On Main excel file there is only one column with username. I wrote below but it return #Name? instead of id. 

Dim i As Integer
Dim LastRow As Integer
Dim LastColumn As Integer
Dim Client_id As Variant
Dim user_id As String
Dim Contract_id As Variant


Sub TestAdd()
LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

'Next

For i = 2 To LastRow
user_id = "=VLOOKUP(Range(Cells(i, 3)),[RefUser.xlsx]Sheet1!$A:$B,2,FALSE)"
Range(Cells(i, 2), Cells(LastRow, 2)).Value = user_id
Next

End Sub

Upvotes: 0

Views: 38

Answers (2)

IvenBach
IvenBach

Reputation: 583

To use VLookup you need to provide some values to the function. The signature looks like VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).

  • lookup_value is the value you are expecting to find.
  • table_array is the range of cells that has the lookup_value in the leftmost column.
  • col_index_num is the column number that contains the information you want to return when a match is found.
  • range_lookup is the match that you're looking for. 0 can be used as a shorthand for False.

Assuming your lookup_value is in cell C2 which corresponds to Cells(i,3) as i starts at 2, and your table_array is in Range("M1:N10"). You can use the formula =VLOOKUP(C2,$M$1:$N$10,2,0). By omitting the $ and leaving C2 as a relative reference you can apply it to the range you want the values to be calculated. This is much more efficient then looping through and applying the the same formula to each cell individually.

Sub AddLookupFormula()
    Dim lastRow As Long
    lastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

    Dim formulaRange As Range
    Set formulaRange = ActiveSheet.Range(ActiveSheet.Cells(2, "B"), ActiveSheet.Cells(lastRow, "B"))

    Dim firstLookupCell As String
    firstLookupCell = formulaRange.Cells(1, 1).Offset(ColumnOffset:=1).Address(False, False)

    Dim completedFormula As String
    completedFormula = "=VLOOKUP(" & firstLookupCell & ",$M$1:$N$10,2,0)"
    formulaRange.Formula = completedFormula
End Sub

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166391

This is not valid as a formula:

user_id = "=VLOOKUP(Range(Cells(i, 3)),[RefUser.xlsx]Sheet1!$A:$B,2,FALSE)"

Something like this would work:

user_id = "=VLOOKUP(C" & i & ",[RefUser.xlsx]Sheet1!$A:$B,2,FALSE)"

Then set that using .Formula and not .Value

You could set all these in one shot though. Try this:

Dim i As Integer
Dim LastRow As Integer

Sub TestAdd()

    With Worksheets("Sheet1")
        .Range("B2", .Cells(.Rows.Count, 1).End(xlUp).Offset(0,1)).Formula = _
             "=VLOOKUP(C2,[RefUser.xlsx]Sheet1!$A:$B,2,FALSE)"

    End With

End Sub

Upvotes: 1

Related Questions