Solved

Type miss match

Posted on 2011-03-15
6
390 Views
Last Modified: 2012-05-11
Hi Experts,

I need Experts help to troubleshoot this problem. I’m using the attached code to crosscheck data from Sheet 1 and 2 and copy the matched data at Matched sheet. But the problem is whenever I run the “Sub CopyData” module it shows an error message as “Type miss match” at this line:

strTmp = Trim(Str(vIn(i, j))) & vIn(i, j + 1)

Hope Experts will help me to fix this.



Sub CopyData()
Dim strTmp As String
Dim strTmp1 As String
'Build the data dictionary
Dim oDicTmp As Object
Set oDicTmp = CreateObject("Scripting.Dictionary")

Dim nI As Integer
Dim nJ As Integer

    For nI = 1 To Range("TABLEDATA").Columns.Count
        For nJ = 1 To Range("TABLEDATA").Columns(nI).Rows.Count
            strTmp = Trim(Range("TABLEDATA").Columns(nI).Rows(nJ).Value)
            If strTmp <> "" Then
                If Not (oDicTmp.Exists(strTmp)) Then
                    oDicTmp.Add strTmp, 1
                'Else
                '    oDicTmp.Item(strTmp) = oDicTmp.Item(strTmp) + 1
                End If
            End If
        Next nJ
    Next nI
 
'------------------------------
Dim oDic As Object, vOut(), vIn(), i As Long, j As Long, n As Long, p As Long, nCol As Long

'nCol = Application.InputBox("How many sets of columns for the results (each set has three columns)?", Type:=1)
nCol = 6 'The number of columns is fixed

With Sheets("Sheet2")
    On Error Resume Next
    .Rows(2).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete Shift:=xlToLeft
    On Error GoTo 0
    vIn = .Range("A1").CurrentRegion.Value
End With

ReDim vOut(1 To UBound(vIn, 1) * UBound(vIn, 2), 1 To 3)

Set oDic = CreateObject("Scripting.Dictionary")

With oDic
    For i = 2 To UBound(vIn, 1)
        For j = 1 To UBound(vIn, 2) - 1 Step 2
            
            strTmp = Trim(Str(vIn(i, j))) & vIn(i, j + 1)
            strTmp1 = Left(vIn(i, j + 1), 5)
            If Not .Exists(strTmp) And oDicTmp.Exists(strTmp1) Then  'Long modified
                n = n + 1
                vOut(n, 1) = vIn(i, j)
                
                vOut(n, 2) = vIn(i, j + 1)
                .Add strTmp, 1
            End If
        Next j
    Next i
End With

p = WorksheetFunction.RoundUp(n / nCol, 0)

Dim nNextRow As Long

nNextRow = NextAvailableRow

With Sheets("Matched")
    '.UsedRange.Clear
    'With .Range("A1")
    With .Range("A" & Trim(Str(nNextRow)))
    
        If nNextRow = 1 Then
            .Resize(, 3).Value = Array("Number", "Type", "")
        End If
        
        'Long added one if statement to check if the number of rows exceed 65536 rows
        If n > 65536 Then
            MsgBox "The number of rows is " & Trim(Str(n)) & " which exceeds 65536." & vbCrLf & vbCrLf & "The process is how halted.", vbCritical + vbOKOnly, "Error"
            Exit Sub
        Else
            If n = 0 Then
                MsgBox "The number of rows is 0.  The result is empty." & vbCrLf & vbCrLf & "The process is how halted.", vbCritical + vbOKOnly, "Error"
                Exit Sub
            Else
                .Offset(1).Resize(n, 3) = vOut
            End If
        End If
        
        For j = 1 To nCol - 1
            If nNextRow = 1 Then
                .Offset(, j * 3).Resize(, 3).Value = Array("Number", "Type", "")
            End If
            .Offset(p + 1, (j - 1) * 3).Resize(n - (p * j), 3).Cut .Offset(1, j * 3)
        Next j
    End With
End With

End Sub

Open in new window

DataCheck.xls
0
Comment
Question by:Cartillo
6 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 50 total points
ID: 35136061
What is in vIn(I, j) and why are you using Str?
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 400 total points
ID: 35136063
Change:

strTmp = Trim(Str(vIn(i, j))) & vIn(i, j + 1)

to:

strTmp = Trim(vIn(i, j)) & vIn(i, j + 1)

Kevin
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35136085
Dear Carlito,

the problem is that the STR function returns an error when trying STR a cell with no data..
check if cell hold data before using the STR or use on error resume next..


Kind regards

Eric
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35136109
You don't need the STR function. The use of the & concatenation operator coerces both the left and right operands to strings. And if you didn't use the concatenation operator then assigning the result to a string typed variable coerces the value to string.

Kevin
0
 
LVL 5

Assisted Solution

by:Khalid Mehmood Awan
Khalid Mehmood Awan earned 50 total points
ID: 35146028
Try debugging & see what does this line return from functions used.

while ur cursor on this line press F9 to mark a BREAK POINT
RUN it
when the code reaches the line u said , it will break...

Check all parts of line individually in watch window
Probably you are passing empty string to some function that requires non-empty or non null string.
0
 

Author Closing Comment

by:Cartillo
ID: 35146630
Hi all,

Thanks for the help.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now