type missmatch error in my macro

osmanoca

Board Regular
Joined
Apr 16, 2016
Messages
87
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:

Code:
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
 
Last edited by a moderator:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Have you looked at your data for errors? That is have you looked at your spreadsheet for errors?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
Also, preforming a Mod every loop is slowing your routine down. Try this resize code

Code:
If UBound(Y, 2) < lngCnt Then ReDim Preserve Y(1 To 2, 1 To 2 * lngCnt)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top