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!

 

[jindon]

How are you populating Col.D of Sheet1 and about how many rows at a time?

 

[QuietRiot]

Dim last As String
    last = Range("D65536").End(xlUp).Row ' finds last row in column D
    Range("E1").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-4]:C[-3],2,FALSE)" ' puts vlookup in E uses all of A and B
    Range("E1").AutoFill Destination:=Range("E1:E" + last) ' fills from E1 to last row
    End Sub

 

[jindon]

With Sheets("sheet1")
     With .Range("d1",.Range("d" & Rows.Count).End(xlUp)).Offset(,1)
          .formula = "=vlookup(d1,sheet2!a:b,2,false)"
          .Value = .Value
     End With
End With

 

[thomachr]

How are you populating Col.D of Sheet1 and about how many rows at a time?

It is a macro that searches for records in another workbook between the dates you enter. There can be anywhere from 1 to 5000 records, depending on the time frame you pick.

Thanks for the two solutions guys. I will try them out tomorrow to see if they work and let you know what happens.

Thanks again, you guys are awesome.

 

[thomachr]

With Sheets("sheet1")
     With .Range("d1",.Range("d" & Rows.Count).End(xlUp)).Offset(,1)
          .formula = "=vlookup(d1,sheet2!a:b,2,false)"
          .Value = .Value
     End With
End With

I tried both of the solutions above, and can't get either one to work quite right.

It did Vlookup for cell D1 and returned that value to every row of data. Also, I have column headings in D1 and E1 and currently, E1 is replaced with the Vlookup function also.

Any thougths?

 

[QuietRiot]

confused by what you mean for the first part.

but on the column headings part. just change it to E2 instead of E1

Dim last As String 
    last = Range("D65536").End(xlUp).Row ' finds last row in column D 
    Range("E2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-4]:C[-3],2,FALSE)"
    Range("E2").AutoFill Destination:=Range("E2:E" + last) 
    End Sub

 

[thomachr]

confused by what you mean for the first part.

but on the column headings part. just change it to E2 instead of E1

Dim last As String 
    last = Range("D65536").End(xlUp).Row ' finds last row in column D 
    Range("E2").FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!C[-4]:C[-3],2,FALSE)"
    Range("E2").AutoFill Destination:=Range("E2:E" + last) 
    End Sub

I think I found part of the issue, I needed single quotes around the sheet2 reference.

The issue I am having now is that the macro is working on a different sheet than I need, ie, it is putting the vlookup functions on Sheet1.

Really, and sorry for the confusion, but the macro is stored in Sheet 1, but I need it to put the Vlookup functions on Sheet2 and looking up values on Sheet3.

 

[QuietRiot]

umm..

does this work?

Sub macro1()

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

 

[thomachr]

umm..

does this work?

Sub macro1()

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

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)

Getting closer though!