Insert number of rows based on cell value, and fill

[Tara.Whitty]

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/Sof...Office_Suites/MS_Office/Excel/Q_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!

 

[Max]

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