Learn how to a build a cloud-first strategyRegister Now


Modify Len Formula

Posted on 2011-04-18
Medium Priority
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
  • 4
  • 3
LVL 30

Expert Comment

ID: 35421749
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

ID: 35421759
Amended File Attached


Author Comment

ID: 35421956
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 35423162
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

ID: 35423367

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

SiddharthRout earned 2000 total points
ID: 35424722
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

ID: 35453641
Thanks Sid

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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