CODEWITHSUNDEEP
excelvba
Belle
Belle

Reputation: 5

How to add rows based on a condition in VBA?

I have a spreadsheet that looks like this:

(C5): Top
(C6):
(C7):
(C8):
(C9):
(C10):
(C11):
(C12):
(C13):
(C14): Bottom

There are 2 user defined variables, apple and orange. Say that apple=3 and orange=3. The output should look like this:

(C5): Top
(C6): Apple 1
(C7): Apple 2
(C8): Apple 3
(C9): Orange 1
(C10): Orange 2
(C11): Orange 3
(C12): Hello world 1
(C13): Hello world 2
(C14): Bottom

If apple = 3 and orange = 4, I want the output to look like this:

(C5): Top
(C6): Apple 1
(C7): Apple 2
(C8): Apple 3
(C9): Orange 1
(C10): Orange 2
(C11): Orange 3
(C12): Orange 4
(C13): Hello world 1
(C14): Hello world 2
(C15): Bottom

If apple+orange >6, then I want to insert empty rows above C14. How do I do this? This is my code so far, however when I ran it, it printed "Apple 1" to "Hello World 2" to cells C6:C17, and pushed "Bottom" down to C26.

Private Sub CommandButton1_Click()

Dim apple As Integer, orange As Integer
Dim i As Long, j As Long, k As Long, l As Long, lRow As Long, sentence1 As Long, sentence2 As Long, addRows As Long

lRow = Cells(6, 3).End(xlUp).Row + 1
apple = InputBox("Please enter number of apples")
orange = InputBox("Please enter number of oranges")
sentence1 = 1
sentence2 = 1

fruit = apple + orange
addRows = fruit - 8
If fruit > 8 Then
    Rows("13:" & addRows).Insert Shift:=xlDown, _
        CopyOrigin:=xlFormatFromLeftOrAbove
End If

    For i = 1 To apple
        Cells(lRow, 3) = "Apple " & i
        lRow = lRow + 1
    Next i

    For j = 1 To orange
        Cells(lRow, 3) = "Orange " & j
        lRow = lRow + 1
    Next j
    
    For k = 1 To sentence1
        Cells(lRow, 3) = "Hello world 1"
        lRow = lRow + 1
    Next k
    
    For l = 1 To sentence2
        Cells(lRow, 3) = "Hello world 2"
        lRow = lRow + 1
    Next l
        
End Sub

Upvotes: 0

Views: 130

Answers (1)

teylyn
teylyn

Reputation: 35915

You don't declare the variable addRows, but it looks like it should be an integer or a long. If you want to use such a variable in a Rows() statement, you cannot put the variable inside the double quotes, like you have here:

Rows("13:addRows").Insert Shift:=xlDown, _

Instead, use

Rows("13:" & addRows).Insert Shift:=xlDown, _

It is not clear what you want to do here, though. What if addRows is less than 13? then that statement does not make sense. You need to edit your question and explain what you want to achieve.

Upvotes: 1

Related Questions