Modify Len Formula

Posted on 2011-04-18
Last Modified: 2012-06-21
Hi Experts,

I would like to request Experts help. How to make the attached formula more flexible (Module at Sheet 5 (Chart2) so that when I amend the “title” at C41 (Chart2) doesn’t impact the Userfome  detail display. At present we can click the chart line by targeting the line for userfome display. If there was issue highlighted for that week, Userform will display related detail of the “Type” selection from Daily Tracking List sheet at  Userform. All selection (selection of type can be made by using Spinner) are working fine except the type under “testing” (column N at “Chart” sheet) category. I believe this is due to text length. Hope Experts will help me to solve this problem.

If Len(Cells(41, 3)) = 11 Then
      mem = Mid(Cells(41, 3), 8, 3)
        mem = Mid(Cells(41, 3), 8, 4)

Open in new window

Question by:Cartillo
    LVL 30

    Expert Comment

    You mean like this?

    Private Sub lblChart_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        'On Error GoTo lblChart_Err
        Dim objSheet As Worksheet
        Dim iWeekNumber As Integer
        Dim iWeekNumberRow As Integer
        Dim dtStart As Date
        Dim dtEnd As Date
        Dim iDailyRow As Integer
        Dim dtIssueDate As Date
        Dim mem As Variant
        Dim MyArray() As String
        MyArray = Split(Cells(41, 3), "(")
        mem = Left(MyArray(0), Len(MyArray(0)) - 1)
        lblChart.Visible = False
        Set objSheet = Sheets("Daily Tracking List")
        iWeekNumber = Int(X / (lblChart.Width / 54) + 1)
        If iWeekNumber = 0 Then iWeekNumber = 1
        iWeekNumberRow = GetWeekRow(iWeekNumber)
        dtStart = Sheets("Detail").Cells(iWeekNumberRow, 2).Value
        dtEnd = Sheets("Detail").Cells(iWeekNumberRow, 3).Value
        With UserForm1
             .Occurrence.Text = ""
             .Outage.Text = ""
             .TotalOutage.Text = ""
             .KPI.Text = ""
             .Updatesbox.Text = ""
            .DetailsBox.Text = ""
            .Updatesbox.Text = ""
        End With
        With UserForm1
            iDailyRow = 2
            Do Until objSheet.Cells(iDailyRow, 2).Value = ""
                dtIssueDate = objSheet.Cells(iDailyRow, 2).Value
                Select Case dtIssueDate
                    Case Is < dtStart
                        ' keep looking
                    Case Is <= dtEnd
                        ' process this
                        If objSheet.Range("D" & iDailyRow) = mem Then
                        .ListBox1.AddItem objSheet.Range("A" & iDailyRow).Value & ": " & objSheet.Range("B" & iDailyRow).Value & " " & Format(objSheet.Range("C" & iDailyRow).Value, "hh:mm:ss")
                        .DetailsBox.Text = objSheet.Range("J" & iDailyRow).Value
                        .Occurrence.Text = objSheet.Range("E" & iDailyRow).Value
                        .Outage.Text = objSheet.Range("G" & iDailyRow).Value
                        .TotalOutage.Text = objSheet.Range("H" & iDailyRow).Value
                        .KPI.Text = objSheet.Range("I" & iDailyRow).Value
                        .Status.Text = objSheet.Range("M" & iDailyRow).Value
                        .Updatesbox.Text = objSheet.Range("K" & iDailyRow).Value
                        '.Area.Text = objSheet.Range("?" & iDailyRow).Value
                        End If
                    Case Else
                        ' assuming dates are in sequence, we're past the requested issue so we're done
                        Exit Do
                End Select
                iDailyRow = iDailyRow + 1
            If UserForm1.ListBox1.ListCount = 0 Then UserForm1.ListBox1.AddItem "No issues for week " & iWeekNumber
        End With
        Set objSheet = Nothing
        lblChart.Visible = True
        Exit Sub
        MsgBox "Error found: " & Err.Description
        Resume lblChart_Exit
    End Sub

    Open in new window

    LVL 30

    Expert Comment

    Amended File Attached


    Author Comment

    Hi Sid,

    Thanks for the file. The detail information wasn’t displayed in the userform, e.g. using my original posting when we select “TH9”  type and  click at week 9 (plot area) the userfome will display all related info at List boxes. The similar things should display for “testing” type as well. Hope I’m not confusing you.

    Author Comment

    Hi Sid,

    Hope my explanation in confusing you. What happen was the link for displaying the data which is C41 (Chart2) is not linking the “lblChart_MouseDown” module (sheet5) when I amend the “listing” sheet title. The line chart was plotted based on “Chart” sheet data. The cell C41 (Chart2) is actually mirroring cell V1 (Chart sheet). Hope this explanation gives you better understanding.

    Author Comment


    After test few data I noticed the connection totally depends on number of correctors that was used cell C41. Is that possible to set the corrector is  between 3 to 7

    “If Len(Cells(41, 3)) = 11 Then
          mem = Mid(Cells(41, 3), 8, 3)”

    LVL 30

    Accepted Solution

    Sorry there was a sligt typo.

    Change line 15 in my code to

     mem = Left(MyArray(1), Len(MyArray(1)) - 1)

    Now Try it. :)


    Author Closing Comment

    Thanks Sid

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now