Search code examples
excelcsvvbscriptmatchtype-mismatch

Type mismatch , compare excel cell value to array value *FIXED*

Current problem:

Target is to copy data from a source .csv file to a target excel sheet, based on matching a value from each row of the .csv to values in an excel sheet column, and then making the copy based on that to the correct cells in the excel sheet.

I'm gathering values to an array from a .csv file in a loop, comparing one of the values in the array to values in the target excel sheet column A, if a match happens, all the values in the array are to be copied to the corresponding row in the excel sheet.

I'm now getting a "Type mismatch" error when trying to compare the excel sheet's cell values to the array value.

Here are the relevant bits of the code:

sfile="\\DFSpath\file1.csv"
smaster="\\DFSpath\file2.xlsx"

set xlapp = createobject("excel.application")

set wbtarget = xlapp.workbooks.open(smaster)

set tgtworksheet = wbtarget.worksheets("sheet1")

set usedRowsXP = tgtworksheet.UsedRange.Rows

dim fs,objTextFile
set fs=CreateObject("Scripting.FileSystemObject")
dim arrStr
set objTextFile = fs.OpenTextFile(sfile)

Do while NOT objTextFile.AtEndOfStream
  arrStr = split(objTextFile.ReadLine,",")

    for each oRow in usedRowsXP   

    Introw = oRow
    If tgtworksheet.cells(Introw,1).value = arrStr(2) Then
    tgtworksheet.cells(Introw,4).value = arrStr(0)
    tgtworksheet.cells(Introw,5).value = arrStr(1)
    tgtworksheet.cells(Introw,6).value = arrStr(2)

    End If
    next
Loop

Fixed by modifying the code as follows:

dim fs,objTextFile
    set fs=CreateObject("Scripting.FileSystemObject")
    dim arrStr
    set objTextFile = fs.OpenTextFile(sfile)

'##Replace quotation marks with nothing in .csv##

strText = objtextFile.ReadAll
objTextFile.Close
strNewText = Replace(strText, """", "")

Set objtextFile = fs.OpenTextFile(sfile, 2)
objtextFile.Write strNewText
objtextFile.Close

set objTextFile = fs.OpenTextFile(sfile, 1)

'##Gather array out of line in .csv + compare user's name to name in excel sheet, if matches, copy data, repeat for each line##


Do while NOT objTextFile.AtEndOfStream
arrStr = split(objTextFile.ReadLine,",")     

for each oRow in usedRowsXP   

Introw = oRow.row
If Cstr(tgtworksheet.cells(Introw,1).value) = arrStr(2) Then
tgtworksheet.cells(Introw,4).value = arrStr(0)
tgtworksheet.cells(Introw,5).value = arrStr(1)
tgtworksheet.cells(Introw,6).value = arrStr(2)

End If
next

Loop
Solution

  • I think the problem is in this row: 

    for each oRow in usedRowsXP  
Introw = oRow

    I'm not really good at VBA, but I think that usedRowsXP is a Range, and each row of it will also be a range. Therefore Introw will be a range. Try using the Row property of oRow, it will return its row number: 

    for each oRow in usedRowsXP  
Introw = oRow.Row

    After that Introw should work fine in the Cells(Introw, 1) etc. expression.