sql - Subscript out of range error during Cartesian product -


i'm trying find cartesian product of 4 columns have data separated delimiter

example

id                     id2                         string           string2  1234   33423,43222,442224,213432    sample;repeat;example;multiple second; possible;delimiter 2345 12354; 55633; 343534;65443;121121 data;set;sample;find     answer;combination;by

and error subscript out of range below code. can going wrong?

sub cartesian()     dim mystr1 variant, mystr2 variant, mystr3 variant, mystr4 variant, _     str1 variant, str2 variant, str3 variant, str4 variant, x long, _     origstring1 variant, origstring2 variant, origstring3 variant, _     origstring4 variant, y long      origstring1 = range("a2:a" & range("a" & rows.count).end(xlup).row)     origstring2 = range("b2:b" & range("a" & rows.count).end(xlup).row)     origstring3 = range("c2:c" & range("a" & rows.count).end(xlup).row)     origstring4 = range("d2:d" & range("a" & rows.count).end(xlup).row)      x = 2      y = lbound(origstring1) ubound(origstring1)          mystr1 = split(origstring1(y), ";")         mystr2 = split(origstring2(y), ";")         mystr3 = split(origstring3(y), ";")         mystr4 = split(origstring4(y), ";")          each str1 in mystr1             each str2 in mystr2                 each str3 in mystr3                     each str4 in mystr4                         range("a" & x).formula = str1                         range("b" & x).formula = str2                         range("c" & x).formula = str3                         range("d" & x).formula = str4                         x = x + 1                     next                 next             next         next      next end sub

or there better way deal using sql? or better way achieve cartesian product of every row using vba?

something works. not find more elegant solution.

sub cartesian()     dim mystr1() string     dim mystr2() string     dim mystr3() string     dim mystr4() string     dim x long     dim origstring1() string     dim origstring2() string     dim origstring3() string     dim origstring4() string     dim y long      dim sht worksheet     set sht = worksheets("sheet1")      redim origstring1(1 range("a" & rows.count).end(xlup).row - 1)      y = 1 ubound(origstring1)         origstring1(y) = cstr(range("a" & cstr(y + 1)).value)     next      redim origstring2(1 range("b" & rows.count).end(xlup).row - 1)      y = 1 ubound(origstring2)         origstring2(y) = cstr(range("b" & cstr(y + 1)).value)     next      redim origstring3(1 range("c" & rows.count).end(xlup).row - 1)      y = 1 ubound(origstring3)         origstring3(y) = cstr(range("c" & cstr(y + 1)).value)     next      redim origstring4(1 range("d" & rows.count).end(xlup).row - 1)      y = 1 ubound(origstring4)         origstring4(y) = cstr(range("d" & cstr(y + 1)).value)     next      x = 2      y = lbound(origstring1) ubound(origstring1)          mystr1() = split(origstring1(y), ";")         mystr2() = split(origstring2(y), ";")         mystr3() = split(origstring3(y), ";")         mystr4() = split(origstring4(y), ";")          each str1 in mystr1             each str2 in mystr2                 each str3 in mystr3                     each str4 in mystr4                         range("a" & x).formula = str1                         range("b" & x).formula = str2                         range("c" & x).formula = str3                         range("d" & x).formula = str4                         x = x + 1                     next                 next             next         next     next end sub

Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more