excel - Every 5 seconds record the value in 2 cells in another worksheet VBA -

problem: have searched extensively , cannot seem work. have timer running when "startbtn" pressed:

dim stoptimer           boolean dim schdtime            date dim etime               date dim currentcost         integer const onesec            date = 1 / 86400#  private sub resetbtn_click()     stoptimer = true     etime = 0     [textbox21].value = "00:00:00" end sub  private sub startbtn_click()    stoptimer = false    schdtime = now()    [textbox21].value = format(etime, "hh:mm:ss")    application.ontime schdtime + onesec, "sheet1.nexttick" end sub  private sub stopbtn_click()     stoptimer = true     beep end sub  sub nexttick()    if stoptimer       'don't reschedule update    else       [textbox21].value = format(etime, "hh:mm:ss")       schdtime = schdtime + onesec       application.ontime schdtime, "sheet1.nexttick"       etime = etime + onesec    end if end sub 

then in cell (say, c16) have manually entered value hourly cost rate. have third cell calculating total cost c16*current timer value.

what want record every 5 seconds after "startbtn" clicked current time , current calculated cost in sheet. have started:

sub increment() dim x string dim n integer dim recordnext date  n = 0 record = [textbox21].value recordnext = [textbox21].value + onesec  range("b13").value = recordnext  until isempty(b4)     if [textbox21].value = recordnext activecell.copy       application.goto(activeworkbook.sheets("sheet2").range("a1").offset(1, 0))         activesheet.paste         application.cutcopymode = false         n = n + 1         recordnext = [textbox21].value + 5 * (onesec)     exit     end if     activecell.offset(1, 0).select loop end sub 

but doesnt work. appreciated.

i have tried simplify timer method down needed.

sheet1 code sheet

option explicit  private sub resetbtn_click()     brun_timer = false     'use following if want remove last time cycle     textbox21.value = format(0, "hh:mm:ss") end sub  private sub startbtn_click()     brun_timer = true     dtime_start =     textbox21.value = format(now - dtime_start, "hh:mm:ss")     range("d16").clearcontents     call next_increment end sub 

module1 code sheet

option explicit  public brun_timer boolean public const isecs integer = 3  'three seconds public dtime_start date  sub next_increment()     worksheets("sheet1")         .textbox21.value = format(now - dtime_start, "hh:mm:ss")         .range("d16") = sheet1.range("c16") / 3600 * _                               second(timevalue(sheet1.textbox21.value)) '# of secs × rate/sec         worksheets("sheet2").cells(rows.count, 1).end(xlup).resize(1, 2).offset(1, 0) = _             array(.textbox21.value, .range("d16").value)     end      if brun_timer _         application.ontime + timeserial(0, 0, isecs), "next_increment" end sub 

note operation of transferring data sheet2 direct value transfer¹ no .goto, activecell or select.

it not entirely clear me trying value transfer. have stacked them 1 after on sheet1.

        

you benefit adding option explicit² top of code sheets. requires variable declaration , if misplace public variable, know.

---

¹ _{see how avoid using select in excel vba macros more methods on getting away relying on select , activate accomplish goals.}

² _{setting require variable declaration within vbe's tools ► options ► editor property page put option explicit statement @ top of each newly created code sheet. avoid silly coding mistakes misspellings influencing use correct variable type in variable declaration. variables created on-the-fly without declaration of variant/object type. using option explicit considered 'best practice'.}