Vrftjkry

How can I use an index match function in VBA to find values in a CSV file then import those values to the matched index in a cell in Excel? Say I have a CSV file which contains the following comma deliminated text:

lookup1, 2

lookup2, 3

And the following data in Sheet2 in Excel:

B2 = lookup1

C2 = 0

B3 = lookup2

C3 = 0

How do I use an index match to find the value 2 in the second column for "lookup1" in the CSV file and then change the value to 2 in cell C2 in Excel?

I've tried the code below but it doesn't work and I don't think it's looking in the Excel sheet to match where the value needs to go

Sub Tester()



    Dim arr1



    arr1 = CsvToArray(Application.GetOpenFilename)



    Debug.Print TestLookup(arr1, "lookup1", 1, 2)



End Sub



Function TestLookup(arr, val, lookincol As Integer, returnfromcol As Integer)

    Dim r

    r = Application.Match(val, Application.Index(arr, 0, lookincol), 0)

    If Not IsError(r) Then

        TestLookup = arr(r, returnfromcol)

    Else

        TestLookup = "Not found" 'or some other "error" value

    End If

End Function



Function CsvToArray(filepath As String) As Variant

    Dim wb As Workbook

    Application.ScreenUpdating = False

    Set wb = Application.ActiveWorkbook

    CsvToArray = wb.Sheets("Sheet2").Range("C2").CurrentRegion.Value

End Function

Thanks!

Your CSV file needs to be formatted with a proper header.

In plain text it would look like:

lookup1,lookup2

2,3

Would comment but not ranked high enough yet.