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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
it gives error. same. please help

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
 
Upvote 0
Can you post some actual sample data? Is there any formula in the data?
 
Upvote 0
can we send excel file here?



[TABLE="width: 504"]
<tbody>[TR]
[TD]A ewa[/TD]
[TD]A halo, Bi vî awayî, Bi vî rengî, Halo, Wiha, Bi vê rê, Bi vî terhî, Bi vîvê awayî herweha, Her wiha, Herweha[/TD]
[/TR]
[TR]
[TD]A gavekî berê[/TD]
[TD]A qasekî berê, Gava din[/TD]
[/TR]
[TR]
[TD]A halo[/TD]
[TD]A ewa, Bi vî awayî, Bi vî rengî, Halo, Wiha, Bi vê rê, Bi vî terhî, Bi vîvê awayî herweha, Her wiha, Herweha[/TD]
[/TR]
[TR]
[TD]A kirin[/TD]
[TD]Dest lê negerîn, Dest nedan, Hile kirin, Île kirin, Lê newerqilîn, Lolî kirin, Lolo kirin, Lorî kirin, Lorîlorî kirin, Muameleya nehatin kirin, Navisîn, Nivîn, Nivinîn, Nivistin, Raketan, Raketin, Ramedan, Razan, Xayis ketin, Xew de çûn, Xew kirin, Xewîn, Xewisîn, Xewtin[/TD]
[/TR]
[TR]
[TD]A li dû[/TD]
[TD]A li pey, A piştî, Ê li dû, Ê li pey, I piştî, Paşînî, Ya paşê[/TD]
[/TR]
[TR]
[TD]A li pey[/TD]
[TD]A li dû, A piştî, Ê li dû, Ê li pey, I piştî, Paşînî, Ya paşê[/TD]
[/TR]
[TR]
[TD]A navîn[/TD]
[TD]A navîn[/TD]
[/TR]
[TR]
[TD]A navîn[/TD]
[TD]A navîn, Ê navîn, Navçe[/TD]
[/TR]
[TR]
[TD]A niha[/TD]
[TD]A nika, Hê jî, Hêjan, Hîna jî, Niha jî[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You can’t post a file in this forum, but you can upload the file to some sites, say dropbox.com, and then post the link here.
 
Upvote 0

You can’t post a file in this forum, but you can upload the file to some sites, say dropbox.com, and then post the link here.
Ok, try this.
I put the result in sheets2.
The ‘x’ variable is the possible max row result, you may change that to suit.
Here’s the code & the first 20 rows result.



Please use Code Tags when posting a code. Like this: [CODE ]Your Code Here[/ CODE]
Code:
[B][COLOR=Royalblue]Sub[/COLOR][/B] a1018151a[B]()[/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] x [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] j [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] k [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
    va [B]=[/B] Range[B]([/B][B][COLOR=brown]"A1"[/COLOR][/B][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [B][COLOR=brown]"B"[/COLOR][/B][B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]))[/B]
    x [B]=[/B] [B][B][COLOR=crimson]200000[/COLOR][/B][/B] [FONT=trebuchet ms][I][COLOR=Lightseagreen]'change to suit[/COLOR][/I][/FONT]
    [B][COLOR=Royalblue]ReDim[/COLOR][/B] vc[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] x[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B])[/B]
 
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
   
        [B][COLOR=Royalblue]If[/COLOR][/B] InStr[B]([/B]va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B]),[/B] [B][COLOR=brown]","[/COLOR][/B][B])[/B] [B]=[/B] [B][B][COLOR=crimson]0[/COLOR][/B][/B] [B][COLOR=Royalblue]Then[/COLOR][/B]
            vc[B]([/B]k [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
            vc[B]([/B]k [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B])[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B])[/B]
            k [B]=[/B] k [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B]
       
        [B][COLOR=Royalblue]Else[/COLOR][/B]
           
            arr1 [B]=[/B] Split[B]([/B]va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B]),[/B] [B][COLOR=brown]","[/COLOR][/B][B])[/B]
            [B][COLOR=Royalblue]For[/COLOR][/B] j [B]=[/B] [B][B][COLOR=crimson]0[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]arr1[B])[/B]
                vc[B]([/B]k [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
                    vc[B]([/B]k [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B])[/B] [B]=[/B] arr1[B]([/B]j[B])[/B]
                k [B]=[/B] k [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B]
            [B][COLOR=Royalblue]Next[/COLOR][/B]
        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
       
    [B][COLOR=Royalblue]Next[/COLOR][/B]
   
    Sheets[B]([/B][B][COLOR=brown]"sheet2"[/COLOR][/B][B]).[/B]Range[B]([/B][B][COLOR=brown]"A1"[/COLOR][/B][B]).[/B]Resize[B]([/B]k[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B])[/B] [B]=[/B] vc
 
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]


Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: Darkslateblue"]
[TH][/TH]
[TH]
A
[/TH]
[TH]
B
[/TH]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
1
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"]A halo[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
2
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"] Bi vî awayî[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
3
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"] Bi vî rengî[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
4
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"] Halo[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
5
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"] Wiha[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
6
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"] Bi vê rê[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
7
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"] Bi vî terhî[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
8
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"] Bi vîvê awayî herweha[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
9
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"] Her wiha[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
10
[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[TD="bgcolor: lightgreen"] Herweha[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
11
[/TD]
[TD="bgcolor: lightgreen"]A gavekî berê[/TD]
[TD="bgcolor: lightgreen"]A qasekî berê[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
12
[/TD]
[TD="bgcolor: lightgreen"]A gavekî berê[/TD]
[TD="bgcolor: lightgreen"] Gava din[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
13
[/TD]
[TD="bgcolor: lightgreen"]A halo[/TD]
[TD="bgcolor: lightgreen"]A ewa[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
14
[/TD]
[TD="bgcolor: lightgreen"]A halo[/TD]
[TD="bgcolor: lightgreen"] Bi vî awayî[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
15
[/TD]
[TD="bgcolor: lightgreen"]A halo[/TD]
[TD="bgcolor: lightgreen"] Bi vî rengî[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
16
[/TD]
[TD="bgcolor: lightgreen"]A halo[/TD]
[TD="bgcolor: lightgreen"] Halo[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
17
[/TD]
[TD="bgcolor: lightgreen"]A halo[/TD]
[TD="bgcolor: lightgreen"] Wiha[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
18
[/TD]
[TD="bgcolor: lightgreen"]A halo[/TD]
[TD="bgcolor: lightgreen"] Bi vê rê[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
19
[/TD]
[TD="bgcolor: lightgreen"]A halo[/TD]
[TD="bgcolor: lightgreen"] Bi vî terhî[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]
20
[/TD]
[TD="bgcolor: lightgreen"]A halo[/TD]
[TD="bgcolor: lightgreen"] Bi vîvê awayî herweha[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
i used that code in a some words it worked well. but i copy 20000 or more or less it gives error as 'SUBSCRİPT aout of range'. in some test also works but doesnt change anything and doesnt show error. what is problem. what is mean of x=20000. is this number of rows will be created maximum? and i changed it to 50000 and same error.
 
Upvote 0
i used that code in a some words it worked well. but i copy 20000 or more or less it gives error as 'SUBSCRİPT aout of range'. in some test also works but doesnt change anything and doesnt show error. what is problem. what is mean of x=20000. is this number of rows will be created maximum? and i changed it to 50000 and same error.
Yes, it’s the possible maximum rows of result. Try x = 1000000
I think you can try 50.000 rows of data with x = 1000000

Remember Excel has max limit at 1,048,576 rows
Excel specifications and limits - Excel
 
Last edited:
Upvote 0
osmanoca, please provide the link of your file here, not via Private Message. This way makes anybody else here can help you too.
 
Upvote 0
i changed many different numbers. it working like that will change but after one or two seconds it doesnt change anything and doesnt show error. İ WİLL BE CRAZY......
 
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