VBA Vlookup Troubles

[excelnoob09]

Hi, I have spent two days googling and searching old threads to no avail so I'm posting here in hopes that someone can help.
I'm trying to automate 2 vlookups using VBA. I will receive reports daily where will we have to do vlookups to pull the advisor name and program name. So I have 2 worksheets in the workbook, sheet 1 where I will paste the report and add 2 columns to pull in the advisor and program. Sheet 2 is named "CRM Report" and that's the report where the advisor and program lives, in columns 5 and 6 respectively- I named the array 'adv'. The ID is column A in Sheet 1 and sheet 2 and that's what I am using as the lookup value. So the Vlookup formula is-

=vlookup(A2, adv,5,0)n - to pull the advisor name
&
= vlookup(A2, adv,6,0) - to pull the program name


here is the first formula that did not work-

Sub Vlookup()
Range("A2") = Application.WorksheetFunction.Vlookup("A2:A", Worksheets("CRM Report").adv, 5, False)
End Sub


I also tried this-

Sub Advisor()
'
' Advisor Macro
' advisor vlookup
'


'
Dim LR As Long
Sheets("CRM Report").Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-2],adv,5,0)"
Range("O2").AutoFill Destination:=Range("B2:B" & LR)
End Sub






Dim LR As Long
Sheets("CRM Report").Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-3],adv,6,0)"
Range("O2").AutoFill Destination:=Range("C2:C" & LR)
End Sub




Thank you in advance for any help.

 

[Norie]

So you want to pull the advisor and program from the sheet 'CRM Reports' based on the ID in column A in the report sheet, 'Sheet1'?

Which column(s) are advisor and program in on 'CRM Reports'?

Is it E and F respectively?

Also, which column(s) on 'Sheet1' do you want to pull the advisor and program to?

In general the formula to get the advisor would look something like this,

=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 5, 0)

and for program.

=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 6, 0)

To add those formulas to 'Sheet1' you could use something like this.

Sub GetAdvisorAndProgram()
Dim LR As Long

    With Sheets("Sheet1")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("B2:B" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 5, 0)"    ' Change B to the column on Sheet1 where you want the advisor
        .Range("C2:C" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 6, 0)"    ' Change B to the column on Sheet1 where you want the program
    End With

End Sub

 

[excelnoob09]

Thank you for the response! yes on 'CRM Reports" the advisor and program column are E and F respectively

On 'Sheet 1' I want to pull advisor and program to columns B and C

Thank you!

So you want to pull the advisor and program from the sheet 'CRM Reports' based on the ID in column A in the report sheet, 'Sheet1'?

Which column(s) are advisor and program in on 'CRM Reports'?

Is it E and F respectively?

Also, which column(s) on 'Sheet1' do you want to pull the advisor and program to?

In general the formula to get the advisor would look something like this,

=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 5, 0)

and for program.

=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 6, 0)

To add those formulas to 'Sheet1' you could use something like this.

Sub GetAdvisorAndProgram()
Dim LR As Long

    With Sheets("Sheet1")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("B2:B" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 5, 0)"    ' Change B to the column on Sheet1 where you want the advisor
        .Range("C2:C" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 6, 0)"    ' Change B to the column on Sheet1 where you want the program
    End With

End Sub

 

[excelnoob09]

thanks so much for the formula, it's returning N/As- do I also have to reference the table array as well as the sheet? I named the table array 'adv'

So you want to pull the advisor and program from the sheet 'CRM Reports' based on the ID in column A in the report sheet, 'Sheet1'?

Which column(s) are advisor and program in on 'CRM Reports'?

Is it E and F respectively?

Also, which column(s) on 'Sheet1' do you want to pull the advisor and program to?

In general the formula to get the advisor would look something like this,

=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 5, 0)

and for program.

=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 6, 0)

To add those formulas to 'Sheet1' you could use something like this.

Sub GetAdvisorAndProgram()
Dim LR As Long

    With Sheets("Sheet1")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("B2:B" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 5, 0)"    ' Change B to the column on Sheet1 where you want the advisor
        .Range("C2:C" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A2:F1000, 6, 0)"    ' Change B to the column on Sheet1 where you want the program
    End With

End Sub

 

[Norie]

I didn't include the table array adv because I wasn't sure how you defined it.

Instead I used a standard reference, 'CRM Reports'!A2:F1000, which should work if there are matches between the IDs in column A on 'Sheet1' and 'CRM Reports'.

The only problem I can see would be if the no of rows on 'CRM Reports' exceeded 1000 or if the values in column A on the sheets don't have the same data type, e.g. one is text the other numeric.

The first problem can easily be fixed by extending the range for column A on 'CRM Reports', for example to the whole column.

Sub GetAdvisorAndProgram()
Dim LR As Long

    With Sheets("Sheet1")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("B2:B" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A:F, 5, 0)"    ' Change B to the column on Sheet1 where you want the advisor
        .Range("C2:C" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A:F, 6, 0)"    ' Change B to the column on Sheet1 where you want the program
    End With

End Sub

The second problem is kind of hard to fix without further information, e.g. what type of data do you have in column A on both sheets?

 

[excelnoob09]

I didn't include the table array adv because I wasn't sure how you defined it.

Instead I used a standard reference, 'CRM Reports'!A2:F1000, which should work if there are matches between the IDs in column A on 'Sheet1' and 'CRM Reports'.

The only problem I can see would be if the no of rows on 'CRM Reports' exceeded 1000 or if the values in column A on the sheets don't have the same data type, e.g. one is text the other numeric.

The first problem can easily be fixed by extending the range for column A on 'CRM Reports', for example to the whole column.

Sub GetAdvisorAndProgram()
Dim LR As Long

    With Sheets("Sheet1")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("B2:B" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A:F, 5, 0)"    ' Change B to the column on Sheet1 where you want the advisor
        .Range("C2:C" & LR).Formula = "=VLOOKUP(A2, 'CRM Reports'!A:F, 6, 0)"    ' Change B to the column on Sheet1 where you want the program
    End With

End Sub

The second problem is kind of hard to fix without further information, e.g. what type of data do you have in column A on both sheets?

They are both ID numbers, 10 characters, a mix of letters and numbers like GM09787022- they are both a "general" text type, maybe I should do a trim on both sheets and change them both to text?