CODEWITHSUNDEEP
excelvbainsertrows
Lola
Lola

Reputation: 97

VBA: insert a variable number of rows

I've written the code below but I can't get past a line where I want to insert a variable number of rows. The compiler screams that it needs a "list separator or )" where there is a colon. But I can't find other ways of writing this. Please, help! =) So, the problematic line is denoted by two stars from each end. Just above it there is a commented line that I've also tried with no success. Also, would you be so kind and explain me what I need the following commands for (they are also found in the problematic line):Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Finally, any suggestions of how I could improve the code, s.t., experienced programmers don't faint when they read it? =)

Huge thank you in advance,

Option Explicit
Dim ws As Worksheet
Dim Blatt1, Blatt2 As String
Dim Anfangsjahr1, Anfangsjahr2 As Integer
Dim reporting_Jahr1, reporting_Jahr2 As String

Public Sub Dreiecke()

For Each ws In Worksheets

    If ws.Name Like "RVA_H*" Then

            If IsEmpty(Blatt1) = False Then
            Blatt2 = ws.Name
            Anfangsjahr2 = ws.Range("A3").Value
            reporting_Jahr2 = ws.Range("A1").Value
            Else
            Blatt1 = ws.Name
            Anfangsjahr1 = ws.Cells(3, 1).Value
            reporting_Jahr1 = ws.Cells(1, 1).Value
            GoTo X
            End If
    Else: GoTo X
    End If

    If reporting_Jahr1 <> reporting_Jahr2 Then
     MsgBox "Dreiecke von unterschiedlichen Jahren"
     Exit Sub

    ElseIf reporting_Jahr1 = reporting_Jahr2 Then
        If Anfangsjahr1 < Anfangsjahr2 Then

        'Sheets(Blatt2).Rows(3:3+Anfangsjahr2-Anfangsjahr1).EntireRow.Insert
        **Worksheets(Blatt2).Rows(3: 3 + Anfangsjahr2 - Anfangsjahr1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove**

    ElseIf Anfangsjahr1 > Anfangsjahr2 Then
        Worksheets(Blatt1).Rows(3:3+Anfangsjahr1-Anfangsjahr2).Insert Shift:=xlDown

    ElseIf Anfangsjahr1 = Anfangsjahr2 Then GoTo X
    End If


    End If
X: Next ws



End Sub

Upvotes: 1

Views: 5294

Answers (2)

MatthewD
MatthewD

Reputation: 6761

I don't follow exactly what you are trying to get to here but there are some syntax issues.

Not sure if this is what you want, but it fixes the syntax. Do you rally want

Worksheets(Blatt2).Rows("3:" & 3 + Anfangsjahr2 - Anfangsjahr1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

EDIT to expand on OP's question in the comments below
The double quotes are used to enclose a string. So normally when you are referencing rows you can say .Rows("3:9").Insert This is providing the rows you want to work with as a string.
In your case you wanted to dynamically provide last row so we have

our string "3:"
transition from string to variable &
and our variables and math 3 + Anfangsjahr2 - Anfangsjahr1

To make up .Rows("3:" & 3 + Anfangsjahr2 - Anfangsjahr1).Insert

Upvotes: 1

luke_t
luke_t

Reputation: 2975

You need to place the row number in double quotation marks and then concatenate the variables to the string using an ampersand:

Worksheets(Blatt2).Rows("3:" & 3 + Anfangsjahr2 - Anfangsjahr1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Upvotes: 0

Related Questions