Modify Len Formula

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)
    Else
        mem = Mid(Cells(41, 3), 8, 4)

Open in new window

Dynamic-Chart.xls
CartilloAsked:
Who is Participating?
 
SiddharthRoutCommented:
Sorry there was a sligt typo.

Change line 15 in my code to

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

Now Try it. :)

Sid
0
 
SiddharthRoutCommented:
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 = ""
         .ListBox1.Clear
        .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
        Loop
        If UserForm1.ListBox1.ListCount = 0 Then UserForm1.ListBox1.AddItem "No issues for week " & iWeekNumber
        .Show
    End With
 
lblChart_Exit:
    Set objSheet = Nothing
    lblChart.Visible = True
    Exit Sub
    
lblChart_Err:
    MsgBox "Error found: " & Err.Description
    Resume lblChart_Exit
    Resume
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
Amended File Attached

Sid
Dynamic-Chart.xls
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CartilloAuthor Commented:
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.
0
 
CartilloAuthor Commented:
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.
0
 
CartilloAuthor Commented:
Hi,

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)”

0
 
CartilloAuthor Commented:
Thanks Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.