I am very new to VB programing and already found myself in a problem.
want to split some rows with info according with some cells values o
col A and B. And When i split those rows i also want to change th
value of those cells. For example:

I want to transform this:

1 1 MP3_Music
2 3 Wave_Music
4 4 MP3_Music

Into:

1 1 MP3_Music
2 2 Wave_Music
3 3 Wave_Music
4 4 MP3_Music

I though in using a ABS to see if i have a difference in a row. If tha
was true then the macro would add a new line. My problem is making th
macro search for for every difference for 200 or 300 lines and chang
them.

Is probably a dumb question but as i said before i am very new t
this..

TIA

Vito

--
Message posted from http://www.ExcelForum.com

I think that this does it, but try it against a copy of the worksheet--just in
case:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim numberOfRows As Long
Dim colAValue As Long
Dim colBValue As Long

Set wks = ActiveSheet
With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
colAValue = .Cells(iRow, "A").Value
colBValue = .Cells(iRow, "B").Value
numberOfRows = colBValue - colAValue
If numberOfRows = 0 Then
'do nothing
Else
.Rows(iRow + 1).Resize(numberOfRows).Insert
With .Cells(iRow, "A").Resize(numberOfRows + 1, 2)
.FormulaR1C1 = "=r[-1]c2 + 1"
.Value = .Value
End With
.Cells(iRow + 1, "C").Resize(numberOfRows, 1).Value _
= .Cells(iRow, "C").Value
End If
Next iRow
End With
End Sub

I started with this:
1 1 MP3_Music
2 18 Wave_Music
19 23 MP3_Music

and got this:

1 1 MP3_Music
2 2 Wave_Music
3 3 Wave_Music
4 4 Wave_Music
5 5 Wave_Music
6 6 Wave_Music
7 7 Wave_Music
8 8 Wave_Music
9 9 Wave_Music
10 10 Wave_Music
11 11 Wave_Music
12 12 Wave_Music
13 13 Wave_Music
14 14 Wave_Music
15 15 Wave_Music
16 16 Wave_Music
17 17 Wave_Music
18 18 Wave_Music
19 19 MP3_Music
20 20 MP3_Music
21 21 MP3_Music
22 22 MP3_Music
23 23 MP3_Music


"sousas <" wrote:

I am very new to VB programing and already found myself in a problem. I
want to split some rows with info according with some cells values of
col A and B. And When i split those rows i also want to change the
value of those cells. For example:

I want to transform this:

1 1 MP3_Music
2 3 Wave_Music
4 4 MP3_Music

Into:

1 1 MP3_Music
2 2 Wave_Music
3 3 Wave_Music
4 4 MP3_Music

I though in using a ABS to see if i have a difference in a row. If that
was true then the macro would add a new line. My problem is making the
macro search for for every difference for 200 or 300 lines and change
them.

Is probably a dumb question but as i said before i am very new to
this..

TIA

Vitor

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson