• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Insterting Lines with Elseif

camdevl09

New Member
Is there a way to insert different amounts of lines depending on a cell's value? For example, in column A, have the following values (1, 2, 3, 4). Is there a way I can say "if the value in column A is 1 then insert 1 line below, or else if the value is 2, insert 2 lines below, or else if the value is 3 insert 3 lines below and so on... I would like to be able to run the macro and it go down the column and do this action for each cell that has a value.


Thanks in advance....
 
Here's an idea:

[pre]
Code:
Sub InsertRows()
Dim i As Integer
Dim StartRow As Integer
Dim EndRow As Integer
Dim ColOfInterest As Integer

'Either set these manually, of have the macro figure it
'out somehow
StartRow = 2
EndRow = 10
ColOfInterest = 1

Application.ScreenUpdating = False
For i = EndRow To StartRow Step -1
With Cells(i, ColOfInterest)
RowCount = .Value
If RowCount <> 0 Then
Cells(i + 1, ColOfInterest).EntireRow.Resize(RowCount).Insert
End If
End With
Next i
Application.ScreenUpdating = True
End Sub
[/pre]
 
Hi Camdevl09,


This should do it for you:


Sub MkLines()

'assumption that you have numbers in your col A

'macro adds "X" nos of new lines specified in col A.


Dim iLstRw As Integer

Dim iNewRw As Integer


iLstRw = Range("A65000").End(xlUp).Row


Do While iLstRw > 1


For iNewRw = 1 To Range("A" & iLstRw - 1).Value

Range("A" & iLstRw).Select

Selection.EntireRow.Insert

Next

iLstRw = iLstRw - 1

Loop


End Sub
 
Ok, I guess using numbers was a bad idea. What is actually going to be in the columns I am basing the number of rows needed to be inserted is text. So, if in column A it says "Net 30" I want it to insert 1 row, if it says "80% Delivery, 20% First Use", I want it to insert 2 columns. Any way of doing that? Thanks!
 
You need to think more like a computer...how is the macro supposed to know that "Net 30" means 1 row, while "80% deliver, 20% first use" means 2 columns? Also, do you really want 2 whole columns, or just 2 cells to the right, since a whole column would affect all the other rows.


Now, if it's some small list of possible choices, might be able to build a Select Case type macro, where the macro will do some particular action based on a value (works similar to a lookup table). But we'd need to know all the choices.


Pseudo code:

Select Case RowCount

Case "Net 30"

'insert 1 row

Case "80% Delivery, 20% First"

'inset 2 cells to right

Case "Something else"

'inset 1 row, 1 cell to right

End Select
 
I Agree with Luke. May be if you put your requirements fully, we can also suggest alternates solutions as well. In case, this is the only requirement you have then use the below code:


Sub MkLines()


Dim iLstRw As Integer


iLstRw = Range("A65000").End(xlUp).Row + 1


Do While iLstRw > 1


'since the action is based out of text, I am using partial match of text with instring function.

If VBA.InStr(1, Range("A" & iLstRw - 1).Value, "Net 30") Then

Range("A" & iLstRw).Select

Selection.EntireRow.Insert


ElseIf VBA.InStr(1, Range("A" & iLstRw - 1).Value, "80% Delivery") Then


'if you want to add 2 columns to RIGHT of Col B use below 3 lines of code

' if you dont want 2 columns then comment below 3 lines.

Range("B" & iLstRw).Select

Selection.EntireColumn.Insert

Selection.EntireColumn.Insert


'if you wanted to add 2 ROWS and not col then use below 2 lines of code

' if you dont want 2 ROWS then comment below 2 lines.

Range("A" & iLstRw).Select

Selection.EntireRow.Resize(2).Insert


End If


iLstRw = iLstRw - 1

Loop


End Sub
 
Back
Top