CODEWITHSUNDEEP
vbaexcel
ExcelMacroStudent
ExcelMacroStudent

Reputation: 23

Type Mismatch Error when searching for a string in an array

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!

Upvotes: 0

Views: 1264

Answers (2)

David Zemens
David Zemens

Reputation: 53623

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")))

Upvotes: 2

tigeravatar
tigeravatar

Reputation: 26650

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

Upvotes: 0

Related Questions