To difficult for me

[Theo]

I've got two problems. The first is that I'm dutch so my English is not very good, but you can't do a thing about that. The second is the following:

I've got a spreadsheet which looks like this:
A B C D E
1 0 val2 val3 val4 val5
2 1 val2 val3 val4 val5
3 0 val2 val3 val4 val5
4 1 val2 val3 val4 val5
5 2 val2 val3 val4 val5
...


I'm searching for a vba-code and/or a macro which does the following:
when cell A1 >0 then add a line directly under it and copy val5 of the previous line (cell E1) in cel B2.
Then the value in cell A2 must be cell A1+1 and so on except when the next value is equal to 0.

It's a complicated problem (for me) but the answer is simple (i guess??). This is useally the case.

Theo

[smozgur]

In your sample data there is no row which A colum data is 0. i will ask you what i could understand : If data like below ;

A B C D E F
1 0 val2 val3 val4 val51
0 1 val2 val3 val4 val52
3 0 val2 val3 val4 val53
0 1 val2 val3 val4 val54
5 2 val2 val3 val4 val55

Then modified data would be like this;

A B C D E F
1 0 val2 val3 val4 val51
0 1 val2 val3 val4 val52
1 val52
3 0 val2 val3 val4 val53
0 1 val2 val3 val4 val54
1 val54
5 2 val2 val3 val4 val55

Can you explain by giving sample data table before and after modification. It would be easy to understand for me then.

[Theo]

On 2002-03-18 04:15, smozgur wrote:
In your sample data there is no row which A colum data is 0. i will ask you what i could understand : If data like below ;

A B C D E F
1 0 val2 val3 val4 val51
0 1 val2 val3 val4 val52
3 0 val2 val3 val4 val53
0 1 val2 val3 val4 val54
5 2 val2 val3 val4 val55

Then modified data would be like this;

A B C D E F
1 0 val2 val3 val4 val51
0 1 val2 val3 val4 val52
1 val52
3 0 val2 val3 val4 val53
0 1 val2 val3 val4 val54
1 val54
5 2 val2 val3 val4 val55

Can you explain by giving sample data table before and after modification. It would be easy to understand for me then.

Thats exactly what I mean.

[smozgur]

This is the code what does that:

Private Sub JustDoIt()
Dim i As Integer
i = 1
With Sayfa1
Do Until .Cells(i, 1) = ""
If .Cells(i, 1) = 0 Then
.Rows(i + 1).Select
Selection.Insert Shift:=xlDown
.Cells(i + 1, 2) = .Cells(i, 5)
.Cells(i + 1, 1) = .Cells(i, 1) + 1
End If
i = i + 1
Loop
End With
End Sub


But it is interesting, because i created this for A1=0 criteria and i think i missed your question's that part. Are you sure this is the code you want?

suat

[Theo]

The code you send me first does exactly what I want. I altered a little bit for my specific needs.

Thanks!!

[smozgur]

It's my pleasure.
Anytime you need.