Type Mismatch Error when searching for a string in an array

Question

I am working on a macro that will consolidate two different sources of order data. The first source will contain old orders as well as some new, the second source will contain only the old orders and will have additional data in columns that were updated manually.

My idea for this is to take the order totals from the second source, paste them in a sheet after the order totals from the first source, and then search all the order numbers from the new file against the order numbers from the existing tracker. I have a for loop that is supposed to find the order numbers from the new file that are not already in the tracker and then insert a row with that order detail. I am receiving a Type mismatch error on the if statement that checks if the string exists in the array. Please take a look at this code:

Dim r As Integer
For r = 1 To 1000
    Dim NewOrd As String
    NewOrd = Range(Cells(r, 1), Cells(r, 1)).Value
    Dim ExistArray As Variant
    ExistArray = Range("a1", Range("a1").End(xlUp))
    Sheets("Sheet2").Select

    If IsEmpty(NewOrd) Then
        Exit For
    End If

    If Not UBound(Filter(ExistArray, NewOrd)) >= 0 And NewOrd <> "" Then
        Rows(r).Select
        Selection.Copy
        Sheets("Sheet3").Select
        Rows(r).Select
        Selection.Insert Shift:=xlDown
        Application.CutCopyMode = False
    End If
    r = r + 1
Next r

I have tried a few different ways of setting the array, tried adding option explicit, and tried nesting for loops (not my brightest efficiency moment). Would greatly appreciate another set of eyes!

Thanks!

David Zemens · Accepted Answer · 2013-08-21 19:42:15Z

2

Assigning a Range object to an array always results in a two-dimensional array, which is causing the error.

Do this:

ExistArray = Application.Transpose(Range("a1", Range("a1").End(xlUp)))

I think that should resolve it for you.

Updates

You may need to:

Dim ExistArray() As Variant

Your range object is also problematic, being a single cell:

ExistArray = Application.Transpose(Array(Range("A1")))

edited Aug 21, 2013 at 19:42

answered Aug 21, 2013 at 19:12

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ExcelMacroStudent Over a year ago

Unfortunately it did not. The code broke on the same line (If Not UBound...) with the same error.

Andy G Over a year ago

Yes, Range("a1", Range("a1").End(xlUp)) results in a single value. The second use of "a1" needs to start further down, using "a1000" or .Rows.Count as in tigeravatar's code.

David Zemens Over a year ago

Your range object is the problem.

David Zemens Over a year ago

@ExcelMacroStudent see updates. You may need to do some additional tests, because this method only works if your range is one cell. If your range needs to be multiple cells, then the previous method should work, you just need to refine the range object's assignment.

ExcelMacroStudent Over a year ago

@DavidZemens I had tried setting the array with a with block earlier and had used the Rows.count to set the end of the range but somehow I didn't make that connection the second (or third) time around. Thanks a lot!

tigeravatar · Accepted Answer · 2013-08-21 19:37:35Z

Change the sheet names from "Sheet1" and "Sheet2" as necessary:

Sub tgr()

    Dim wsNew As Worksheet
    Dim wsTracker As Worksheet
    Dim rIndex As Long

    'This is the sheet that contains the new data that needs to be added
    Set wsNew = Sheets("Sheet1")

    'This sheet contains the old data
    Set wsTracker = Sheets("Sheet2")

    'Go through each row in the new data
    For rIndex = 1 To wsNew.Cells(Rows.Count, "A").End(xlUp).Row

        'Verify that the row isn't blank and that it doesn't already exist in wsTracker
        If Len(wsNew.Cells(rIndex, "A").Value) > 0 And WorksheetFunction.CountIf(wsTracker.Columns("A"), wsNew.Cells(rIndex, "A").Value) = 0 Then

            'This is a new item that needs to be added
            'Copy the row to the next available row in wsTracker
            wsNew.Rows(rIndex).Copy wsTracker.Cells(Rows.Count, "A").End(xlUp).Offset(1)

        End If
    Next rIndex

    Set wsNew = Nothing
    Set wsTracker = Nothing

End Sub

Collectives™ on Stack Overflow

Type Mismatch Error when searching for a string in an array

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related