CODEWITHSUNDEEP
excelvb.netformulavlookup
MichaelXX
MichaelXX

Reputation: 27

Populating a specific column range from Excel Sheet using Vb.net Vlookup

I'm trying to populate a specific column range from my excel sheet using vb.Net w/ vlookup formula but it keeps returning a 1 value to a single cell which I am expecting to fill all rows affected on the column.

**Resolved, here's my final code:

Dim App As New Excel.Application
Dim workbook As Excel.Workbook
Dim sheet As Excel.Worksheet
Dim exrange As Excel.Range



 Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
    Try
        Dim fn As String = System.IO.Path.GetFileNameWithoutExtension(filedirectory1) 'Gets the filename  
        Dim convertedfn = fn.Substring(0, 31) 'Returns a specific string based on the character count from the filename
        workbook = App.Workbooks.Open(filedirectory1)
        sheet = workbook.Worksheets(convertedfn) '
        exrange = sheet.UsedRange

        exrange = CType(sheet.Columns("B:B"), Excel.Range) 'selects a specific column range in the excel file
        exrange.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow) 'Inserts a column from the selected column range above

        Dim lastrow As Long
        lastrow = sheet.Range("A" & sheet.Rows.Count).End(Excel.XlDirection.xlUp).Row ' To get the row count that has value from a specific column
        sheet.Range("B2:B" & lastrow).Formula = "=VLOOKUP(A:A,'[somefilename.xlsx]JULY 2021'!$C:$M,11,0)" 'Vlookup formula 

        App.DisplayAlerts = False
        workbook.Save()
        workbook.Close()
        App.Quit()

        MessageBox.Show("Done")

    Catch ex As Exception
        MessageBox.Show(ex.ToString)
        workbook.Close()
        App.Quit()


    End Try

End Sub

Old result:

enter image description here

Final result:

enter image description here

edit: finally made it work by adding a specific range from this code Before:

 'sheet.Range("B2").Formula = "=VLOOKUP(A:A,'[somefilename.xlsx]JULY 2021'!$C:$M,11,0)"'

Post Code:

'sheet.Range("B2:B4").Formula = vlookup formula etc..'

Edit 2: Got it working. Declared a variable that counts the row of the cells that has a value from a specific column.

Dim lastrow As Long
            lastrow = sheet.Range("A" & sheet.Rows.Count).End(Excel.XlDirection.xlUp).Row ' To get the row count that has value from a specific column

Reference: Excel Vlookup with Autofill VBA

Upvotes: 1

Views: 578

Answers (1)

MichaelXX
MichaelXX

Reputation: 27

This has been resolved by adding these to the existing code.

Dim lastrow As Long
        lastrow = sheet.Range("A" & sheet.Rows.Count).End(Excel.XlDirection.xlUp).Row ' To get the row count that has value from a specific column

This is to fill/populate a specific column range with a vlookup formula.

sheet.Range("B2:B" & lastrow).Formula = "=VLOOKUP(A:A,'[somefilename.xlsx]JULY 2021'!$C:$M,11,0)" 'Vlookup formula

Upvotes: 0

Related Questions