Tricky Tricky!

here what i got! :

A B C
1
2 1
3
4
5
6
7 2
8
9
10

I need a macro that will transform it to look like this! :

A B C
1
1440
2 1
3
4
5
6
1440
1440
7 2
8
9
10

So the macro will search in column C, and if there is a value >0 it will insert new lines corresponding on the number he found in a C cell (if C7=2, will insert two rows upward). After that i need the macro to put the values 1440 in B cell of the new row inserted!

thanks!

or look at my file!

Hi mhax,

Something like this should work:

Sub test()
Dim lLastRow As Long
Dim lCurrRow As Long
Dim vValue As Variant

Application.ScreenUpdating = False

With Worksheets("sheet1")
lLastRow = .Cells(.Rows.Count, 1).End(xlUp)

For lCurrRow = lLastRow To 1 Step -1
vValue = .Cells(lCurrRow, 3).Value
If Len(vValue) > 0 And IsNumeric(vValue) Then
.Cells(lCurrRow, 1).Resize(vValue, _
1).EntireRow.Insert shift:=xlUp
.Cells(lCurrRow, 2).Resize(vValue, _
1).Value = 1440
End If
Next lCurrRow
End With

Application.ScreenUpdating = True
End Sub

There are more efficient ways of doing it if this is going to be recurring
task and you have thousands or tens of thousands of rows. But this should
be fairly quick.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

mhax wrote:
> here what i got! :
>
> A B C
> 1
> 2 1
> 3
> 4
> 5
> 6
> 7 2
> 8
> 9
> 10
>
> I need a macro that will transform it to look like this! :
>
> A B C
> 1
> 1440
> 2 1
> 3
> 4
> 5
> 6
> 1440
> 1440
> 7 2
> 8
> 9
> 10
>
> So the macro will search in column C, and if there is a value >0 it
> will insert new lines corresponding on the number he found in a C cell
> (if C7=2, will insert two rows upward). After that i need the macro to
> put the values 1440 in B cell of the new row inserted!
>
> thanks!

OMG!!!!!!!! THANKS YOU!!!!!!!! I will be able to finish my work tomorrow finaly :D! You're doing a good job man! Have a good day!

Mhax

Originally Posted by Jake Marx

Hi mhax,

Something like this should work:

Sub test()
Dim lLastRow As Long
Dim lCurrRow As Long
Dim vValue As Variant

Application.ScreenUpdating = False

With Worksheets("sheet1")
lLastRow = .Cells(.Rows.Count, 1).End(xlUp)

For lCurrRow = lLastRow To 1 Step -1
vValue = .Cells(lCurrRow, 3).Value
If Len(vValue) > 0 And IsNumeric(vValue) Then
.Cells(lCurrRow, 1).Resize(vValue, _
1).EntireRow.Insert shift:=xlUp
.Cells(lCurrRow, 2).Resize(vValue, _
1).Value = 1440
End If
Next lCurrRow
End With

Application.ScreenUpdating = True
End Sub

There are more efficient ways of doing it if this is going to be recurring
task and you have thousands or tens of thousands of rows. But this should
be fairly quick.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

mhax wrote:
> here what i got! :
>
> A B C
> 1
> 2 1
> 3
> 4
> 5
> 6
> 7 2
> 8
> 9
> 10
>
> I need a macro that will transform it to look like this! :
>
> A B C
> 1
> 1440
> 2 1
> 3
> 4
> 5
> 6
> 1440
> 1440
> 7 2
> 8
> 9
> 10
>
> So the macro will search in column C, and if there is a value >0 it
> will insert new lines corresponding on the number he found in a C cell
> (if C7=2, will insert two rows upward). After that i need the macro to
> put the values 1440 in B cell of the new row inserted!
>
> thanks!

mhax wrote:
> OMG!!!!!!!! THANKS YOU!!!!!!!! I will be able to finish my work
> tomorrow finaly :D! You're doing a good job man! Have a good day!

No problem - glad to help!

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]