VBA "Match" with string returning error 2042

Question

I am beginning a code that will open a csv file, find specific columns and then copy and paste them into the primary excel workbook.

The csv file will have various headers in potentially different orders as they wer automatically generated. Before trying to copy any information, I want to validate the csv file to make sure all the necessary data is present.

Dim WebImp As Workbook
Dim BackLog As String
Dim col As Long, res As Variant
Dim SearchValue As String

Private Sub CommandButton1_Click()

planned:
    MsgBox "Open a valid Web Backlog export.", vbExclamation, "Web Import"

    Application.FileDialog(msoFileDialogFilePicker).Show
    BackLog = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
    Set WebImp = Workbooks.Open(BackLog)
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    WebImp.Sheets(1).Activate
    SearchValue = "Summary"
    res = Application.Match(SearchValue, WebImp.Sheets(1).Rows(1), 0)
    If IsError(res) Then
        WebImp.Close
        MsgBox "File missing necessary data.", vbExclamation, "Missing Data"
        GoTo planned
    End If


End Sub

This is an example of the headers. Highlighted is the column I am trying to seek, but there is no guarantee it will be in column A in the future.

Currently, my code is defaulting to

error 2042

and determines the file is incorrect, even though a cell with "Summary" is present.

What is wrong with the syntax I have written? Why is it not grabbing properly? And what are some potential solutions?

just this: `Application.Match(SearchValue, Rows(1), 0)`. Nothing should come before `Application` — CallumDA, Feb 22 '18 at 14:09
You should be using `WebImp.Sheets(1).Rows(1)` not just `Rows(1)` — Rory, Feb 22 '18 at 14:10
Please don't edit your question to reflect the suggestions provided in answers and comments. — CallumDA, Feb 22 '18 at 14:13
Sorry, failed to adjust some variables for posting. I edited the code according to the suggestions but error 2042 is still present. Will not change the question in the future sorry. — Thelnternet, Feb 22 '18 at 14:13
Apparently your title cell includes an extra space "Summary " so that it cannot be found. — T.M., Feb 22 '18 at 14:16
what's `Len([A1])`? Are you sure there are no additional non-printing characters in that cell? — David Zemens, Feb 22 '18 at 14:19
If you debug set `Application.DisplayAlerts = True` so you don't miss any important messages — Pᴇʜ, Feb 22 '18 at 14:19
Possible duplicate of [Why am I getting Error 2042 in VBA Match?](https://stackoverflow.com/questions/15526784/why-am-i-getting-error-2042-in-vba-match) — IQV, Feb 22 '18 at 14:20
@IQV, I read through that post, however the poster is using an int variable and I was under the impression that me using a string might be causing a problem separate form that post — Thelnternet, Feb 22 '18 at 14:21
@DavidZemens He is opening a CSV so there should be only one sheet? — Pᴇʜ, Feb 22 '18 at 14:26
I've just tested the above and it works perfectly on my 2013 Excel, opening a CSV file with Summary in first cell and storing the column number (1) in `res`. Might be worth opening test.csv in Notepad++ or similar to check there are no hidden characters in the 'cell'. — CLR, Feb 22 '18 at 14:29
Does `Application.Match("*" & SearchValue & "*", WebImp.Sheets(1).Rows(1), 0)` work? If it does, you have some extra characters in the cell. — Rory, Feb 22 '18 at 14:41
Could do with an answer to @DavidZemens' question: what is `Len([A1])` — CLR, Feb 22 '18 at 14:44
Len(A1) is 7 characters. Unsure what happened, but I copied the code to a new workbook to try and troubleshoot without damaging the master file, and it works fine now... — Thelnternet, Feb 22 '18 at 15:09

score 2 · Accepted Answer · answered Feb 22 '18 at 14:53

Unless there are additional non-printing characters (which you could identify by testing the Len(WebImp.Sheets(1).Range("A1"), the code should work.

Another way to verify wouldbe using the Range.Find method.

Dim r as Range
Set r = WebImp.Sheets(1).Rows(1)
If r.Find("Summary", LookAt:=xlWhole) Is Nothing Then
    If r.Find("Summary") Is Nothing Then
        MsgBox "Summary doesn't exist in this row"
    Else
        MsgBox "Summary exists, with additional non-printed characters."
    End If
End If

Before trying to copy any information, I want to validate the csv file to make sure all the necessary data is present.

You could also do this using a stream reader to validate the contents of the csv.

Function FileContainsSummary(filepath$)
' returns the index position of "Summary" in row 1 of the CSV file
'filepath = "C:\debug\test.csv"
Dim contents As String
Dim headers() As String
Dim i As Long
Dim ret
Dim ff As Long

ff = FreeFile()

Open filepath For Binary As ff
contents = Space$(LOF(ff))
' reads the entire file contents:
'Get ff, , contents

'reads only the first line:
Line Input #ff, contents
Close ff

'~~ Let's see if "Summary" exists at all:
'   You can remove this block after debugging.
If InStr(contents, "Summary") Then
    MsgBox "Exists!"
End If

' ~~ find it's position
headers = Split(contents, ",")

' default value of -1, will return if Summary not found.
ret = -1

For i = LBound(headers) To UBound(headers)
    If headers(i) = "Summary" Then
        ret = i + 1
        GoTo EarlyExit
    End If
    If InStr(headers(i), "Summary") Then
        MsgBox "WARNING: Found, but with additional characters."
        ret = i + 1
        GoTo EarlyExit
    End If
Next

EarlyExit:
    FileContainsSummary = ret
End Function

VBA "Match" with string returning error 2042

1 Answers1