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

Popular posts from this blog

apache - PHP Soap issue while content length is larger -

asynchronous - Python asyncio task got bad yield -

javascript - Complete OpenIDConnect auth when requesting via Ajax -