Please guide me by anyone for the periods of month

[Mohammad Ishaq]

Hello, I have a userform with a listbox and a combobox .The listbox contain some items.I want that when i click on any item in the listbox,the combobox show the one month later of the latest period.For example when i click "AA" the combobox show Jul - Aug 2020 instead of Jun - Jul 2020(Latest Month Period).I attached the image and the file also,please guide me about this scenario .

 

[vletm]

Mohammad Ishaq
Did You use something from Your previous threads solution

How to show one month later after recent month based on selection from listbox

Hi,I have a userform with a listbox and textbox.I have also some data on sheet one.My question is that when i click on any item on listbox,the textbox show one month later of recent month.For Example if i select the name AA,then the most recent month in the second column is August-20,but i want...

chandoo.org

... You wanted to show something like You're getting the result now.
( Because, the form - not match with my Excel )

 

[Hui]

Try this

Private Sub ListBox1_Click()

Dim var1 As Variant
var1 = Range("Name").Value

Dim var2 As Variant
var2 = Range("Period").Value

Dim i As Integer, mySel As Variant

For i = 1 To UBound(var1)
  ' Debug.Print i, var1(i, 1)
   If var1(i, 1) = ListBox1.Value Then mySel = i
Next

UserForm1.ComboBox1.Value = var2(mySel, 1)

End Sub

 

[Chihiro]

Why combo box? It's not like you are adding any list to it...

Also, you will need to tell us how "Nov - Dec 2020" should be handled.

Should it be... "Dec - Jan 2021"? Or something else?

Assuming "Dec - Jan 2021" is the expected result when CD is selected...

Private Sub ListBox1_Change()
With ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) = True Then
            ComboBox1.Value = StrMonth(.List(i))
        End If
    Next
End With
End Sub

Function StrMonth(lName As String) As String
m = 0
With Sheets("Sheet1")
    tmp = .Range("H11:I" & .Cells(Rows.Count, "H").End(xlUp).Row).Value
    For i = 1 To UBound(tmp)
        tmp(i, 2) = DateValue(Split(tmp(i, 2), " ")(0) & " 1, " & Split(tmp(i, 2), " ")(3))
        If tmp(i, 1) = lName Then
            m = IIf(m < tmp(i, 2), tmp(i, 2), m)
        End If
    Next
End With
StrMonth = Format(DateAdd("m", 1, m), "mmm") & " - " & Format(DateAdd("m", 2, m), "mmm yyyy")
End Function

 

[Chihiro]

Or using Hui's code as basis...
Note: This one requires that H:I range is sorted (i.e. Later date string for AA does not appear before earlier date of AA). Since it's single pass with no comparison operator.

Private Sub ListBox1_Click()

Dim var1 As Variant
var1 = Range("Name").Value

Dim var2 As Variant
var2 = Range("Period").Value

Dim i As Integer, mySel As Variant

For i = 1 To UBound(var1)
  ' Debug.Print i, var1(i, 1)
   If var1(i, 1) = ListBox1.Value Then mySel = i
Next

Dim tmp As Variant
tmp = var2(mySel, 1)
tmp = DateValue(Split(tmp, " ")(0) & " 1, " & Split(tmp, " ")(3))
UserForm1.ComboBox1.Value = Format(DateAdd("m", 1, tmp), "mmm") & " - " & Format(DateAdd("m", 2, tmp), "mmm yyyy")

End Sub

 

[Hui]

I'm assuming the duplicate date ranges are a mistake
But I may be wrong, in which case so is my code

 

[Mohammad Ishaq]

Good Job .Thanks for your co-operation.