i have userform patient registration, in said userform there combobox selecting primary doctor way generate patient id based on doctor selection, dont start want code evaluate last id same prefix generate next 1 e.g.
kt000001 kt000002 lg000001 here userform code
private sub commandbutton1_click() dim irow long dim ws worksheet set ws = worksheets("lista pacientes") 'find first empty row in database irow = ws.cells.find(what:="*", searchorder:=xlrows, _ searchdirection:=xlprevious, lookin:=xlvalues).row + 1 'check valid patient name if trim(me.textbox1.value) = "" me.textbox1.setfocus msgbox "favor introducir nombre" exit sub end if ws .cells(irow, 2).value = me.textbox1.value .cells(irow, 3).value = me.textbox2.value .cells(irow, 4).value = me.textbox3.value .cells(irow, 5).value = me.textbox4.value .cells(irow, 7).value = me.textbox5.value .cells(irow, 8).value = me.textbox6.value .cells(irow, 10).value = me.textbox7.value .cells(irow, 11).value = me.textbox8.value .cells(irow, 12).value = me.textbox9.value .cells(irow, 13).value = me.textbox10.value .cells(irow, 14).value = me.textbox11.value .cells(irow, 15).value = me.textbox12.value .cells(irow, 16).value = me.textbox13.value .cells(irow, 17).value = me.combobox1.value .cells(irow, 6).formulalocal = "=concatenar(limpiar(espacios(b" & irow & "));si(limpiar(espacios(c" & irow & "))="""";"""";"" "");limpiar(espacios(c" & irow & "));si(limpiar(espacios(d" & irow & "))="""";"""";"" "");limpiar(espacios(d" & irow & "));si(limpiar(espacios(e" & irow & "))="""";"""";"" "");limpiar(espacios(e" & irow & ")))" .cells(irow, 9).formulalocal = "=sifecha(h" & irow & ";hoy();""y"")" end 'clear data me.textbox1.value = "" me.textbox2.value = "" me.textbox3.value = "" me.textbox4.value = "" me.textbox5.value = "" me.textbox6.value = "" me.textbox7.value = "" me.textbox8.value = "" me.textbox9.value = "" me.textbox10.value = "" me.textbox11.value = "" me.textbox12.value = "" me.textbox13.value = "" me.combobox1.value = "" me.textbox1.setfocus unload me end sub private sub commandbutton2_click() unload me end sub
you go follows (mind comments):
option explicit private sub commandbutton1_click() dim irow long dim ws worksheet dim idcol long '<~~ hold unique patient id column index dim ndoc long '<~~ count number of occurrences of chosen doctor id in patient id column dim docid string '<~~ holed doctor id value (retrieved combobox1) 'check valid patient name. <~~ @ beginning of sub, not run code uselessly if trim(me.textbox1.value) = "" me.textbox1.setfocus msgbox "favor introducir nombre" exit sub end if idcol = 17 '<~~ column index write unique patient ids. change needs docid = me.combobox1.value '<~~ retrieve doctor id value combobox1 set ws = worksheets("lista pacientes") ws 'find first empty row in database irow = .cells.find(what:="*", searchorder:=xlrows, searchdirection:=xlprevious, lookin:=xlvalues).row + 1 ndoc = worksheetfunction.countif(.cells(1, idcol).resize(irow), docid & "*") '<~~ count number of occurrences of chosen doctor id in patient id column .cells(irow, idcol).value = docid & format(ndoc + 1, "000000") '<~~ write patient unique id '...rest of code here end '...rest of code here end sub 