database - Creating a secondary reference based on destination sheet -
i have dilemma. have code here works simple data entry. pop information in , click button on userform information gets sent mastersheet, 1 or 2 of other 3 sheets may need depending on specific qualifying aspects.
the issue have here mastersheet has reference next number in sequence. should data entered qualify ws2 wanting generate reference number again next number in sequence (within sheet only).
below code, able assit?
thanks,
dim mrow long dim ws1 worksheet dim ws2 worksheet dim ws3 worksheet dim ws4 worksheet dim nextnum long dim xnum long set ws1 = worksheets("masterdata") set ws2 = worksheets("x") set ws3 = worksheets("a") set ws4 = worksheets("c") nextnum = sheets("masterdata").range("a2").end(xldown).value + 1 'xnum = sheets("x").range("a2").end(xldown).value + 1 dim targetworksheets variant '16 qualifying scenarios determine data sent select case true case combopd.value = "y" , combonp.value = "y" , txtcval.value >= 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) <= 1: targetworksheets = array(ws1, ws2, ws3) case combopd.value = "y" , combonp.value = "y" , txtcval.value >= 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) > 1: targetworksheets = array(ws1, ws2, ws3) case combopd.value = "y" , combonp.value = "y" , txtclaimval.value < 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) <= 1: targetworksheets = array(ws1, ws3) case combopd.value = "y" , combonp.value = "y" , txtcval.value < 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) > 1: targetworksheets = array(ws1, ws4) case combopd.value = "y" , combonp.value = "n" , txtcval.value >= 50 , datevalue(me.td.value) - datevalue(me.txtdd.value) <= 3: targetworksheets = array(ws1, ws2, ws3) case combopd.value = "y" , combonp.value = "n" , txtcval.value >= 50 , datevalue(me.td.value) - datevalue(me.txtdd.value) > 3: targetworksheets = array(ws1, ws2, ws4) case combopd.value = "y" , combonp.value = "n" , txtcval.value < 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) <= 3: targetworksheets = array(ws1, ws3) case combopd.value = "y" , combonp.value = "n" , txtcval.value < 50 , datevalue(me.td.value) - datevalue(me.txtdd.value) > 3: targetworksheets = array(ws1, ws4) case combopd.value = "n" , combonp.value = "y" , txtcval.value >= 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) <= 1: targetworksheets = array(ws1, ws3) case combopd.value = "n" , combonp.value = "y" , txtcval.value >= 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) > 1: targetworksheets = array(ws1, ws4) case combopd.value = "n" , combonp.value = "y" , txtcval.value < 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) <= 1: targetworksheets = array(ws1, ws3) case combopd.value = "n" , combonp.value = "y" , txtcval.value < 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) > 1: targetworksheets = array(ws1, ws4) case combopd.value = "n" , combonp.value = "n" , txtcval.value >= 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) <= 3: targetworksheets = array(ws1, ws3) case combopd.value = "n" , combonp.value = "n" , txtcval.value >= 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) > 3: targetworksheets = array(ws1, ws4) case combopd.value = "n" , combonp.value = "n" , txtcval.value < 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) <= 3: targetworksheets = array(ws1, ws3) case combopd.value = "n" , combonp.value = "n" , txtcval.value < 50 , datevalue(me.txtrd.value) - datevalue(me.txtdd.value) > 1: targetworksheets = array(ws1, ws4) case else: targetworksheets = array(ws1) end select each ws in targetworksheets 'find first empty row in worksheets mrow = ws.cells.find(what:="*", searchorder:=xlrows, _ searchdirection:=xlprevious, lookin:=xlvalues).row + 1 'copy userform info data qualifying database sheets ws.cells(mrow, 1).value = nextnum ws.cells(mrow, 2).value = format(date, "dd/mm/yyyy") ws.cells(mrow, 3).value = format(time, "hh:mm:ss") ws.cells(mrow, 4).value = cint(format(date, "ww")) ws.cells(mrow, 5).value = format(date, "mmm-yy") ws.cells(mrow, 6).value = cint(format(date, "yyyy")) ws.cells(mrow, 7).value = 1 ws.cells(mrow, 8).value = txtwt.value * (1300 / 1000) ws.cells(mrow, 9).value = application.worksheetfunction.vlookup(combobd.value, sheets("lookup vals").range("g:h"), 2, false) ws.cells(mrow, 10).value = application.username if combobd.value = "mn" ws.cells(mrow, 11).value = application.worksheetfunction.vlookup(combocompany.value, sheets("lookup vals").range("l:n"), 2, false) else if combobd.value = "pur" ws.cells(mrow, 11).value = application.worksheetfunction.vlookup(combocompany.value, sheets("lookup vals").range("p:r"), 2, false) else if combobd.value = "vog" ws.cells(mrow, 11).value = application.worksheetfunction.vlookup(combocom.value, sheets("lookup vals").range("p:r"), 2, false) ws.cells(mrow, 12).value = format(me.txtrd.value, "dd/mm/yyyy") ws.cells(mrow, 13).value = combopd.value ws.cells(mrow, 14).value = combonp.value ws.cells(mrow, 15).value = combobd.value ws.cells(mrow, 16).value = combocom.value ws.cells(mrow, 17).value = txtadditional.value ws.cells(mrow, 18).value = format(me.txtdd.value, "dd/mm/yyyy") ws.cells(mrow, 19).value = txtbn.value ws.cells(mrow, 20).value = txtfs.value ws.cells(mrow, 21).value = combopr.value ws.cells(mrow, 22).value = combois.value ws.cells(mrow, 23).value = txtun.value ws.cells(mrow, 24).value = txtwt.value ws.cells(mrow, 25).value = txtin.value ws.cells(mrow, 26).value = txtdt.value ws.cells(mrow, 27).value = txtshp.value next ws
how function:
function getnextid(ws worksheet, col variant) long getnextid = worksheetfunction.max(ws.columns(col)) + 1 end function call this:
nextnum = getnextid(sheets("masterdata"), "a") ' or: nextnum = getnextid(sheets("masterdata"), 1) xnum = getnextid(sheets("x"), "a"))
Comments
Post a Comment