Vlookup macro

[papil]

Hi,

I have 2 worksheets . i am looking for macro code so that i dont have to do vlookup everytime. And the number of rows will change in both the worksheets.
i have data in column D from D2 to last cell in that column in sheet 1. i lookup this value from sheet 2 and populate the data in column BW of sheet 1,BW has formula-vlookup(D2,Sheet2$A$1:$D$610,4,false) ,there can be more than 610 rows and more than 4 columns in sheet 2 its not fixed too. similarly i have columns BX ,BZ of sheet 1 that will be populated with-

BW-vlookup(D2,Sheet2$A$1:$D$610,4,false)
BX -vlookup(D2,Sheet2$A$1:$Z$610,24,false)
BZ -vlookup(D2,Sheet2$A$1:$BC$610,34,false)

Please help .Thanks.

 

[Veritan]

Can you be more specific when you say:

there can be more than 610 rows and more than 4 columns in sheet 2 its not fixed too.

I'm not exactly certain what you mean by this. Are you saying that the data in Sheet2 isn't always in the same column/other location? If that's the case, what do you use to determine the data to use, column headings?

 

[papil]

I meant the last row will change in sheet 2 i.e. number of records in excel. Columns will be from A to AS in sheet 2 that is fixed.Data will always be fixed from those particular columns.Lets say-i will take the data from columns-B,C,D,E,F,G from sheet 2 into columns BW,BX,BY,BZ,CA,CB in sheet 1.

Can you be more specific when you say:



I'm not exactly certain what you mean by this. Are you saying that the data in Sheet2 isn't always in the same column/other location? If that's the case, what do you use to determine the data to use, column headings?

 

[Veritan]

Okay, I think this should help you out. I've set the code up so that the only thing you'll need to change are the variables in the Call lines.

Option Explicit
Sub VLookup()
    Sheets("Sheet1").Activate
    Call VLFunction("Sheet2", 75, 4, 4)
    Call VLFunction("Sheet2", 76, 26, 24)
    Call VLFunction("Sheet2", 78, 55, 34)
End Sub
Private Sub VLFunction(SheetName$, ColNum%, SearchCol%, VLColNum%)
    Dim i%, LastWriteRow%, LastSearchRow%, r As Range, VLookupData() As Variant
    LastWriteRow = Range("D2").End(xlDown).Row
    LastSearchRow = Sheets(SheetName).Cells(1, VLColNum).End(xlDown).Row
    VLookupData = Range("D2:D" & LastWriteRow)
    For Each r In Range(Cells(2, ColNum), Cells(LastWriteRow, ColNum))
        i = i + 1
        With Sheets(SheetName)
            r = WorksheetFunction.VLookup(VLookupData(i, 1), .Range(.Range("A1"), .Cells(LastSearchRow, SearchCol)), VLColNum, False)
        End With
    Next r
End Sub

When you run the VLookup sub, it'll call the VLFunction sub to perform the VLookups that you want for each column of data you are writing to. The first variable in the Call lines is just the name of the sheet you are pulling the data from, "Sheet2" in your example. You can change this to whatever the name of the sheet is, just make sure to leave the quote marks in. The second variable is the numerical value of the column you are writing to. So in your first formula example, you were writing to column BW. BW is the 75th column, so you would put 75 for the second variable. The third variable is the last column of your search array. In your second formula example, you were searching A1:Z610. Z is the 26th column, so put 26 here. Finally, the fourth variable is the column you are searching for the data in. In your third formula example, you were looking for data in column AH, the 34th column, so put 34 here. I've already set up your 3 example formulas in the code, and you should be able to add and edit any others that you need without much difficulty. Note that the code is set to start writing the results of the VLookups in row 2 of sheet1. If this needs to change, you'll have to adjust some of the references.