Simplify formula

[MrThor]

Hi, I have a formula here which works but is not so handy. I dont want for example create a new userform for every day in Januari as you can se that I have done here. Is there a way to create a code to do the same thing with only one userform and a shorter code?

If Not Application.Intersect(Target, Range("B5")) Is Nothing And Me.ComboBox1.Value = "Jan" ThenJan1.Show


sh1.Range("A13:A21").Value = sh2.Range("B2:B10").Value
sh1.Range("B13:B21").Value = sh2.Range("C2:C10").Value
sh1.Range("D13:D21").Value = sh2.Range("E2:E10").Value


End If


If Not Application.Intersect(Target, Range("C5")) Is Nothing And Me.ComboBox1.Value = "Jan" Then
Jan2.Show


sh1.Range("A13:A21").Value = sh2.Range("B12:B21").Value
sh1.Range("B13:B21").Value = sh2.Range("C12:C21").Value
sh1.Range("D13:D21").Value = sh2.Range("E12:E21").Value
End If


If Not Application.Intersect(Target, Range("D5")) Is Nothing And Me.ComboBox1.Value = "Jan" Then
Jan3.Show


sh1.Range("A13:A21").Value = sh2.Range("B23:B32").Value
sh1.Range("B13:B21").Value = sh2.Range("C23:C32").Value
sh1.Range("D13:D21").Value = sh2.Range("E23:E32").Value
End If

 

[Rijnsent]

Hi MrThor,
not sure if this his what you are looking for, but this could be another approach:

If Me.ComboBox1.Value = "Jan" Then

    StartRw = 0
    If Not Application.Intersect(Target, Range("B5")) Is Nothing Then
        Jan1.Show
        StartRw = 2
    End If
    If Not Application.Intersect(Target, Range("C5")) Is Nothing Then
        Jan2.Show
        StartRw = 12
    End If
    If Not Application.Intersect(Target, Range("D5")) Is Nothing Then
        Jan3.Show
        StartRw = 23
    End If
    
    If StartRw > 0 Then
        sh1.Range("A13:A21").Value = sh2.Range("B" & StartRw & ":B" & StartRw + 8).Value
        sh1.Range("B13:B21").Value = sh2.Range("C2:C10").Offset(StartRw - 2, 0).Value
        sh1.Range("D13:D21").Value = sh2.Range("E2:E10").Offset(StartRw - 2, 0).Value
        'Both offset and the build_a_string version work fine, it's what you prefer
    End If

End If

 

[MrThor]

Hi Rijnsent,

It seems like your code nearly works! But I get one "object required" according to the cursive style below:

[COLOR=#333333]    If StartRw > 0 Then[/COLOR]        [I]sh1.Range("A13:A21").Value = sh2.Range("B" & StartRw & ":B" & StartRw + 8).Value[/I]
        sh1.Range("B13:B21").Value = sh2.Range("C2:C10").Offset(StartRw - 2, 0).Value
        sh1.Range("D13:D21").Value = sh2.Range("E2:E10").Offset(StartRw - 2, 0).Value
        'Both offset and the build_a_string version work fine, it's what you prefer [COLOR=#333333]    End If[/COLOR]

Thank you for your help!

 

[MrThor]

Ty Rijnsent, It worked