excel vba - script out of range error help needed -
i have snip of code not populate worksheet names printing in pdf. subscript out of range compiling error. ideas?
application.screenupdating = false sheets("macros").select dim strpath string, strfilename string dim integer, y integer, x integer dim wkstnames() variant dim icount integer, iwkstcount integer dim printrange range set printrange = range("p222:p248") dim p() variant icount = 1 iwkstcount = thisworkbook.sheets.count redim wkstnames(1 iwkstcount) redim p(1 27) y = 1 iwkstcount x = 1 27 printrange.rows(p(x)).select if activecell = "true" wkstnames(y) = sheets(icount).name icount = icount + 1 next x next y sheets(wkstnames).select ' error occurs here
the reason pretty simple actually. redimming array in beginning , line wkstnames(y) = sheets(icount).name
not executing , hence getting @ least 1 blank value in array.
try , loop through array after created. use code
for = lbound(wkstnames) ubound(wkstnames) debug.print wkstnames(i) next worksheets(wkstnames).select
you notice @ least 1 value blank , hence excel cannot find sheet.
a working example like
dim wkstnames(1 2) string wkstnames(1) = "sheet1" wkstnames(2) = "sheet2" worksheets(wkstnames).select
example (followup comments)
this example show how can select sheet(s) without declaring array beforehand.
sub sample() dim ar dim wsname string, sdelim string sdelim = "|" thisworkbook = 1 .sheets.count if .sheets(i).name "sheet*" if wsname = "" wsname = .sheets(i).name else wsname = wsname & sdelim & .sheets(i).name end if end if next if instr(1, wsname, sdelim) ar = split(wsname, sdelim) else ar = wsname end if .sheets(ar).select end end sub
Comments
Post a Comment