please can me correct code eliminate overflow error when there 1 cell meets selection criteria in code below? cell a4 , down contains description of product , cell b4 , down contain quantity. intent copy description a4 column l many times corresponds quantity in b4 , repeat process many times there product descriptions in column a. works when there more 1 description copied, results in overflow error if a5 downwards empty. 3 macros being called in code below - believe - innocuous , have unprotecting worksheet before real code starts run , format range , re-protect worksheet once done. pretty decent excel formulas, coding weak, hoping answer simple flaw in code. many in advance.
private sub create_numberlist() application.screenupdating = false sheets("selections").select call unprotectselections dim lrow long dim x, mycount integer lrow = range("a4:b4").end(xldown).row each cell in range("a4:a" & lrow) cell.copy mycount = cell.offset(0, 8) x = x + 1 range("l" & x).pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _ :=false, transpose:=false mycount = mycount - 1 loop until mycount = 0 next cell application.cutcopymode = false call protectselections call reformatselections end sub
change: lrow = range("a4:b4").end(xldown).row
to: lrow = range("a" & rows.count).end(xlup).row
the reason if a5:b5 , on down empty, lrow becomes bottom in spreadsheet xldown function, , variables cannot hold memory row numbers (and code loops needlessly).
so, if code starts @ bottom , looks (with xlup) find last row of data check.
Comments
Post a Comment