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.
2) actual output it should look like this . (I have edited it manually )
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 Else 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.