Excel vba can't find date when entered by user -

-

i'm still new vba , code worked earlier today reason stopped finding date in selected column if present. i'm reading through each row , looking 2 criteria (1. date migration, 2. network). once done, copy rows depending on criteria separate worksheets , consequently save them later on. problem despite date enter, doesn't find anymore - , make sure enter in dd/mm/yyyy format that's underlying format. despite put it boots me err_execute.

here kind of data i'm working with:

  • cola pcname (i.e. john's machine)
  • colb user name (i.e. john doe)
  • colc devicetype (i.e. laptop)
  • cold network (i.e. jody's network)
  • cole migration wave (i.e. 1st wave)
  • colf top user of device
  • colg last person login
  • colh location of device
  • coli migration date (looked in workbook still formula) colj user's email
  • colk sr#
  • coll migration date (copied value instead of formula)
sub test()  dim lsearchrow integer dim lcopytorow1 integer dim lcopytorow2 integer dim lsearchvalue string on error goto err_execute sheets("confirmed devices").activate range("i2:i10000").select selection.copy range("l2:l10000").pastespecial xlpastevalues sheets.add.name = "jody" sheets.add.name = "jason" lsearchvalue = inputbox("which migration date wish prepare    files for?", "the format has dd/mm/yyyy.")  lsearchrow = 2 lcopytorow1 = 1 lcopytorow2 = 1  while len(range("a" & cstr(lsearhrow)).value) > 0 if range("l" & cstr(lsearchrow)).value = lsearchvalue , range("d" & cstr  (lsearchrow)).value = "jody's network"  rows(cstr(lsearchrow) & ":" & cstr(lsearchrow)).select  selection.copy  sheets("jody").select  rows(cstr(lcopytorow1) & ":" & cstr(lcopytorow1)).select  sheets("jody").paste  lcopytorow1 = lcopytorow1 + 1  sheets("confirmed devices").select  end if  if range("l" & cstr(lsearchrow)).value = lsearchvalue , range("d" & cstr(lsearchrow)).value = "jason"      rows(cstr(lsearchrow) & ":" & cstr(lsearchrow)).select      selection.copy      sheets("jason").select      rows(cstr(lcopytorow2) & ":" & cstr(lcopytorow2)).select      sheets("jason").paste     lcopytorow2 = lcopytorow2 + 1      sheets("confirmed devices").select  end if  lsearchrow = lsearchrow + 1  wend  'msgbox "all matching data has been copied."  exit sub  err_execute:  msgbox "that date not found."`

any in finding out why doesn't find date anymore appreciated.

the error handeler isn't working though, despite value put input (lsearchvalue), still msg data has been copied. other fixing typo, changed code little bit in hopes of getting error handeler work. able help? there perhaps better way deal value can't found in 'l' column?

sub test()  application.screenupdating = false application.displayalerts = false  dim lsearchrow integer dim lcopytorow1 integer dim lcopytorow2 integer dim lsearchvalue string dim fname1 string dim fname2 string dim fpath string dim newbook1 workbook dim newbook2 workbook  sheets("confirmed devices").activate range("i2:i10000").select selection.copy range("l2:l10000").pastespecial xlpastevalues  on error goto err_execute  lsearchvalue = inputbox("which migration date wish prepare files for?", "the format has dd/mm/yyyy.") lsearchrow = 2 lcopytorow1 = 1 lcopytorow2 = 1 sheets.add.name = "jodi" sheets.add.name = "jason"   while len(range("a" & cstr(lsearchrow)).value) > 0  if range("l" & cstr(lsearchrow)).value = lsearchvalue , range("d" & cstr(lsearchrow)).value = "jodi’s network”   rows(cstr(lsearchrow) & ":" & cstr(lsearchrow)).select   selection.copy   sheets("jodi").select   rows(cstr(lcopytorow1) & ":" & cstr(lcopytorow1)).select   sheets("jodi").paste   lcopytorow1 = lcopytorow1 + 1   sheets("confirmed devices").select   elseif range("l" & cstr(lsearchrow)).value = lsearchvalue , range("d" & cstr(lsearchrow)).value = "jason’s network"   rows(cstr(lsearchrow) & ":" & cstr(lsearchrow)).select   selection.copy   sheets("jason").select   rows(cstr(lcopytorow2) & ":" & cstr(lcopytorow2)).select   sheets("jason").paste   lcopytorow2 = lcopytorow2 + 1   sheets("confirmed devices").select   end if  lsearchrow = lsearchrow + 1  wend  msgbox "all matching data has been copied."   exit sub  err_execute: msgbox "there no migrations date"  end sub


Comments

Post a Comment

Popular posts from this blog

qt - Using float or double for own QML classes -

-
i've created components , helper methods using c++ , qt used in qml gui. of methods calculate gui elements (size, transformations, scene graph items creation). target arm 32 bit using opengl / eglfs render output. opengl using float , qml using real (defined double ). what should use in c++ do not lose precision? if use qreal ( double ) shouldn't lose precision. if git grep float in qtbase.git , can find out bit more: dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0-* important behavior changes * dist/changes-5.2.0-**************************************************************************** dist/changes-5.2.0- dist/changes-5.2.0- - qt compiled qreal typedef'ed double on dist/changes-5.2.0: platforms. qreal float on arm chipsets before. guarantees more dist/changes-5.2.0- consistent behavior between platforms qt supports, binary dist/chan...
Read more

Create Outlook appointment via C# .Net -

-
i need create outlook appointment using microsoft.office.interop.outlook , while can work locally on own workstation , works if run web application via server's browser, not work when connect server externally. hence, think might permission issue. i changed application pool identity " localsystem " don't access denied error. unfortunately, doesn't work. the app behaves if appointment created, appointment doesn't appear in outlook. here include file on top of aspx.cs page using outlook = microsoft.office.interop.outlook; here code using pop appointment. outlook.application apptapp = new outlook.application(); outlook.appointmentitem appt = apptapp.createitem(outlook.olitemtype.olappointmentitem) outlook.appointmentitem; appt.subject = txtfirstname.text + " " + txtlastname.text; appt.body = txtcomment.text; appt.alldayevent = false; appt.start = datetime.parse(txtreminderdate.text + " 8:00 am"); appt.end = datetime.p...
Read more

ios - Swift Array Resetting Itself -

-
i have 2 arrays: pointhistory , datehistory . have nstimer , , when timer runs out, action called. in action appending int pointhistory , , current date string datehistory . here timer action: func timeraction() { let steppervalue = int(stepper.value) employee.numberofpoints = steppervalue networking.saveemployee(employee, completion: { (error) -> void in if error != nil { let alert = uialertcontroller(title: "error", message: "there error updating \(self.employee.name)'s points", preferredstyle: .alert) let alertaction = uialertaction(title: "ok", style: .cancel, handler: nil) alert.addaction(alertaction) self.presentviewcontroller(alert, animated: true, completion: nil) } }) var dateformatter = nsdateformatter() dateformatter.dateformat = "mm-dd-yyyy" dateformatter.timezone = nstimezone.localtimezone() var date = nsdate() var ...
Read more