Excel 2007 VBA Assigning part of a named range to an array -

i have table of monthly sales figures - freqdata1. each column represents month , numbered 1 12. user chooses 1 of these numbers dropdown list.

i have code find column number , have tried assign data in column array can use copy different spreadsheet basic vba knowledge , despite lots of searching have been unable find code how or different method carry out.

can please

sub annualfreqmacro()     dim tpnoint long, branchnoint long, colno long    dim freqarray()     worksheets("freq data").activate    tpnoint = range("b42").value    branchnoint = range("b41").value     colno = application.match(tpnoint, range("tpbr1"), 0)     charaarray = range("freqdata1").cells (1, colno), cells(16, colno))     end sub 

many in advance

i think answer: it's how you're using range.

delete chararray = ... line , replace with:

with range("freqdata1")    charaarray = .range(.cells(1, colno), .cells(16, colno))  end 

the issue how you're setting range, range().cells(), cells() isn't context, you'd want more range(cells(),cells()).

let's "freqdata1" range a10:a20. if use

with range("freqdata1")     .cells(1,1).select  end 

this select top left cell (row 1, column 1) in range "freqdata", cell a10 selected.

a final misc. point: avoid using .select/.activate. can activate sheet of course can follow macro, when setting variables ranges/cell values, etc. it's best qualify sheet referring to.

sub annualfreqmacro() dim tpnoint long, branchnoint long, colno long dim freqarray() dim freqws  worksheet set freqws = worksheets("freq data") ' worksheets("freq data").activate ' removed this, since have variable now.  tpnoint = freqws.range("b42").value    ' see how added worksheet want b42's value from? branchnoint = freqws.range("b41").value colno = application.match(tpnoint, range("tpbr1"), 0) freqws.range("freqdata1")    ' i'm assuming "freqdata1" on worksheet     charaarray = .range(.cells(1, colno), .cells(16, colno)) end end sub 

i'm not positive if have qualify named range's sheet, since it's named range, added safe.

edit2: hm, oddly enough, if named range "myrange" a1:a10, can still myrange.range(myrange.cells(1,1),myrange.cells(1,2)), though there's no second column in range, expands it. thought it'd throw error, nope. note.