Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Type miss match

Posted on 2011-03-15
6
Medium Priority
?
416 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 200 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 1600 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 200 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

926 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