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!

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!