Excel 2010 VB help 2

[tonyvee1973]

Hi All

Having a bit of a nightmare with something really simple.
I have a spreadsheet with 27000 lines of data in.

I simply want to insert 10 black rows after a row if column e in that row contains 39013

I have tried below but it only inserts 1 line.. Help

Sub Macro()
nr = Cells(Rows.Count, 5).End(xlUp).Row
For r = nr To 5 Step -1
If Cells(r, 5).Text = "39013" Then
Rows(r + 1).Insert Shift:=xlDown
End If
Next
End Sub

 

[SkipVought]

Hi,

If Cells(r, 5).Text = "39013" Then

Are you SURE that you have the CHARACTERS 39013 rather than the NUMBER 39013.

BIG difference!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

Hi Skip
The cells with the numbers in are formatted as general - they are just numbers. Have i got it wrong?

 

[SkipVought]

The format does not matter! You can have TEXT in a cell formatted as a NUMBER or a NUMER in a cell formatted as TEXT. The formatting is irrelevant to the underlying value in the cell or range.

If you, in reality, have a NUMBER then

If Cells(r, 5).Text = "39013" Then

will FAIL, as you are equating a NUMERIC value to a TEXT value!

FORMATTING alone, changes NOTHING!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

ok, understood.
WHen i run the macro below it does insert 1 line after the text '39013' but only 1 line. i have tried playing with it but cant get it to add 10 lines

Sub Macro()
nr = Cells(Rows.Count, 5).End(xlUp).Row
For r = nr To 5 Step -1
If Cells(r, 5).Text = "39013" Then
Rows(r + 1).Insert Shift:=xlDown
End If
Next
End Sub

 

[Loomah]

hi
try rows(r+1 &":"& r+11).insert shift:=xldown

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[tonyvee1973]

Just the ticket! Thanks as always skip - huge Kudos your way!
Tone

 

[tonyvee1973]

Ooops - Sorry Loomah.
Appreciate the help and Kudos moves to you!

 

[tonyvee1973]

Just have another issue here
If i wanted to insert more rows under a different number in this same macro any ideas how i would do?
I want to add 24 blank rows under the row with 36043 in, and 10 empty lines under the row with 39013 in.
Below is my attempt - dont laugh! i try to run it but it errors out - ANy ideas?


Sub Macro()
nr = Cells(Rows.Count, 5).End(xlUp).Row
For r = nr To 5 Step -1
If Cells(r, 5).Text = "36043" Then
Rows(r + 1 & ":" & r + 24).Insert shift:=xlDown
If Cells(r, 5).Text = "39013" Then
Rows(r + 1 & ":" & r + 10).Insert shift:=xlDown
End If
Next
End Sub

 

[SkipVought]

Sub Macro()
    Dim nr As Integer, r As Long
    
    nr = Cells(Rows.Count, 5).End(xlUp).Row
    
    For r = nr To 5 Step -1
        Select Case Cells(r, 5).Text
            Case "36043"
                Rows(r + 1 & ":" & r + 24).Insert shift:=xlDown
            Case "39013"
                Rows(r + 1 & ":" & r + 10).Insert shift:=xlDown
        End Select
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

Skip, you are the man!
Is your grey matter for sale?
Thanks as always, tone

 

[SkipVought]

I often clean up my code after some thot...

Sub Macro()
    Dim nr As Integer, r As Long, i As Integer
    
    nr = Cells(Rows.Count, 5).End(xlUp).Row
    
    For r = nr To 5 Step -1
        Select Case Cells(r, 5).Text
            Case "36043": i = 24
            Case "39013": i = 10
        End Select
        
        Rows(r + 1 & ":" & r + i).Insert shift:=xlDown
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

Hiya

Just applied the code above to add blank rows into my evergrowing sheet - when i try to run it i get "Run-time error '6': overflow.
Any ideas on why this is and how i can fix?
Thanks

 

[SkipVought]

I answered in your most recent thread.

AVOID the Insert method!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

Hi Skip
How would i achieve what i need avoiding the insert method?
Sorry but i really am lost here :S

 

[SkipVought]

Your current approch is to FIND the place that you want to insert, using some logic. THAT logic most often implies a SORT ORDER.

Therefore, it makes much more sense, and from a database perspecitve, it is understood, that you add a new row AT THE BOTTOM of your table, and SORT it into the desired position if required, rather than using a mickey mouse insert process.

Out of curiosity, what is the significance of, "column e in that row contains 39013?"

What is the data in column e?

How is your table structured?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

Hi
Ok, i understand.
column e contains stock codes.
this whole sheet is for keeping an eye on stock levels for 80+ items across 600+ shops and times to replenish stock.
i'm despeately trying to get an Access DB together but dont have the time to sit down and get my head around it - Catch 22!

 

[SkipVought]

So you add your new rows of data at the bottom of your table, very easy to find. and SORT into order.

BEST & ACCEPTED Practice!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}