vba - Remove selected numbers from a comma separated list management in Excel? -

-

this might little tricky, vba...

i have comma separated lists in cells based on start times on 5 minutes intervals need remove times 5 apart.

the numbers text, not time @ point. example, 1 list 2210, 2215, 2225, 2230, 2240 (the start times).

in case, 2215 , 2230 should removed need remove opposite numbers (i.e.,2210 , 2225) in other cases (the end times).

someone helped me specs:

a cell contains times: t(1), t(2), t(3), ... t(n). starting @ time t(1), each value in list examined. if t(x) less 6 minutes after t(x-1) delete t(x) , renumber t(x+1) t(n).

input:

2210, 2215, 2225, 2230, 2240

output:

column1: 2210 column2: 2240

this think require.

option explicit sub deleteselectedtimes()    dim rowcrnt long    rowcrnt = 2    while cells(rowcrnt, 1).value <> ""     cells(rowcrnt, 1).value = processsinglecell(cells(rowcrnt, 1).value, 1)     cells(rowcrnt, 2).value = processsinglecell(cells(rowcrnt, 2).value, -1)     rowcrnt = rowcrnt + 1   loop  end sub function processsinglecell(byval cellvalue string, byval stepfactor long) string    dim celllist() string   dim celllistcrntstg string   dim celllistcrntnum long   dim inxcrnt long   dim inxend long   dim inxstart long   dim timecrnt long    ' time in minutes   dim timelast long    ' time in minutes    celllist = split(cellvalue, ",")    if stepfactor = 1     inxstart = lbound(celllist)     inxend = ubound(celllist)   else     inxstart = ubound(celllist)     inxend = lbound(celllist)   end if    celllistcrntstg = trim(celllist(inxstart))   if (not isnumeric(celllistcrntstg)) or instr(celllistcrntstg, ".") <> 0     ' either sub-value not numeric or if contains decimal point     ' either way cannot time.     processsinglecell = cellvalue     exit function   end if   celllistcrntnum = val(celllistcrntstg)   if celllistcrntnum < 0 or celllistcrntnum > 2359     ' value not time formatted hhmm     processsinglecell = cellvalue     exit function   end if   timelast = 60 * (celllistcrntnum \ 100) + (celllistcrntnum mod 100)    inxcrnt = inxstart + stepfactor inxend step stepfactor     celllistcrntstg = trim(celllist(inxcrnt))     if (not isnumeric(celllistcrntstg)) or instr(celllistcrntstg, ".") <> 0       ' either sub-value not numeric or if contains decimal point       ' either way cannot time.       processsinglecell = cellvalue       exit function     end if     celllistcrntnum = val(celllistcrntstg)     if celllistcrntnum < 0 or celllistcrntnum > 2359       ' value not time formatted hhmm       processsinglecell = cellvalue       exit function     end if     timecrnt = 60 * (celllistcrntnum \ 100) + (celllistcrntnum mod 100)     if abs(timecrnt - timelast) < 6       ' delete unwanted time list       celllist(inxcrnt) = ""     else       ' current time becomes last time next loop       timelast = timecrnt     end if   next    cellvalue = join(celllist, ",")    if left(cellvalue, 1) = ","     cellvalue = mid(cellvalue, 2)     cellvalue = trim(cellvalue)   end if    while instr(cellvalue, ",,") <> 0     cellvalue = replace(cellvalue, ",,", ",")   loop    processsinglecell = cellvalue  end function

explanation

sorry lack of instructions in first version. assumed question more technique manipulating data vba.

deleteselectedtimes operates on active worksheet. easy change work on specific worksheet or range of worksheets if require.

deleteselectedtimes ignores first row assume contains column headings. test worksheet has headings in row 1. processes columns , b of every row until reaches row empty column a.

processsinglecell has 2 parameters: string , direction. deleteselectedtimes uses direction values in column processed left right while values in column b processed right left.

i assume #value error because processsinglecell not check string of format "number,number,number". have changed processsinglecell if string not of format, change string.

i have no clear idea of or not know come more questions necessary.


Comments

Post a Comment

Popular posts from this blog

jasper reports - Fixed header in Excel using JasperReports -

-
how can create excel report fixed header using japserreports? mean need header fixed when scroll excel file. this possible adding couple properties in jrxml file each report. take @ advanced excel features freeze panes. if wanted freeze after first column header (down left side basically) this: <statictext> <reportelement style="sans_bold" mode="opaque" x="0" y="60" width="104" height="20" forecolor="#ffffff" backcolor="#666666"> <property name="net.sf.jasperreports.export.xls.auto.filter" value="start"/> <property name="net.sf.jasperreports.export.xls.column.width" value="110"/> <property name="net.sf.jasperreports.export.xls.freeze.column.edge" value="left"/> </reportelement> &
Read more

media player - Android: mediaplayer went away with unhandled events -

-
i need duration of audio file series of voice announcements need play app. have added audio files resources , play fine. sample code below works perfect intended purpose: return duration of audio files. here code: float getdurationofaudioresource(locationenum loc, context context){ float duration = 0; try { mediaplayer mp; mp = mediaplayer.create(context, getaudioresource(loc)); duration = mp.getduration(); mp.release(); mp = null; } catch (illegalstateexception e) {e.printstacktrace(); logerror(25, "testdescitem:fault::could not open mediaplayer object audio resource.");} return duration; } here's weird thing. code called in main activity prepares set of audio instructions given test. there no errors within activity. second activity called, long string of errors on logcat. 03-07 13:23:43.820: i/actionlogger(21435): gentest_info_test #0 created. 03-07 13:23:43.830: i/actionlogger(21435): gente
Read more

python - ('The SQL contains 0 parameter markers, but 50 parameters were supplied', 'HY000') or TypeError: 'tuple' object is not callable -

-
import pyodbc,nltk,array cnxn = pyodbc.connect('driver={mysql odbc 5.1 driver};server=127.0.0.1;port=3306;database=information_schema;user=root; password=1234;option=3;') cursor = cnxn.cursor() cursor.execute("use collegedatabase ;") cursor.execute("select * sampledata ; ") cnxn.commit() s=[] j=[] x=[] words = [] w = [] sfq = [] pos=[] entry in cursor: s.append(entry.injury_type),j.append(entry.injury_desc) nltk.tokenize import punktwordtokenizer nltk.corpus import stopwords tokenizer = punktwordtokenizer() english_stops = set(stopwords.words('english')) in range(0,26): # filter stop words tokenizer.tokenize(j[i]) w.append([word word in tokenizer.tokenize(j[i]) if word not in english_stops]) in range(0 , 26):#converting tokenzied text ito string sfq.append(" ".join(w[a])) replacers import regexpreplacer replacer = regexpreplacer() in range (0,26):#pos tagging replacer.repl
Read more