Help writing Vlookup into VBA code

[thomachr]

I need help writing the VLookup function into a VBA macro.

I currnetly have a macro that generates a list of companies in column D on Sheet1. There could be a different number of rows populated every time the macro runs.

I also have a list of all the possible companies next to their e-mail address on Sheet2 (company in column A, corresponding e-mail in column B).

I know Vlookup can search sheet2 and populate the correct e-mail address on sheet1, but I want a VBA solution in which it will automatically see how many rows of companites I have, perform Vlookup for each company, and place the corresponding emails in sheet1, column E.


Thanks so much for any help you can offer!

 

[thomachr]

Nope. The Vlookup formulas are still being put into Sheet1 column E. They do, however, work properly with the references to sheet three ('sheet3'! instead of sheet3! though)

This got me closer, I get the proper Vlookup formula in Sheet2 Cell E2, but then get a 400 error

last = Sheets("Sheet2").Range("D65536").End(xlUp).Row
Sheets("Sheet2").Range("E2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet3'!C[-4]:C[-3],2,FALSE)"
Sheets("Sheet2").Range("E2").AutoFill Destination:=Range("E2:E" + last)

I think the last line is the only issue, any thoughts?

 

[jindon]

With Sheets("Sheet2")
     With .Range("d2",.Range("d" & Rows.Count).End(xlUp)).Offset(,1)
         .Formula = "=vlookup(d2,sheet3!a:b,2,false)"
         .Value = .Value
     End With
End With

 

[thomachr]

With Sheets("Sheet2")
     With .Range("d2",.Range("d" & Rows.Count).End(xlUp)).Offset(,1)
         .Formula = "=vlookup(d2,sheet3!a:b,2,false)"
         .Value = .Value
     End With
End With

Works great! Thanks so much!

The only thing i had to edit was the sheet3 reference needs to do in single quotes:

"=vlookup(d2,'sheet3'!a:b,2,false)"

instead of:

"=vlookup(d2,sheet3!a:b,2,false)"

Thanks again! This site rocks![/code]