type missmatch error in my macro

[osmanoca]

hello, i have word and meanings in two columns like dictionary. meanings are seperated with comma and i want to make every meaning a new row. so i use this code. it is working when i write a few rows to test. but it gives error in dictionary database. what is problem please look and help me...

my code so:

Sub SliceNDice()
Dim objRegex As Object
Dim X
Dim Y
Dim lngRow As Long
Dim lngCnt As Long
Dim tempArr() As String
Dim strArr
Set objRegex = CreateObject("vbscript.regexp")
objRegex.Pattern = "^\s+(.+?)$"
 'Define the range to be analysed
X = Range([a1], Cells(Rows.Count, "b").End(xlUp)).Value2
ReDim Y(1 To 2, 1 To 1000)
For lngRow = 1 To UBound(X, 1)
     'Split each string by ","
    tempArr = Split(X(lngRow, 2), ",")
    For Each strArr In tempArr
        lngCnt = lngCnt + 1
         'Add another 1000 records to resorted array every 1000 records
        If lngCnt Mod 1000 = 0 Then ReDim Preserve Y(1 To 2, 1 To lngCnt + 1000)
        Y(1, lngCnt) = X(lngRow, 1)
        Y(2, lngCnt) = objRegex.Replace(strArr, "$1")
    Next
Next lngRow
 'Dump the re-ordered range to columns C:D
[c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y)
End Sub

 

[steve the fish]

Check your data. Ill bet theres an error in there somewhere.

 

[osmanoca]

i dont know codes, i took from website...so need help of yours

 

[steve the fish]

Have you looked at your data for errors? That is have you looked at your spreadsheet for errors?

 

[osmanoca]

how can i look. it is a big word list. error doesnt show and word in data. only dlsplay error. how can see that problem place in data?

 

[steve the fish]

USe something like this:

=SUM(IF(ISERROR(A1:B100),1))

Enter with CTRL-SHIFT-ENTER. Change the 100 to your last row in the data. Look for these - {} - around the formula. If they arent there then you havent entered correctly. What is the result?

 

[mikerickson]

Error Values (e.g. #DIV/0) are useually caused as the result of formulas. So, to find those cells with errors, use Excel's GoTo > Special and check Formulas and Errors. You will be taken to those cells.

But, a rountine shouldn't fall over just because the data isn't as expected. To handle situations where there are error values in the data base.

For lngRow = 1 To UBound(X, 1)
'Split each string by ","
tempArr = Split(CStr(X(lngRow, 2)), ",")
For Each strArr In tempArr
lngCnt = lngCnt + 1
'Add another 1000 records to resorted array every 1000 records
If lngCnt Mod 1000 = 0 Then ReDim Preserve Y(1 To 2, 1 To lngCnt + 1000)
Y(1, lngCnt) = CStr(X(lngRow, 1))
Y(2, lngCnt) = objRegex.Replace(strArr, "$1")
Next
Next lngRow

 

[mikerickson]

Also, preforming a Mod every loop is slowing your routine down. Try this resize code

If UBound(Y, 2) < lngCnt Then ReDim Preserve Y(1 To 2, 1 To 2 * lngCnt)

 

[osmanoca]

i have dict database. one columun is word and other is meaning. so column B has many multimeaning seperated with comma. i need i macro to make all comma word new row. i use for this.

like
book a, b, c, d

it will be so:
book a
book b
book c
book d

my data has more than 50000 words . so do you know any code for this.

 

[osmanoca]

please anyone helps....for mesage above