Posts by vkingw2k


    Why is the value of .Item("abc")(0) still remain as 1?

    Hi guys, need your assistance,


    I'm trying to update the value of dictionary as below, but to no avail. The value of dic.Item(x(i, 1))(6) remain unchanged. :(


    Re: Redim Array size


    Thanks MickG, exactly what I'm looking for. How about if I were to apply on this coding below?


    Re: Populating Array to Listbox


    Thanks buddy, any idea for this?


    Say if I add a combobox, how do I populate the combobox value with years available (E.g. in this example 2010 to 2014), then when I select the year in combobox, it will only show the rating for that year? E.g. If I click 2013 on combobox, it will only show Year 2013 - Rating 4 using the same listbox, click 2012 and I'll see Year 2012 and its rating.

    Re: Populating Array to Listbox


    Thanks pike for the prompt reply,


    1. how do I make it to detect the last column of year available and populate instead of setting a fixed row number. Say if in future I add year 2015 and 2016 it will auto populate too.
    2. Say if I add a combobox, how do I populate the combobox value with years available (E.g. in this example 2010 to 2014), then when I select the year in combobox, it will only show the rating for that year? E.g. If I click 2013 on combobox, it will only show Year 2013 - Rating 4 using the same listbox.


    :)

    Hi guys,


    Need ya assistance, how do I populate the values into the listbox as below?


    Code
    Private Sub txtTMID_AfterUpdate()
    Dim Arr() As String
    Dim rng As Range, pl As Worksheet: Set pl = Sheets("Sheet1")
    Set rng = pl.Range("A2:A" & pl.Range("A" & Rows.Count).End(xlUp).Row).Find(Me.txtTMID.Value, lookat:=xlWhole)
        If Not rng Is Nothing Then
            
        End If
        
        Me.lstRating = Arr
    End Sub



    [ATTACH=CONFIG]58913[/ATTACH]


    Download the workbook here.
    https://db.tt/4M4mfmGF

    Hi guys,


    How do I recode to redim and add one more row to array Arr whenever 'If Val(x(i, 1)) = Me.txtTMID.Value' is True?


    I want to clear all these label at once, but when I run this code it cleared all the labels up to "CCC".


    Caption of label "BB", "RL", "TS", "MD", "MW", "BJ", "CS", "TH", "PO", "PN", "TC", "CW", "PG", "TP", "CP", "LEN", "LMD", "LHK", "LCN", "LML", "LKO", "JP", "LVT", "LTH" remained unchanged. Anything wrong with my code?


    Code
    Sub Refresh()
    Me.lblTM1.Picture = LoadPicture("")
    strclear = Array("Name", "Nationality", "ServiceLength", "Prof", "Gender", "PM", "SM", "SL", "AWOL", "HL", _ 
                                     "UNAUTH", "TAbsent", "TWorking", "TLeave", "ARate", "ODRDO", "ALPHL", "Contact", "CSEL", _ 
                                     "Expiry", "CCC", "BB", "RL", "TS", "MD", "MW", "BJ", "CS", "TH", "PO", "PN", "TC", "CW", "PG", _ 
                                     "TP", "CP", "LEN", "LMD", "LHK", "LCN", "LML", "LKO", "JP", "LVT", "LTH")
    For i = LBound(strclear) To UBound(strclear)
         Me.Controls("lbl" & strclear(i)).Caption = xlNull
    Next
    End Sub

    Hey peeps,


    Here's my coding, how do I code to retrieve item for "1572"'s - BJ which is 50?


    I tried via msgbox but to no avail.



    [TABLE="class: grid, width: 50"]

    [tr]


    [td]

    1572

    [/td]


    [td]

    BJ

    [/td]


    [td]

    50

    [/td]


    [/tr]


    [tr]


    [td]

    16818

    [/td]


    [td]

    PN

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    16818

    [/td]


    [td]

    TP

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    22985

    [/td]


    [td]

    BB

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    22985

    [/td]


    [td]

    TS

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    1572

    [/td]


    [td]

    RL

    [/td]


    [td]

    50

    [/td]


    [/tr]


    [tr]


    [td]

    1572

    [/td]


    [td]

    PN

    [/td]


    [td]

    50

    [/td]


    [/tr]


    [tr]


    [td]

    1572

    [/td]


    [td]

    TS

    [/td]


    [td]

    50

    [/td]


    [/tr]


    [/TABLE]

    Re: VBA to format data from single column to two column


    pardon me, I wanted to format the single column list into the format below


    [TABLE="class: grid, width: 50"]

    [tr]


    [td]

    ID Number

    [/td]


    [td]

    02-GROUP

    [/td]


    [td]

    03-TEAM

    [/td]


    [td]

    04-NOMINATION

    [/td]


    [/tr]


    [tr]


    [td]

    [0016798]

    [/td]


    [td]

    0001

    [/td]


    [td]

    D26

    [/td]


    [td]

    00050 (MORNING)

    [/td]


    [/tr]


    [tr]


    [td]

    [0022985]

    [/td]


    [td]

    0002

    [/td]


    [td]

    M20

    [/td]


    [td]

    00098 (DAY)

    [/td]


    [/tr]


    [tr]


    [td]

    [0016818]

    [/td]


    [td]

    0004

    [/td]


    [td]

    N12

    [/td]


    [td]

    00100 (DAY)

    [/td]


    [/tr]


    [/TABLE]

    Re: VBA to format data from single column to two column


    Exactly what I wanted, thanks mate!


    What if they are in the format below? I tried modifying the code but to no avail :(


    https://db.tt/m2MfCsQA


    I wanted it to show like


    [TABLE="class: grid, width: 60"]

    [tr]


    [td]

    ID

    [/td]


    [td]

    NOMINATION

    [/td]


    [td]

    TEAM

    [/td]


    [td]

    GROUP

    [/td]


    [/tr]


    [tr]


    [td]

    016798

    [/td]


    [td]

    255

    [/td]


    [td]

    M36

    [/td]


    [td]

    0050

    [/td]


    [/tr]


    [tr]


    [td]

    016818

    [/td]


    [td]

    30

    [/td]


    [td]

    N20

    [/td]


    [td]

    0008

    [/td]


    [/tr]


    [/TABLE]

    Hi guys, how can code the VBA to format the table below to the format underneath?




    Before

    [TABLE="class: grid, width: 50"]

    [tr]


    [td]

    NOM 1

    [/td]

    [/tr]

    [tr][td]

    [/td]


    [/tr]


    [tr]


    [td]

    022985

    [/td]


    [/tr]


    [tr]


    [td]

    016798

    [/td]


    [/tr]


    [tr]


    [td]

    NOM 2

    [/td]


    [/tr]


    [tr]


    [td]

    016818

    [/td]


    [/tr]


    [tr]


    [td]

    002979

    [/td]


    [/tr]


    [tr]


    [td]

    001557

    [/td]


    [/tr]


    [tr]


    [td]

    NOM 5

    [/td]


    [/tr]


    [tr]


    [td]

    001541

    [/td]


    [/tr]


    [tr]


    [td]

    001573

    [/td]


    [/tr]


    [tr]


    [td]

    003742

    [/td]


    [/tr]


    [tr]


    [td]

    003100

    [/td]


    [/tr]


    [tr]


    [td]

    022999

    [/td]


    [/tr]


    [tr]


    [td]

    008387

    [/td]


    [/tr]


    [/TABLE]



    After


    [TABLE="class: grid, width: 80"]

    [tr]


    [td]

    022985

    [/td]


    [td]

    NOM 1

    [/td]


    [/tr]


    [tr]


    [td]

    016798

    [/td]


    [td]

    NOM 1

    [/td]


    [/tr]


    [tr]


    [td]

    016818

    [/td]


    [td]

    NOM 2

    [/td]


    [/tr]


    [tr]


    [td]

    002979

    [/td]


    [td]

    NOM 2

    [/td]


    [/tr]


    [tr]


    [td]

    001557

    [/td]


    [td]

    NOM 2

    [/td]


    [/tr]


    [tr]


    [td]

    001541

    [/td]


    [td]

    NOM 5

    [/td]


    [/tr]


    [tr]


    [td]

    001573

    [/td]


    [td]

    NOM 5

    [/td]


    [/tr]


    [tr]


    [td]

    003742

    [/td]


    [td]

    NOM 5

    [/td]


    [/tr]


    [tr]


    [td]

    003100

    [/td]


    [td]

    NOM 5

    [/td]


    [/tr]


    [tr]


    [td]

    022999

    [/td]


    [td]

    NOM 5

    [/td]


    [/tr]


    [tr]


    [td]

    008387

    [/td]


    [td]

    NOM 5

    [/td]


    [/tr]


    [/TABLE]

    Hi guys,


    Below is my code. Is there any way to shorten the coding?