none
VB Script : Mismatch Error after adding new columns RRS feed

  • Question

  • Hi Folks -

    I'm getting the mismatch error when I'm trying to delete a certain string from a column.

    My script adds 3 columns to an excel file and then I add formulas to it and what not.  In the end, I want to delete the entire row if "#N/A" is found in Column H.

    However, the scrip fails and gives me the type mismatch error. If i switch and use a different column (one I didn't insert) it does not give me an error.

    Does anyone know why? Do I need to set the column as a certain type for the loop to read it?

    Here is my script:

    Const xlShiftToRight = -4161
    Set objExcel = CreateObject("Excel.Application")
    'objexcel.DisplayAlerts = False
    
    '::-- Declare argurments passed from batch script --::'
    Dim objWorkbook1, LastCell1, objWorkbook2, LastCell2
    Dim args, STAGE, LOCALEXPORTPATH, FILE1, FILE2
    set args = Wscript.arguments
    
    STAGE= args(0)
    LOCALEXPORTPATH= args(1)
    FILE1= args(2)
    FILE2= args(3)
    
    Dim FILE1TAB, FILE2TAB
    FILE1TAB= "Sheet1"
    FILE2TAB= "SAP_to_Cost_Center_CMSO_SUBF_Re"
    
    If STAGE = "1" Then
    
    	WScript.Echo "Opening the following files for processing:" 
    	WScript.Echo " "
    	WScript.Echo FILE1
    	WScript.Echo FILE2
    
    	Set objWorkbook1= objExcel.Workbooks.Open( LOCALEXPORTPATH & FILE1 )
    	Set objWorkbook2= objExcel.Workbooks.Open( LOCALEXPORTPATH & FILE2 )
    	'objWorkbook2.CheckCompatibility = False
    	
    	'::-- Get LastCell of FILE1 --::'
    	Set objWorksheet1 = objWorkbook1.Worksheets(FILE1TAB)
    	xlUp = -4162
    	Set LastCell1 = objWorksheet1.Range("A" & objWorksheet1.Rows.Count).End(xlUp)
    	
    	'::-- Get LastCell of FILE2 --::'
    	Set objWorksheet2 = objWorkbook2.Worksheets(FILE2TAB)
    	xlUp = -4162
    	Set LastCell2 = objWorksheet2.Range("A" & objWorksheet2.Rows.Count).End(xlUp)
    	
    	'::-- Add Sheet to FILE1 --::'
    	Set objWorksheet3 = objWorkbook1.Sheets.Add( , objWorkbook1.WorkSheets(objWorkbook1.WorkSheets.Count))
    	
    	'::-- Add 3 Columns to FILE1TAB --::'
    	Set objRange = objWorkbook1.Worksheets(FILE1TAB).Range("G:I").EntireColumn
    	objRange.Insert(xlShiftToRight)
    	
    	objRange = objWorkbook2.Worksheets(FILE2TAB).Range("A2:E" & LastCell2.Row).Copy
    	objWorkbook1.Worksheets("Sheet2").Range("A1:E" & LastCell2.Row).PasteSpecial objRange
    	
    	objWorkbook1.Worksheets(FILE1TAB).Range("G4").Value = "Cost Center - Full ID"
    	objWorkbook1.Worksheets(FILE1TAB).Range("H4").Value = "Cost Center Sub Function"
    	objWorkbook1.Worksheets(FILE1TAB).Range("I4").Value = "Cost Center Function/Division"
    	objWorkbook1.Worksheets(FILE1TAB).Range("G5").Value = "=LOOKUP(2,1/(TRIM(RIGHT(SUBSTITUTE(E5,""_"",REPT("" "",250)),250))" & _
    															"=RIGHT(Sheet2!$A$2:$A$" & LastCell2.Row & ",LEN(TRIM(RIGHT(SUBSTITUTE(E5,""_"",REPT("" "",250)),250))))),Sheet2!$A$2:$A$" & LastCell2.Row & ")" 
    	objWorkbook1.Worksheets(FILE1TAB).Range("H5").Value = "=VLOOKUP(G5,Sheet2!A:E,3,FALSE)"
    	objWorkbook1.Worksheets(FILE1TAB).Range("I5").Value = "=VLOOKUP(G5,Sheet2!A:E,4,FALSE)"
    	
    	'::-- Drag down formulas in Column G:I of FILE1 --::'
    	Set SourceRange = objWorksheet1.Range("G5:I5")
    	Set FillRange = objWorksheet1.Range("G5:I" & LastCell1.Row)
    	SourceRange.AutoFill FillRange
    	
    	'::-- Copy Column G:I and paste as values --::'
    	objWorkbook1.Worksheets(FILE1TAB).Range("G5:I" & LastCell1.Row).Copy
    	objWorkbook1.Worksheets(FILE1TAB).Range("G5:I" & LastCell1.Row).PasteSpecial -4163
    	
    	'::-- Loop through Column G searching for string - if found then delete entire row --::'
    	Set objWorksheet1 = objWorkbook1.Worksheets(FILE1TAB)
    	xlUp = -4162
    	m = objWorksheet1.Range("H" & objWorksheet1.Rows.Count).End(xlUp).Row
    	For r = m To 1 Step -1
    		If InStr(objWorksheet1.Range("H" & r), "#N/A") > 0 Then
    			objWorksheet1.Range("H" & r).EntireRow.Delete
    		End If
    	Next
    	
    	objWorkbook1.Worksheets("Sheet2").Delete
    	
    	objWorkbook1.Save
    	objWorkbook1.Close
    	objWorkbook2.Close
    
    Else
       WScript.Echo "Nothing available to process"
    End If
    
    '::-- Exit Protocol--::'
    objExcel.Quit
    WScript.Quit

    Any help is greatly appreciated - thank you!


    • Moved by Bill_Stewart Tuesday, May 8, 2018 7:08 PM Move to more appropriate forum
    Wednesday, March 14, 2018 12:13 AM

All replies

  • What is the complete error message and line that causes it?


    \_(ツ)_/

    Wednesday, March 14, 2018 12:19 AM
  • The line that causes it is this :

    If InStr(objWorksheet1.Range("H" & r), "#N/A") > 0 Then

    The full message is as follows:

    Char : 3
    Error : Type mismatch
    Code : 800A000D
    Source : Microsoft VBScript runtime error

    Thank you!


    Wednesday, March 14, 2018 1:02 AM
  • I think you need to do this:

    If InStr(objWorksheet1.Range("H" & r).Value, "#N/A") > 0 Then

    The reference returns a range object.  If it resolves to a cell then adding value will extract the contents.  If it return more than a cell you will have to isolate the cell in question.


    \_(ツ)_/

    Wednesday, March 14, 2018 1:12 AM
  • Hi!

    Thank yo for the suggestion. I just tried it and it's giving a nearly identical answer. Except the type mismatch displays : InStr(objWorksheet1.Range("H" & r).Value

    hmmm, very weird.


    Wednesday, March 14, 2018 8:20 AM
  • Run the following test:

    Set objExcel = CreateObject("Excel.Application")
    Set wb = objExcel.Workbooks.Add()
    objExcel.Visible = True
    Set objWorksheet1 = wb.Worksheets.Item(1)
    objWorksheet1.Range("H1").Value = "#N/A"
    MsgBox objWorksheet1.Range("H1").Formula
    If InStr(objWorksheet1.Range("H1").Formula, "#N/A") > 0 Then
    	MsgBox "Found!"
    Else
    	MsgBox "Not Found"
    End If
    	
    


    \_(ツ)_/

    Wednesday, March 14, 2018 12:13 PM
  • Hi -

    It says N/A found!

    Wednesday, March 14, 2018 12:47 PM