(Solved) – Show the output in empty row

  • by

I am new to VBA. i have created triangles which i need to place into the 1st empty row above the given data.

Eg: Please Refer the image.

1) Output after running below code. Yellow marked lines is the row where the output should be shown.

enter image description here

2) actual output it should look like this . (I have edited it manually )

enter image description here

I have created triangle for every data in column c. which i have matched to RANGE(I4:AK4),
but as you can see in image, i am getting output in row 13 in actual it should be in empty row.

It is just my idea: As empty row should be used as the output for the given below data.

For Eg: ROW 20 should be used as output row for given below data in column C (C21,C22,C23)

Please refer the code below as i have tried to create triangle but not able to move to 1st empty row to given below data.

Sub addshape()
Dim lastrow As Long
Dim lastCol As Long
Dim heightCell As Long
Dim widthCell As Long
Dim R As Excel.Range
Dim i As Long
Dim l As Long
Dim s As Shape

With ThisWorkbook.Sheets("Tabelle1")

    lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
    lastCol = .Cells(4, Columns.Count).End(xlToLeft).Column
For i = 6 To lastrow
        If .Cells(i, 3) = " " Then
         Set R = .Range(.Cells(4, 9), .Cells(4, lastCol))

         If IsEmpty(.Cells(i, 3).Value) = False Then

         Set s = ActiveSheet.Shapes.addshape(msoShapeIsoscelesTriangle, 51, 220, 14, 12)
                With s
                .Name = "triangle" & i
                .Rotation = 180
                .TextFrame.Characters.Text = Cells(i, 2)
                .TextFrame.Characters.Font.Size = 10
                .TextFrame.Characters.Font.Color = RGB(0, 0, 0)
                .TextFrame.VerticalAlignment = xlVAlignCenter
                .TextFrame.HorizontalAlignment = xlHAlignCenter
                .Line.Weight = 1
                .Line.ForeColor.RGB = RGB(0, 0, 0)
                .TextEffect.Alignment = msoTextEffectAlignmentCentered
                .TextFrame.Orientation = msoTextOrientationHorizontal
                .TextFrame.VerticalOverflow = xlOartVerticalOverflowOverflow
                .Fill.ForeColor.RGB = RGB(245, 144, 66)
                End With
            s.Left = 10
            l = Application.WorksheetFunction.Match(.Cells(i, 3), R, 0)
            l = R(1, l).Left
            l = l   (R(1, l).Width / 24)
            s.Left = l
            End If
        End If
Next i
End With
End Sub

Please share your ideas or your codes.
Will be thankful.

Leave a Reply

Your email address will not be published. Required fields are marked *