VB Script for Vlookup

[bmontoni]

Hi all,

I am wondering if it is possible to use VB Script to perform the following v-lookup:

I have a workbook with 2 tabs. I want to add a new column to the right in the table below that uses the "Document" field to lookup the "Mgs." field in the second table. Is this possible? I would need it to go to the last row as the number of rows could change.

Thanks in advance!

 

[Saurabhj]

Hi,

Check below code:

Sub insertMsg()
    On Error Resume Next
    Dim lastRow As Integer, rowno As Integer
    Dim lookupRange As Range
    
    With Sheets("Delivery-with ZIV")
        'Insert new column
        .Columns("I:I").Insert Shift:=xlToRight
        
        'Heading of new column
        .Range("I1") = "Msg"
        
        'Identifying the total rows
        lastRow = .Cells(Rows.Count, "H").End(xlUp).Row
        
        'Setting lookup range to check msg value
        Set lookupRange = Sheets("VBFS - DeliveryWoZIV").Range("B1:G" & Sheets("VBFS - DeliveryWoZIV").Cells(Rows.Count, "G").End(xlUp).Row)
        
        'Loop to get values using VLOOKUP function
        For rowno = 2 To lastRow
            .Range("I" & rowno) = Application.WorksheetFunction.VLookup(.Range("H" & rowno), lookupRange, 6, False)
        Next
    End With
End Sub

 

[DanteAmor]

Try this:

Sub fxVloolup()
  With Sheets("Delivery-with ZIV").Range("AA2:AA" & Sheets("Delivery-with ZIV").Range("H" & Rows.Count).End(3).Row)
    .Formula = "=IFERROR(VLOOKUP(H2,'VBFS - DeliveryWoZIV'!B:G,6,0),""Not Found"")"
    .Value = .Value
  End With
End Sub

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Example:

Dante Amor
	A		C					H																			AA
1			Sorg					document
2			us02					382509310																			64
3			us02					382509388																			Not Found
Delivery-with ZIV

Dante Amor
	A	B					G
1		document					msg
2		382509310					64
3		382509311					64
VBFS - DeliveryWoZIV