VBA Excel "error 13: type mismatch" -
i used code create 100000 numbers (12 digit unique random numeric numbers )
sub uniqueramdom() const strcharacters string = "0123456789" dim cllalphanums collection dim arrunqalphanums(1 60000) string dim varelement variant dim stralphanum string dim alphanumindex long dim lubound long dim lnumchars long dim long set cllalphanums = new collection lubound = ubound(arrunqalphanums) lnumchars = len(strcharacters) on error resume next stralphanum = vbnullstring = 1 12 stralphanum = stralphanum & mid(strcharacters, int(rnd() * lnumchars) + 1, 1) next cllalphanums.add stralphanum, stralphanum loop while cllalphanums.count < lubound on error goto 0 each varelement in cllalphanums alphanumindex = alphanumindex + 1 arrunqalphanums(alphanumindex) = varelement next varelement range("a1").resize(lubound).value = application.transpose(arrunqalphanums) set cllalphanums = nothing erase arrunqalphanums end sub it works with: dim arrunqalphanums(1 50000) string
but with: dim arrunqalphanums(1 100000) string , not working , producing error : type mismatch
i have following code in here http://www.excelforum.com/
you have hit limitation of transpose. below work
dim arrunqalphanums(1 65536 ) string 'remember number 65536? this wont work
dim arrunqalphanums(1 65537 ) string you find limitation inherited on ranges prior versions of excel. microsoft may have left business incomplete
you refactor code below
option explicit sub uniqueramdom() const strcharacters string = "0123456789" dim stralphanum string dim alphanumindex long dim lubound long dim lnumchars long dim long dim irow long irow = 1 lubound = 100000 'change here ubound. can increase execution time. lnumchars = len(strcharacters) on error resume next stralphanum = vbnullstring = 1 12 stralphanum = stralphanum & mid(strcharacters, int(rnd() * lnumchars) + 1, 1) next cells(irow, 1) = stralphanum irow = irow + 1 loop while irow <= lubound on error goto 0 end sub
Comments
Post a Comment