Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

filling data in excel

Status
Not open for further replies.

dpaulo

Technical User
Jun 24, 2002
3
US
I have a problem similar to one already discused about filling data into cells, but couldn't make heads or tails of it.. I'm quite new at VB and programming generally.

I have a matrix of data in excel that's something like this:

103 2 3 34 20 SGH .5
103 2 3 34 GA .5
103 2 3 34 TO 3.5 2.4
103 2 3 34 9 GA .5
with many more records,
the first record has "20" in the 5th column. I want to make 19 more duplicates of that row and add it to the matrix. other records may have no values or like the last one, 9 (so I'd want to add 8 more records)

how do I go about doing this?

thank you!
 
Hi,

So you want to loop thru your matrix, row by row and if ther's a value in col 5, then copy/paste that row [col 5 value]-1 times?
Code:
set rng = activesheet.usedrange
with rng
  r1 = .row
  r2 = r1 + .rows.count - 1
end with
for each c in range(cells(r1, 5), cells(r2, 5))
  with c
    if .value > 0 then
      rows(.row & ":" & .row).copy
      set rng = activesheet.usedrange
      with rng
        r1 = .row
        r2 = r1 + .rows.count - 1
      end with
      range(cells(r2+1, 1), cells(r2+.value)).paste
    end if
  end with
next
end with
Something like this might work :)

Skip,
Skip@TheOfficeExperts.com
 
thanks skip,
I'm still working on the the VBA version for this.... I think I have to include selection somewhere.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top