yes, have written macro microsoft excel (2010 if helps), assistance of else. wondering if has way of shortening it, , making more efficient. still getting same outcome gave before? example of csv format have work can found here... , yes, unfortunately, have put columns , cells..
the issue stumped on this: on .cell(2, 3) example... if notice, on each section copy , paste, has new row.. want that.. novice @ this, , not find way make paste each 1 next available row.. solution 2, 3, 4, 5.. , on.. if knows how change make this.. loop? per say, great help. loop amount of data being copied though, , not repeat.
here example csv: media fire it's clean, promise. thank time.
macro code copy column/row data cells 1 sheet specific cells
sub formatdata() dim col integer col = 1 1 worksheets(2) .cells(2, 2) = cells(1, col) .cells(2, 3) = cells(2, col) & ". " & cells(3, col) & ". " & cells(4, col) & ". " & cells(5, col) & "." .cells(2, 4) = cells(7, col) .cells(2, 5) = cells(10, col) end next col col = 2 2 worksheets(2) .cells(3, 2) = cells(1, col) .cells(3, 3) = cells(2, col) & ". " & cells(3, col) & ". " & cells(4, col) & ". " & cells(5, col) & "." .cells(3, 4) = cells(7, col) .cells(3, 5) = cells(10, col) end next col col = 3 3 worksheets(2) .cells(4, 2) = cells(1, col) .cells(4, 3) = cells(2, col) & ". " & cells(3, col) & ". " & cells(4, col) & ". " & cells(5, col) & "." .cells(4, 4) = cells(7, col) .cells(4, 5) = cells(10, col) end next col col = 4 4 worksheets(2) .cells(5, 2) = cells(1, col) .cells(5, 3) = cells(2, col) & ". " & cells(3, col) & ". " & cells(4, col) & ". " & cells(5, col) & "." .cells(5, 4) = cells(7, col) .cells(5, 5) = cells(10, col) end next col col = 1 1 worksheets(2) .cells(6, 2) = cells(13, col) .cells(6, 3) = cells(14, col) & ". " & cells(15, col) & ". " & cells(16, col) & ". " & cells(17, col) & "." .cells(6, 4) = cells(19, col) .cells(6, 5) = cells(22, col) end next col col = 2 2 worksheets(2) .cells(7, 2) = cells(13, col) .cells(7, 3) = cells(14, col) & ". " & cells(15, col) & ". " & cells(16, col) & ". " & cells(17, col) & "." .cells(7, 4) = cells(19, col) .cells(7, 5) = cells(22, col) end next col col = 3 3 worksheets(2) .cells(8, 2) = cells(13, col) .cells(8, 3) = cells(14, col) & ". " & cells(15, col) & ". " & cells(16, col) & ". " & cells(17, col) & "." .cells(8, 4) = cells(19, col) .cells(8, 5) = cells(22, col) end next col col = 4 4 worksheets(2) .cells(9, 2) = cells(13, col) .cells(9, 3) = cells(14, col) & ". " & cells(15, col) & ". " & cells(16, col) & ". " & cells(17, col) & "." .cells(9, 4) = cells(19, col) .cells(9, 5) = cells(22, col) end next col col = 1 1 worksheets(2) .cells(10, 2) = cells(25, col) .cells(10, 3) = cells(26, col) & ". " & cells(27, col) & ". " & cells(28, col) & ". " & cells(29, col) & "." .cells(10, 4) = cells(31, col) .cells(10, 5) = cells(34, col) end next col col = 2 2 worksheets(2) .cells(11, 2) = cells(25, col) .cells(11, 3) = cells(26, col) & ". " & cells(27, col) & ". " & cells(28, col) & ". " & cells(29, col) & "." .cells(11, 4) = cells(31, col) .cells(11, 5) = cells(34, col) end next col col = 3 3 worksheets(2) .cells(12, 2) = cells(25, col) .cells(12, 3) = cells(26, col) & ". " & cells(27, col) & ". " & cells(28, col) & ". " & cells(29, col) & "." .cells(12, 4) = cells(31, col) .cells(12, 5) = cells(34, col) end next col col = 4 4 worksheets(2) .cells(13, 2) = cells(25, col) .cells(13, 3) = cells(26, col) & ". " & cells(27, col) & ". " & cells(28, col) & ". " & cells(29, col) & "." .cells(13, 4) = cells(31, col) .cells(13, 5) = cells(34, col) end next col col = 1 1 worksheets(2) .cells(14, 2) = cells(37, col) .cells(14, 3) = cells(38, col) & ". " & cells(39, col) & ". " & cells(40, col) & ". " & cells(41, col) & "." .cells(14, 4) = cells(43, col) .cells(14, 5) = cells(46, col) end next col col = 2 2 worksheets(2) .cells(15, 2) = cells(37, col) .cells(15, 3) = cells(38, col) & ". " & cells(39, col) & ". " & cells(40, col) & ". " & cells(41, col) & "." .cells(15, 4) = cells(43, col) .cells(15, 5) = cells(46, col) end next col col = 3 3 worksheets(2) .cells(16, 2) = cells(37, col) .cells(16, 3) = cells(38, col) & ". " & cells(39, col) & ". " & cells(40, col) & ". " & cells(41, col) & "." .cells(16, 4) = cells(43, col) .cells(16, 5) = cells(46, col) end next col col = 4 4 worksheets(2) .cells(17, 2) = cells(37, col) .cells(17, 3) = cells(38, col) & ". " & cells(39, col) & ". " & cells(40, col) & ". " & cells(41, col) & "." .cells(17, 4) = cells(43, col) .cells(17, 5) = cells(46, col) end next col col = 1 1 worksheets(2) .cells(18, 2) = cells(49, col) .cells(18, 3) = cells(50, col) & ". " & cells(51, col) & ". " & cells(52, col) & ". " & cells(53, col) & "." .cells(18, 4) = cells(55, col) .cells(18, 5) = cells(58, col) end next col col = 2 2 worksheets(2) .cells(19, 2) = cells(49, col) .cells(19, 3) = cells(50, col) & ". " & cells(51, col) & ". " & cells(52, col) & ". " & cells(53, col) & "." .cells(19, 4) = cells(55, col) .cells(19, 5) = cells(58, col) end next col col = 3 3 worksheets(2) .cells(20, 2) = cells(49, col) .cells(20, 3) = cells(50, col) & ". " & cells(51, col) & ". " & cells(52, col) & ". " & cells(53, col) & "." .cells(20, 4) = cells(55, col) .cells(20, 5) = cells(58, col) end next col col = 4 4 worksheets(2) .cells(21, 2) = cells(49, col) .cells(21, 3) = cells(50, col) & ". " & cells(51, col) & ". " & cells(52, col) & ". " & cells(53, col) & "." .cells(21, 4) = cells(55, col) .cells(21, 5) = cells(58, col) end next col col = 1 1 worksheets(2) .cells(22, 2) = cells(61, col) .cells(22, 3) = cells(62, col) & ". " & cells(63, col) & ". " & cells(64, col) & ". " & cells(65, col) & "." .cells(22, 4) = cells(67, col) .cells(22, 5) = cells(70, col) end next col col = 2 2 worksheets(2) .cells(23, 2) = cells(61, col) .cells(23, 3) = cells(62, col) & ". " & cells(63, col) & ". " & cells(64, col) & ". " & cells(65, col) & "." .cells(23, 4) = cells(67, col) .cells(23, 5) = cells(70, col) end next col col = 3 3 worksheets(2) .cells(24, 2) = cells(61, col) .cells(24, 3) = cells(62, col) & ". " & cells(63, col) & ". " & cells(64, col) & ". " & cells(65, col) & "." .cells(24, 4) = cells(67, col) .cells(24, 5) = cells(70, col) end next col col = 4 4 worksheets(2) .cells(25, 2) = cells(61, col) .cells(25, 3) = cells(62, col) & ". " & cells(63, col) & ". " & cells(64, col) & ". " & cells(65, col) & "." .cells(25, 4) = cells(67, col) .cells(25, 5) = cells(70, col) end next col col = 1 1 worksheets(2) .cells(26, 2) = cells(73, col) .cells(26, 3) = cells(74, col) & ". " & cells(75, col) & ". " & cells(76, col) & ". " & cells(77, col) & "." .cells(26, 4) = cells(79, col) .cells(26, 5) = cells(82, col) end next col col = 2 2 worksheets(2) .cells(27, 2) = cells(73, col) .cells(27, 3) = cells(74, col) & ". " & cells(75, col) & ". " & cells(76, col) & ". " & cells(77, col) & "." .cells(27, 4) = cells(79, col) .cells(27, 5) = cells(82, col) end next col col = 3 3 worksheets(2) .cells(28, 2) = cells(73, col) .cells(28, 3) = cells(74, col) & ". " & cells(75, col) & ". " & cells(76, col) & ". " & cells(77, col) & "." .cells(28, 4) = cells(79, col) .cells(28, 5) = cells(82, col) end next col col = 4 4 worksheets(2) .cells(29, 2) = cells(73, col) .cells(29, 3) = cells(74, col) & ". " & cells(75, col) & ". " & cells(76, col) & ". " & cells(77, col) & "." .cells(29, 4) = cells(79, col) .cells(29, 5) = cells(82, col) end next col col = 1 1 worksheets(2) .cells(30, 2) = cells(85, col) .cells(30, 3) = cells(86, col) & ". " & cells(87, col) & ". " & cells(88, col) & ". " & cells(89, col) & "." .cells(30, 4) = cells(91, col) .cells(30, 5) = cells(94, col) end next col col = 2 2 worksheets(2) .cells(31, 2) = cells(85, col) .cells(31, 3) = cells(86, col) & ". " & cells(87, col) & ". " & cells(88, col) & ". " & cells(89, col) & "." .cells(31, 4) = cells(91, col) .cells(31, 5) = cells(94, col) end next col col = 3 3 worksheets(2) .cells(32, 2) = cells(85, col) .cells(32, 3) = cells(86, col) & ". " & cells(87, col) & ". " & cells(88, col) & ". " & cells(89, col) & "." .cells(32, 4) = cells(91, col) .cells(32, 5) = cells(94, col) end next col col = 4 4 worksheets(2) .cells(33, 2) = cells(85, col) .cells(33, 3) = cells(86, col) & ". " & cells(87, col) & ". " & cells(88, col) & ". " & cells(89, col) & "." .cells(33, 4) = cells(91, col) .cells(33, 5) = cells(94, col) end next col end sub
here's refactored sub
points note:
use variant arrays looping/data processing because looping through cells slow
you can change value of
srcblockscontrol how many blocks process, or extract source datarefering destination sheet index can problematic if moved. safer refer name
worksheets("sheetname")
.
sub formatdata() dim rw2 integer, rwa integer, cola integer dim vdst() variant, vsrc variant dim srcblocks integer srcblocks = 8 ' process 8 blocks of 12 rows vsrc = activesheet.range("a1:d" & srcblocks * 12) redim vdst(1 srcblocks * 4 + 1, 1 5) rwa = 0 srcblocks * 12 - 1 step 12 ' = 0, 12, 24, ... cola = 1 4 ' 4 columns in src rw2 = (rwa \ 12) * 4 + cola + 1 ' 4 dst rws per block, = 2..5, 6..9, ... vdst(rw2, 2) = vsrc(rwa + 1, cola) vdst(rw2, 3) = vsrc(rwa + 2, cola) & ". " & _ vsrc(rwa + 3, cola) & ". " & _ vsrc(rwa + 4, cola) & ". " & _ vsrc(rwa + 5, cola) & "." vdst(rw2, 4) = vsrc(rwa + 7, cola) vdst(rw2, 5) = vsrc(rwa + 10, cola) next cola next rwa worksheets(2).range("a1:e" & cstr(srcblocks * 4 + 1)) = vdst end sub to add flexability, variable number of columns in source data
sub formatdata() dim rw2 integer, rwa integer, cola integer dim vdst() variant, vsrc variant dim srcblocks integer, srccolumns integer srcblocks = 8 ' process 8 blocks of 12 rows ' srccolumns = 5 ' columns in source data ' vsrc = activesheet.range( _ activesheet.cells(1, 1), _ activesheet.cells(srcblocks * 12, srccolumns)) redim vdst(1 srcblocks * srccolumns + 1, 1 5) rwa = 0 srcblocks * 12 - 1 step 12 ' = 0, 12, 24, ... ' cola = 1 srccolumns ' srccolumns columns in source ' rw2 = (rwa \ 12) * srccolumns + cola + 1 ' srccolumns rows in destination per source block ' vdst(rw2, 2) = vsrc(rwa + 1, cola) vdst(rw2, 3) = vsrc(rwa + 2, cola) & ". " & _ vsrc(rwa + 3, cola) & ". " & _ vsrc(rwa + 4, cola) & ". " & _ vsrc(rwa + 5, cola) & "." vdst(rw2, 4) = vsrc(rwa + 7, cola) vdst(rw2, 5) = vsrc(rwa + 10, cola) next cola next rwa worksheets(2).range("a1:e" & cstr(srcblocks * 4 + 1)) = vdst end sub 
Comments
Post a Comment