How to show dynamic caption from a row of items where each item is 4 cell apart?

qwww

I have a sheet like this :

enter image description here

And I have a user form which will dynamically add labels based on data I have in a column or row. The issue is that if its all aligned one by one, then I am able to generate dynamic labels. But I need to generate the first row of this sheet. So my labels will be A,B,C,D,E etc

This is my code :

    Private Sub UserForm_Initialize()
     Me.ComboBox1.RowSource = "Sheet1!C8:C" & Range("C" & Rows.Count).End(xlUp).Row

     number = Range("E1").Value
     Dim lblL1 As Control
     Dim cell As Range
           Dim Rng As Range
    For i = 1 To number
        Set lblL1 = Controls.Add("Forms.Label.1")
        With lblL1
        .Name = "lbl" & i
        .Height = 20
        .Width = 50
        .Left = 20
        .Top = 20 * i * 1
        End With

    Next i


    Set Rng = Range("O6", "ED6") 
'This is what I added to achieve this. But I am getting the last one displayed and not others
         For Each cell In Rng.Cells
            If cell.Value <> vbNullString Then
            With lblL1
            .Caption = cell.Value
            End With
            End If

        Next cell



    End Sub

If the items in the row/column are adjacent then I can use something like this :

Private Sub UserForm_Initialize()
 Me.ComboBox1.RowSource = "Sheet1!C8:C" & Range("C" & Rows.Count).End(xlUp).Row

 number = Range("E1").Value
 Dim lblL1 As Control
 Dim cell As Range
       Dim Rng As Range
For i = 1 To number
    Set lblL1 = Controls.Add("Forms.Label.1")
    With lblL1
.Caption = "Label”" & i
    .Name = "lbl" & i
    .Height = 20
    .Width = 50
    .Left = 20
    .Top = 20 * i * 1
    End With

Next i
Dim q As Long
For q = 1 To number
 Controls("lbl" & q) = Cells(6, q).Value
Next q

End Sub

This is what I tried which gives me the last item in that row only :

 For Each cell In Rng.Cells
                If cell.Value <> vbNullString Then
                With lblL1
                .Caption = cell.Value
                End With
                End If

            Next cell

What has to be done to show all data in that row as each label (one by one) dynamically. Here each data is 3 cells apart.

teylyn

Without reading all your code, I'm trying to approach this from a logical point of view. You could

  • create an array, then loop through all the cells in the row and add each cell with content to the array
  • if the pattern is known, use OFFSET to get the values of the cells
  • change the data layout so each column has the label in row 1 and the respective values for that category in the cells below.

The last approach would make most sense, because it tackles the issue from a data architecture point of view instead of building workarounds for bad data layouts.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

how to show the caption div when current item is focused in owl carousel?

From Dev

How to create a heatmap where each cell is divided into 4 triangles?

From Dev

How to emit items from list with delay between each item?

From Dev

How to emit items from list with delay between each item?

From Dev

How to display columns from MySQL in one row with counters for each items

From Dev

How much do Linux distributions set apart from each other?

From Dev

How to show each item of <li>?

From Dev

How to show each item of <li>?

From Dev

How to get one row per each item from and mysql query

From Dev

Delphi 7 - How to delete an item from listview using its caption

From Dev

How To Add A Caption To A GridView Cell

From Dev

How to record a CheckBox Caption in a cell?

From Dev

How can i change color of particular row in datagridview, where a cell value is first timestamp of each day?

From Dev

dictionary item = cell value in last row in column for each grouping

From Dev

Order items alphabetically apart from World

From Dev

How to group items in array by summary each item

From Dev

JQuery/ How to show alerts for each table cell?

From Dev

Array callback for every item apart from one

From Dev

How to show list items on top of each other

From Dev

How to show progress bar in each listview item?

From Dev

How to show a unique image for each checkbox item?

From Dev

How to show each item in a PureScript List

From Dev

Add multipage caption by looping from cell values

From Dev

How to divide each row of a cell array by another cell array?

From Dev

How to show partial caption and on hover slide up the entire caption

From Java

Plotly: How to create a line plot from a pandas DataFrame where each cell contains a list?

From Dev

How to calculate multiplication of each row and show it with jquery

From Dev

How to calculate multiplication of each row and show it with jquery

From Dev

How to show the number for each row in a column in the cxgrid

Related Related

  1. 1

    how to show the caption div when current item is focused in owl carousel?

  2. 2

    How to create a heatmap where each cell is divided into 4 triangles?

  3. 3

    How to emit items from list with delay between each item?

  4. 4

    How to emit items from list with delay between each item?

  5. 5

    How to display columns from MySQL in one row with counters for each items

  6. 6

    How much do Linux distributions set apart from each other?

  7. 7

    How to show each item of <li>?

  8. 8

    How to show each item of <li>?

  9. 9

    How to get one row per each item from and mysql query

  10. 10

    Delphi 7 - How to delete an item from listview using its caption

  11. 11

    How To Add A Caption To A GridView Cell

  12. 12

    How to record a CheckBox Caption in a cell?

  13. 13

    How can i change color of particular row in datagridview, where a cell value is first timestamp of each day?

  14. 14

    dictionary item = cell value in last row in column for each grouping

  15. 15

    Order items alphabetically apart from World

  16. 16

    How to group items in array by summary each item

  17. 17

    JQuery/ How to show alerts for each table cell?

  18. 18

    Array callback for every item apart from one

  19. 19

    How to show list items on top of each other

  20. 20

    How to show progress bar in each listview item?

  21. 21

    How to show a unique image for each checkbox item?

  22. 22

    How to show each item in a PureScript List

  23. 23

    Add multipage caption by looping from cell values

  24. 24

    How to divide each row of a cell array by another cell array?

  25. 25

    How to show partial caption and on hover slide up the entire caption

  26. 26

    Plotly: How to create a line plot from a pandas DataFrame where each cell contains a list?

  27. 27

    How to calculate multiplication of each row and show it with jquery

  28. 28

    How to calculate multiplication of each row and show it with jquery

  29. 29

    How to show the number for each row in a column in the cxgrid

HotTag

Archive