Excel VBA - Nested loop to format excel table columns -

i have macro far, adds 4 new table columns existing table ("table1"). now, macro format 3rd , 4th row percentage. include in loop listed in code. have tried several different ways this. don't think quite understand how ubound function works, can understand trying do.

i unsure if allowed continue utilize statement in nested loop in regards me 'lst' variable.

@jeeped - i'm looking @ 1 again...thanks walking me through whole project lol

sub attstatpivinserttablecolumns_2()  dim lst listobject dim currentsht worksheet dim colnames variant, r1c1s variant  dim h integer, integer  set currentsht = activeworkbook.sheets("sheet1") set lst = activesheet.listobjects("table1")  colnames = array("aht", "target aht", "transfers", "target transfers") r1c1s = array("=([@[inbound talk time (seconds)]]+[@[inbound hold time (seconds)]]+[@[inbound wrap time (seconds)]])/[@[calls handled]]", "=350", "=[@[call transfers and/or conferences]]/[@[calls handled]]", "=0.15")  lst h = lbound(colnames) ubound(r1c1s)     .listcolumns.add     .listcolumns(.listcolumns.count).name = colnames(h)     .listcolumns(.listcolumns.count).databodyrange.formular1c1 = r1c1s(h)   if ubound(colnames(h)) = 2 or ubound(colnames(h)) = 3                 = ubound(colnames(h), 2) ubound(colnames(h), 3)             .listcolumns(.listcolumns.count).numberformat = "0%"   end if         next next h end  end sub 

you don't need nest second for loop. if want set 3rd , 4th columns percentage, need set when iteration of loop (h) 2 or 3 (remembering arrays index 0). shouldn't cross arrays main loop, , since lbound in cases 0 might use anyway. try this:

with lst     h = 0 ubound(r1c1s)         .listcolumns.add         .listcolumns(.listcolumns.count).name = colnames(h)         .listcolumns(.listcolumns.count).databodyrange.formular1c1 = r1c1s(h)         if h = 2 or h = 3                         .listcolumns(.listcolumns.count).numberformat = "0%"         end if     next h end 

to answer other point in question, ubound(array) gives index of largest element (the upper boundary) in given array. have 50 elements in such array, ubound(array) return 49 (zero based mentioned before). lbound gives other end of array (the lower boundary), zero.