Collapse column

Autor Thema: Office 2010: Variablen Zuweisung  (Gelesen 227 mal)

Offline Streat

  • Newbie
  • *
  • Beiträge: 1
    • Profil anzeigen
  • Office-KnowHow: Fortgeschritten
  • VBA-KnowHow- : Mittelmäßig
  • Version [Office] : Office 2010
Office 2010: Variablen Zuweisung
« am: Februar 24, 2018, 09:23:50 Vormittag »
Folgender Code bereitet mir Probleme.

If CheckBox35.Value = True Then
If Tag = "01" Or "1" Then
Rng1 = "B"
Rng2 = "D"
ElseIf Tag = "02" Or "2" Then
Rng1 = "Q"
Rng2 = "S"
ElseIf Tag = "03" Or "3" Then
Rng1 = "AF"
Rng2 = "AH"
ElseIf Tag = "04" Or "4" Then
Rng1 = "AU"
Rng2 = "AW"
ElseIf Tag = "05" Or "5" Then
Rng1 = "BJ"
Rng2 = "BL"
ElseIf Tag = "06" Or "6" Then
Rng1 = "BY"
Rng2 = "CA"
ElseIf Tag = "07" Or "7" Then
Rng1 = "CN"
Rng2 = "CP"
ElseIf Tag = "08" Or "8" Then
Rng1 = "DC"
Rng2 = "DE"
ElseIf Tag = "09" Or "9" Then
Rng1 = "DR"
Rng2 = "DT"
ElseIf Tag = "10" Then
Rng1 = "EG"
Rng2 = "EI"
ElseIf Tag = "11" Then
Rng1 = "EV"
Rng2 = "EX"
ElseIf Tag = "12" Then
Rng1 = "FK"
Rng2 = "FM"
ElseIf Tag = "13" Then
Rng1 = "FZ"
Rng2 = "GB"
ElseIf Tag = "14" Then
Rng1 = "GO"
Rng2 = "GQ"
ElseIf Tag = "15" Then
Rng1 = "HD"
Rng2 = "HF"
ElseIf Tag = "16" Then
Rng1 = "HS"
Rng2 = "HU"
ElseIf Tag = "17" Then
Rng1 = "IH"
Rng2 = "IJ"
ElseIf Tag = "18" Then
Rng1 = "IW"
Rng2 = "IY"
ElseIf Tag = "19" Then
Rng1 = "JL"
Rng2 = "JN"
ElseIf Tag = "20" Then
Rng1 = "KA"
Rng2 = "KC"
ElseIf Tag = "21" Then
Rng1 = "KP"
Rng2 = "KR"
ElseIf Tag = "22" Then
Rng1 = "LE"
Rng2 = "LG"
ElseIf Tag = "23" Then
Rng1 = "LT"
Rng2 = "LV"
ElseIf Tag = "24" Then
Rng1 = "MI"
Rng2 = "MK"
ElseIf Tag = "25" Then
Rng1 = "MX"
Rng2 = "MZ"
ElseIf Tag = "26" Then
Rng1 = "NM"
Rng2 = "NO"
ElseIf Tag = "27" Then
Rng1 = "OB"
Rng2 = "OD"
ElseIf Tag = "28" Then
Rng1 = "OQ"
Rng2 = "OS"
ElseIf Tag = "29" Then
Rng1 = "PF"
Rng2 = "PH"
ElseIf Tag = "30" Then
Rng1 = "PU"
Rng2 = "PW"
ElseIf Tag = "31" Then
Rng1 = "QJ"
Rng2 = "QL"
End If

Egal welchen Tag der Nutzer auswählt, das Programm nimmt immer den ersten Tag und gibt daher den Variablen Rng1 und Rng2 den Wert B und D. Was kann zu diesem Problem führen?

Offline maninweb

  • Global Moderator
  • Hero Member
  • *****
  • Beiträge: 927
  • Microsoft Excel MVP
    • Profil anzeigen
    • Excel Formula Translator
  • Office-KnowHow: Experte
  • VBA-KnowHow- : Sehr gut
  • Version [Office] : Office 2010
Antw:Office 2010: Variablen Zuweisung
« Antwort #1 am: Februar 24, 2018, 09:47:36 Vormittag »
Hallo,

zunächst sind die If-Bedinungen nicht korrekt, es sollte z.B. für das erste If sein: If Tag = "01" Or Tag = "1" Then
Eine Select-Case-Anweisung wäre zudem etwas übersichtlicher. Hier ein Beispiel:

Code: Visual Basic
  1.   If CheckBox35.Value = True Then
  2.  
  3.     Select Case CLng(Tag)
  4.    
  5.       Case 1
  6.        
  7.         Rng1 = "B"
  8.         Rng2 = "D"
  9.        
  10.       Case 2
  11.        
  12.         Rng1 = "Q"
  13.         Rng2 = "S"
  14.      
  15. '     Case 3
  16. '
  17. '     Entsprechend fortführen
  18. '
  19. '     :
  20. '     :
  21.      
  22.       Case Else
  23.      
  24.     End Select
  25.    
  26.   End If

Gruß
Microsoft Excel MVP 2011-2017/2018 · excel-ticker.de · excel-translator.de · excel-soccer.de