Error 9: subscript out of range in VBA excel -


i'm amending vba codes generating server type , host name according system name, of server types , host names empty or showing outliers such loopback , netbackup. , 1 big problem couldn't figure out every system name including kul, sever type , host name not showing anything, means blank. have posted part related kul , if guys find error-free post other parts. appreciate lot helping me out such error, thanks.

here sample data still showing blanks , outliers:

server_type host_name system_name    mount_point          size    size_mb   size_gb  size_tb             loopback  loopback:kux  /alliance/webplatform 6144    6144             netbackup netbackup:kux /opt                  8070    8070

here's whole part reference:)

private sub commandbutton1_click() dim total_row integer dim systemname string 'column c dim newstring string dim tabname string dim hostname string 'dim servertype string  'hostname , server type column total_row = worksheetfunction.counta(range(range("c2"), range("c2").end(xldown))) = 2 total_row + 1     systemname = cells(i, 3).value     'copy size size_mb column     cells(i, 6).value = cells(i, 5).value     'take size_mb/1024 size_gb     cells(i, 7).value = cells(i, 6).value / 1024     'take size_gb/1024 size_tb     cells(i, 8).value = cells(i, 7).value / 1024     'tab_name column s(19)     tabname = cells(i, 19).value     select case tabname         case "linux"             hostname = left(systemname, worksheetfunction.find(":lz", systemname) - 1)             cells(i, 2).value = hostname             cells(i, 1).value = getservertype(hostname)         case "windows"             hostname = mid(systemname, worksheetfunction.find("primary:", systemname) + 8, _                        worksheetfunction.find(":nt", systemname) - worksheetfunction.find("primary:", systemname) - 8)             cells(i, 2).value = hostname             cells(i, 1).value = getservertype(hostname)         case "unix"     case "unix"     if cbool(instr(1, systemname, ":kux", vbtextcompare))         hostname = left(systemname, instr(1, systemname, ":kux", vbtextcompare) - 1)     elseif cbool(instr(1, systemname, ":kul", vbtextcompare))         hostname = left(systemname, instr(1, systemname, ":kul", vbtextcompare) - 1)     else         debug.print systemname     end if     cells(i, 2).value = hostname     cells(i, 1).value = getservertype(hostname)    end select     'column m(13) = space used percent, 'column o(15) = greater 85%    if (cells(i, 13).value > 85)         cells(i, 15).value = "yes"    else         cells(i, 15).value = "no"    end if     'column q(17) = timestamp, column r(18) = date    cells(i, 18).value = mid(cells(i, 17), 5, 4)    'to generate month column p (16)    cells(i, 16).value = getmonth(left(cells(i, 18), 2))     'application name - cep (column t)(20)  (note maybe need check if cannot find application display #na)    'note vloopup must minus 3 cause loopup start column d    cells(i, 20).value = application.vlookup(hostname, sheet3.range("d:v"), 19, false)    'lob (bu) - cep (column u)(21)    cells(i, 21).value = application.vlookup(hostname, sheet3.range("d:x"), 21, false)    'system purpose (column v)(22)    cells(i, 22).value = application.vlookup(hostname, sheet3.range("d:v"), 16, false)    'tower (column w)(23)    cells(i, 23).value = application.vlookup(hostname, sheet3.range("d:y"), 22, false)    'harddisk (column x)(24) (from column m in mastercep)    cells(i, 24).value = application.vlookup(hostname, sheet3.range("d:v"), 10, false)    'hw model (column y)(25)    cells(i, 25).value = application.vlookup(hostname, sheet3.range("d:v"), 5, false)    'masterbu (column z)(26)    cells(i, 26).value = application.vlookup(cells(i, 21).value, worksheets("masterbu").range("a:b"), 2, false)    'ts/others (column ac)(29)    cells(i, 29).value = application.vlookup(hostname, sheet2.range("a:b"), 2, false)    'sl (column ad)(30)    cells(i, 30).value = application.vlookup(hostname, sheet3.range("d:w"), 20, false)     'mount_check (column ae)(31)    'note: cells(i,4) mount_point column    'index match    if (application.worksheetfunction.isna(application.vlookup(cells(i, 2), worksheets("indexmatch").range("b:b"), 1, false)))          cells(i, 31).value = "#n/a"    else         cells(i, 31).value = application.index(worksheets("indexmatch").range("g:g"), application.match(cells(i, 4), worksheets("indexmatch").range("d:d"), 0))    end if  next  end sub  function getservertype(host_name string) string  select case left(host_name, 1)     case "a", "a", "p"         getservertype = "aix"     case "s", "s"         getservertype = "sun"     case "x", "x", "w", "w", "p"         getservertype = "wintel"     case else         getservertype = "" end select end function  function getmonth(twodigitmonth string) string select case twodigitmonth     case "01"         getmonth = "jan"     case "02"         getmonth = "feb"     case "03"         getmonth = "mar"     case "04"         getmonth = "apr"     case "05"         getmonth = "may"     case "06"         getmonth = "jun"     case "07"         getmonth = "jul"     case "08"         getmonth = "aug"     case "09"         getmonth = "sep"     case "10"         getmonth = "oct"     case "11"         getmonth = "nov"     case "12"         getmonth = "dec" end select end function


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