How do i force VBA to add a row itself and used that row to print the output-CodePudding

I want to add some code in which the code add an empty row at B29 itself rather then i add myself without disturbing the Alignement of CRONS in B29. I know after adding empty row at B29 CRONS will no more at B29 but it doesn't matter what matter is the alignment for for rest of the sheet. The picture is from sheet1 where I have to add the empty row and print the output (In line14).

The code I wrote is based on 2 sheets

1. Sub Energy_consumption_per_cubic()
2. Dim s1 As Worksheet, s2 As Worksheet
3. Dim check1 As Boolean, check12 As Boolean, motor_power As Boolean, flow As Boolean

4. Set s1 = ThisWorkbook.Worksheets(1)
5. Set s2 = ThisWorkbook.Worksheets(2)

6. check1 = s2.CHECKBOXES("Check Box 1").Value = xlOn
7. check12 = s2.CHECKBOXES("Check Box 5").Value = xlOn
8. motor_power = s1.Range("B25").Value = "Motor Power"
9. flow = s1.Range("B26").Value = "Flow(from fill level)"


10. If ((check1 And check12) Or _
11.     (check1 And motor_power) Or _
12.     (flow And check12) Or _
13.     (flow And motor_power)) Then
14.        s1.Range("B29").Value = "Energy consumption per cubic"
15. Else
16.     s1.Range("B29").Value = "-"
17. End If

18. End Sub

CodePudding user response：

Please, test the next updated code. It searches for string "Crons" in column "A:A" and inserts a row above it (if found):

Sub Energy_consumption_per_cubic()
 Dim s1 As Worksheet, s2 As Worksheet, cellCr As Range
 Dim check1 As Boolean, check12 As Boolean, motor_power As Boolean, flow As Boolean

 Set s1 = ThisWorkbook.Worksheets(1)
 Set s2 = ThisWorkbook.Worksheets(2)

 check1 = s2.CheckBoxes("Check Box 1").value = xlOn
 check12 = s2.CheckBoxes("Check Box 5").value = xlOn
 motor_power = s1.Range("B25").value = "Motor Power"
 flow = s1.Range("B26").value = "Flow(from fill level)"

 Set cellCr = s1.Range("A1:A" & s1.Range("A" & s1.rows.count).End(xlUp).row).Find("Crons")
     If Not cellCr Is Nothing Then 'if a cell in A:A having "Crons" value exists:
        s1.rows(cellCr.row).Insert 'insert a row above it
        If ((check1 And check12) Or _
                (check1 And motor_power) Or _
                (flow And check12) Or _
                (flow And motor_power)) Then
            s1.Range("B29").value = "Energy consumption per cubic"
        Else
           s1.Range("B29").value = "-"
        End If
    Else
        MsgBox "Could not found ""Crons"" in A:A...": Exit Sub
    End If
End Sub