Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert number of rows based on cell value, and fill
I have an Excel database of animal behaviors that shows a count of the
times a given behavior has been observed for a given date . I need to expand that out into separate entries for each observation; e.g., date column: 2/14/08; behavior column: feeding; count column (#observations): 50 for these 50 observations of feeding on 2/14/04, i need 50 rows that say feeding, instead of 1 row with a count of 50. I need all the info copied into the new rows. This is similar to the previous question at http://www.experts-exchange.com/Soft..._23031740.html. However, I tried to copy the code and adapt it to my file and it didn't run. Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert number of rows based on cell value, and fill
Hi Tara,
You can use ADO with XML to perform such task. Sample Code: Dim rst as adodb.recordset Dim Filestream as adodb.stream set rst= New Adodb.recordset 'append fields rst.append '<field Name',datatype : : rst.update rst.open ,adodynamic,adlockoptimistic 'Now loop the values Dim Data as varient Dim counter as long Data= worksheet.range("<Your Range Value") for i=1 to 65536 for counter=1 to Data rst.addnew rst.field("value")= <Your value next next rst.update rst.save filestrem,adxml filestream.saveto <path,adcreateoverwrite filestream.close rs.close set rs=nothing 'NOW OPEN SAVED XML FILE set rst= new adodb.recordset rst.open path,"Provider=MSPersist" Range("<your worksheet target").copyrecordset rst rst.close set rst=nothing This will solve your purpose " wrote: I have an Excel database of animal behaviors that shows a count of the times a given behavior has been observed for a given date . I need to expand that out into separate entries for each observation; e.g., date column: 2/14/08; behavior column: feeding; count column (#observations): 50 for these 50 observations of feeding on 2/14/04, i need 50 rows that say feeding, instead of 1 row with a count of 50. I need all the info copied into the new rows. This is similar to the previous question at http://www.experts-exchange.com/Soft..._23031740.html. However, I tried to copy the code and adapt it to my file and it didn't run. Any help would be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto fill rows with months based on cell value | Excel Discussion (Misc queries) | |||
Insert a number rows according to a cell value | Excel Programming | |||
Insert a number of rows based on a value in a cell on active row | Excel Programming | |||
Insert a number of rows based on a value in a cell on active row | Excel Discussion (Misc queries) | |||
Insert a number of rows based on a value in a cell on active row | Excel Worksheet Functions |