i have little experience vba, , appreciate issue. in basic sense, need convert 2 columns of data in sheet 1 rows of data in sheet 2.
it looks in excel:
and need this:
i've written code transfer headings on sheet 2, , works fine. i'm having issues transferring actual values in correct format. right now, body of code
ws.range("b3").copy ws2.range("c2").pastespecial xlpastevalues ws.range("b4").copy ws2.range("d2").pastespecial xlpastevalues ws.range("b5").copy ws2.range("e2").pastespecial xlpastevalues ws.range("b6").copy ws2.range("f2").pastespecial xlpastevalues
continued on , on. however, won't work, actual document i'm working on has tens of thousands of data points. know there's way automate process, i've tried has either done nothing or given error 1004.
any appreciated!!
edit: there hundreds of little sections of data, each 18 rows long (1 row frame #, 1 row time, , 1 row each of 16 channels). i'm trying loop step size of 18. possible? i'm fine loops, i've never done loop copying , pasting cell values
this method leverages loops , arrays transfer data. isn't dynamic method gets job done. loops use existing constants, if data set changes can adjust constants , should run fine. make sure adjust worksheet names match names using in excel document. in effect, doing loading data array , transposing onto worksheet.
if data set sizes change quite bit, want include logic adjust loop variables , array size declarations. if case, let me know , i'll figure out how , post edit.
sub movetimedata() set source = thisworkbook.sheets("rawdata") set dest = thisworkbook.sheets("transposesheet") const datasetsize = 15 const row15start = 3 const row15end = 18 const row30start = 21 const row30end = 36 const colstart = 2 const destcolstart = 2 const dest15rowstart = 2 const dest30rowstart = 3 dim time15array() integer dim time30array() integer redim time15array(0 datasetsize) redim time30array(0 datasetsize) dim x integer dim y integer dim c integer c = 0 x = row15start row15end time15array(c) = source.cells(x, colstart).value c = c + 1 next x c = 0 x = row30start row30end time30array(c) = source.cells(x, colstart).value c = c + 1 next x x = 0 datasetsize dest.cells(dest15rowstart, x + destcolstart).value = time15array(x) next x x = 0 datasetsize dest.cells(dest30rowstart, x + destcolstart).value = time30array(x) next x end sub
edit-> think looking after reading edits
sub movetimedata() set source = thisworkbook.sheets("rawdata") set dest = thisworkbook.sheets("transposesheet") const numberdatagroups = 4 const datasetsize = 15 const stepsize = 18 const sourcerowstart = 3 const sourcecolstart = 2 const destcolstart = 2 const destrowstart = 2 dim x integer dim y integer dim currentrow integer currentrow = destrowstart x = 0 numberdatagroups y = 0 datasetsize dest.cells(currentrow, y + destcolstart).value = source.cells((x * stepsize) + (y + sourcerowstart), sourcecolstart) next y currentrow = currentrow + 1 next x end sub
now key working knowing how many groups of data dealing after data dump. either need include logic detecting or adjust constant called numberdatagroups reflect how many groups have. note: leveraged similar technique traversing arrays have data stored in row major format.
Comments
Post a Comment