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 StringDim strTmp1 As String'Build the data dictionaryDim oDicTmp As ObjectSet oDicTmp = CreateObject("Scripting.Dictionary")Dim nI As IntegerDim 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 fixedWith Sheets("Sheet2") On Error Resume Next .Rows(2).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete Shift:=xlToLeft On Error GoTo 0 vIn = .Range("A1").CurrentRegion.ValueEnd WithReDim 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 iEnd Withp = WorksheetFunction.RoundUp(n / nCol, 0)Dim nNextRow As LongnNextRow = NextAvailableRowWith 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 WithEnd WithEnd Sub

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

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!

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.

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.

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…