I am looking for a way to shorten this code by consolidating each "Case is" instant into one getting the case name from a variable

Question

The purpose of the macro is to choose the correct data depending on the selected case in a dropdown menue on my "Recommendation" sheet.

This is my code:

Private Sub cmbNatCoList_Change()

'Define selected NatCo and variables for loop
Dim NatCoList As String
Dim ZeileUntersucht As Integer
Dim ZeileEintragen As Integer

'Line which is looked at in NatCo sheet
ZeileUntersucht = 17
'Line which is written in in Recommendation Sheet
ZeileEintragen = 15

'Selected NatCo
NatCoList = cmbNatCoList.Value

'Set selected NatCo on NatCoRecommendations sheet
'ThisWorkbook.Sheets("NatCoRecommendations").cmbNatCoList.Value = NatCoList

'Set position and size of combobox
cmbNatCoList.Top = 85
cmbNatCoList.Left = 1120
cmbNatCoList.Width = 100
cmbNatCoList.Height = 40

'Write selected NatCo on charts
Range("z1") = NatCoList
Worksheets("Stock Control Chart").Range("C61") = NatCoList
Worksheets("Price Chart").Range("D44") = NatCoList

'Search for recommendations for selected NatCo
 Select Case NatCoList

'========================================
Case Is = "TDG"
Worksheets("Recommendations").Rows("1:224").Hidden = False
    'Loop looking for recommendations in each line, writes BNCode in list if yes 
and continues with next line, if not just continues
For ZeileUntersucht = 20 To 515
    If Worksheets("TDG").Cells(ZeileUntersucht, 238).Value = "yes" Then
        Worksheets("Recommendations").Cells(ZeileEintragen, 3) = Worksheets("TDG").Cells(ZeileUntersucht, 1)
        ZeileEintragen = ZeileEintragen + 1

    End If
Next ZeileUntersucht




'===========================================
Case Is = "TMNL"
Worksheets("Recommendations").Rows("1:263").Hidden = False
    For ZeileUntersucht = 20 To 515
        If Worksheets("TMNL").Cells(ZeileUntersucht, 238).Value = "yes" Then
            Worksheets("Recommendations").Cells(ZeileEintragen, 3) = 
Worksheets("TMNL").Cells(ZeileUntersucht, 1)
            ZeileEintragen = ZeileEintragen + 1

    End If
Next ZeileUntersucht

This is parts of the code I am trying to improve.

Those Case Is cases are about 20 in total I am just showing you two here. The code inside is exactly the same, the only thing that changes with a selected case is the worksheet it is looking at.

Important to note: It is always only one case selected (used) for the macro.

If you need any more information, please ask.

score 0 · Accepted Answer · answered Dec 20 '17 at 11:04

I've noticed that the number of rows that in Recommendations that you set Hidden to false has changed for the first Case is 1:224 and the second is 1:263, but if this supposed to be the same then something like the following would do, simply replace your whole Select Case with the following:

Worksheets("Recommendations").Rows("1:224").Hidden = False
    For ZeileUntersucht = 20 To 515
        If Worksheets(NatCoList).Cells(ZeileUntersucht, 238).Value = "yes" Then
            Worksheets("Recommendations").Cells(ZeileEintragen, 3) = Worksheets(NatCoList).Cells(ZeileUntersucht, 1)
            ZeileEintragen = ZeileEintragen + 1
        End If
    Next ZeileUntersucht

Oh thank you good eye that should be the same number..! I will test your solution, thank you! — Tim, Dec 20 '17 at 13:06
@Xiabier Thank you very much that was very easy and is working perfectly! — Tim, Dec 20 '17 at 13:42

I am looking for a way to shorten this code by consolidating each "Case is" instant into one getting the case name from a variable

1 Answers1