Excel VBA: How to push data to an exclusive range on another sheet? -

Excel VBA: How to push data to an exclusive range on another sheet? -

i utilize sub below part of sub error checks , saves grade sheet. there @ to the lowest degree 39 grade sheets generated throughout course of study built for. each event may accomplished more 1 time - weather cancel, or pupil failed.

the findemptyinsertdata sub takes 3 of import info grade sheet (date, grade, status) , adds them sheet named index. index keeps running tally on events accomplished...based solely on output of 'error check , save' macro.

my question more of logic question, rather specific code (although it'll help). findemptyinsertdata works wonderfully. however, info gets pushed , added index many times user clicks 'error check , save' form command button. pushed 1 time per grade sheet...the problem/challenge user might need go , alter grade sheet (wrong date, different status...etc).

index looks this:

event abc-1 abc-2 def-1 def-2 date dd-mmm dd-mmm dd-mmm dd-mmm grade 1 2 2 3 status wx eff eff eff ---- ---- ---- ---- date dd-mmm grade 3 status eff

i'm thinking solution lie in fact 1 event ever attempted/accomplished per day. therefore...if date of gradesheet matches date in index, don't force info again...except if grade or status changes. ugh, brain hurts!

thanks in advance!

sub findemptyinsertdata() dim ws worksheet dim gsdate date dim gsworking string dim gsmsnnum string dim colnum integer gsworking = activewindow.activesheet.name gsdate = activesheet.range("s3") gsgrade = activesheet.range("d40") gsstatus = activesheet.range("o7") gsmsnnum = activesheet.range("d3") application.screenupdating = false 'opens index (sheet4) , finds first empty cell sheet4.activate sheet4.unprotect 'finds sortie name column in index each cell in activesheet.rows(5).cells if cell = gsmsnnum cell.select: exit next cell 'takes active column number , assigns variable colnum = activecell.column 'finds first open cell in column each cell in activesheet.columns(colnum).cells if len(cell) = 0 cell.select: exit next cell activecell.value = gsdate 'prints date gs first empty cell activecell.numberformat = "dd-mmm" selection.offset(1, 0).select 'moves 1 cell downwards activecell.value = gsgrade 'prints grade gs selection.offset(1, 0).select 'moves 1 cell downwards activecell.value = gsstatus 'prints status gs activecell.borders(xledgebottom).weight = xlmedium 'adds bottom border 'protects index page sheet4.protect 'returns open gs worksheets(gsworking).activate end sub

without seeing index , other code involved, best recommendation make.

conceptually, far simple database design goes, might benefit adding 5th row event sequence. add together integer variable code looks sequence , when assigning info event. increment 1 when adding event info index. if hate thought of consuming entire row can tell code hide row.

this way can have many entries containing same or different info necessary. can take take "event" , highest number "sequence" final submitted grade default.

event abc-1 abc-2 def-1 def-2 ------------------------------------- seq 1 1 1 1 ' <--- pull info event name & sequence date dd-mmm dd-mmm dd-mmm dd-mmm grade 1 2 2 3 status wx eff eff eff ---- ---- ---- ---- seq 2 ' <--- pull info event name & sequence date dd-mmm grade 3 status eff

additionally, add together row index contain info event active 1 have pushed grade sheet.

event abc-1 abc-2 def-1 def-2 ------------------------------------- seq 1 1 1 1 ' <--- pull info event name & sequence date dd-mmm dd-mmm dd-mmm dd-mmm grade 1 2 2 3 status wx eff eff eff active 0 1 1 1 ---- ---- ---- ---- seq 2 ' <--- pull info event name & sequence date dd-mmm grade 3 status eff active 1

excel vba excel-vba