vba - For loop doesn't run in worksheet -


i tried link common columns when click cell in policycomponents sheet, loop doesn't run - exits sub.

code snippet:

sub linkname()  dim long dim shtusedrange, shtusedrangecol dim name string dim name1 string dim lookup_range range dim box   shtusedrange = activesheet.usedrange.rows.count 'count used rows in activesheet shtusedrangecol = activesheet.usedrange.columns.count 'count used column in activesheet  name = activecell.row 'row of selected cell  name1 = activesheet.cells(name, 1).value 'name of row selected  'msgbox name1  set lookup_range = thisworkbook.sheets("policydetails").range("a1:z5000") 'set range of policy details search  box = application.worksheetfunction.vlookup(name1, lookup_range, 1, false) 'to match name policy details  msgbox box  = 1 thisworkbook.sheets("policycomponents").rows.count step -1  if thisworkbook.sheets("policycomponents").cells(i, 1).value = box  thisworkbook.sheets("policy viewer").cells(16, 2).value = thisworkbook.sheets("policycomponents").cells(i, 4).value  end if     next     end sub

  1. you using name string type variable assigning row number value. means name "2" , not 2 , cannot used when number required. never idea call variables same reserved words vba's .name.
  2. you using step -1 starting @ 1 means never go anywhere.

that should enough loop going.

sub linkname()      dim long     dim shtusedrange, shtusedrangecol     dim rw long     dim lu variant     dim lookup_range range     dim box variant       'count used rows in activesheet     shtusedrange = activesheet.usedrange.rows.count      'count used column in activesheet     shtusedrangecol = activesheet.usedrange.columns.count      'row of selected cell     rw = activecell.row      'name of row selected     lu = activesheet.cells(rw, 1).value      'msgbox lu      'set range of policy details search     set lookup_range = thisworkbook.sheets("policydetails").range("a1:z5000")      'there no error control here if there no match     'to match name policy details     box = application.worksheetfunction.vlookup(lu, lookup_range, 1, false)      msgbox box      = 1 thisworkbook.sheets("policycomponents").rows.count step 1         if thisworkbook.sheets("policycomponents").cells(i, 1).value = box             thisworkbook.sheets("policy viewer").cells(16, 2) = _               thisworkbook.sheets("policycomponents").cells(i, 4).value             'probably best exit hte loop here unless want try , catch other matches             'exit         end if     next  end sub

i renamed 2 of variables. didn't know nature of value (number/text/date) trying left variant.


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