0

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!

  • What have you tried so far? – cybernetic.nomad Nov 20 '18 at 18:39
  • You can use Application.WorksheetFunction.Index and Application.WorksheetFunction.Match in VBA, but I doubt it will be useful in this case. – Christofer Weber Nov 20 '18 at 18:51
  • @cybernetic.nomad i made changes to the post to include the code I've used so far, but it's not quite what i need – Tyll25 Nov 20 '18 at 19:12
0

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.