[VBA] 2 Arrays miteinander verknüpfen

[jules]

Hallo zusammen,

folgendes Szenario; Verwendung in einer IF-Abfrage:

Code:

   arr = Array("test1", "test2", "test3")
   arr2 =Array("testdatei_1.xls", "testdatei_2.xls","testdatei_3.xls")
 
Set Bereich = .Range("C2:N2")    
    For Each Zelle In Bereich

     If Zelle = arr  Then
           MsgBox arr2
       End If

   Next

Natürlich funktioniert die nachfolgende Zeile so nicht. Ich möchte, dass er prüft, ob in Zelle ein Begriff aus arr vorkommt und wenn ja, er dann den dazugehörigen Wert aus arr2 auswirft:
 If Zelle = arr Then

 MsgBox arr2

Jemand eine Idee?


Gruss

[Steffl]

Hallo,

falls sich in dem Bereich keine leeren Zellen befinden

Code:

Sub prcJules()
   Dim arr As Variant, arr2 As Variant
   Dim Bereich As Range, Zelle As Range
   arr = Array("test1", "test2", "test3")
   arr2 = Array("testdatei_1.xls", "testdatei_2.xls", "testdatei_3.xls")
  
   With ActiveSheet
      Set Bereich = .Range("C2:N2")
      For Each Zelle In Bereich
        
         '     If Zelle = arr Then
         If IsNumeric(WorksheetFunction.Match(Zelle.Value, arr, 0)) Then
            MsgBox arr2(WorksheetFunction.Match(Zelle.Value, arr, 0) - 1)
         End If
        
      Next
   End With
End Sub

[jules]

Servus,

klingt super!

Werde es gleich mall testen..

Danke!

- Kann ich die Werte für den Array auch aus einer Liste (Range) auslesen.. ?

[jules]

Hi nochmal,

es funktioniert wenn ich es auf einem Blatt nutze, allerdings nicht, wenn ich es wie in meiner Mappe für mehrere Blätter anwenden will:

Code:

For Each wks In Worksheets
   Select Case wks.Name
Case "Inhalt", "Data", "Suppliers", "Budget", "Jan", "Feb", "March", "April", "June", "July", "August", "Sept", "Oct", "Nov", "Dec", "Preise"
Case Else
           With wks
           Blatt = .Range("B1")

   Set Bereich = .Range("C2:N2")
   For Each Zelle In Bereich
   Zellen = Range(Cells(4, Zelle.Column), Cells(21, Zelle.Column)).Address

   If IsNumeric(WorksheetFunction.Match(Zelle.Value, arr, 0)) Then
          Dateiname = arr2(WorksheetFunction.Match(Zelle.Value, arr, 0) - 1)
          MsgBox Dateiname
         

Woran liegt's ?

Merci und LG

[jules]

** MEIN FEHLER **

.. Anscheinend muss ich bei "Case"   E X A K T  den Tabellennamen angeben. Also auch Groß-/Klein Schreibung etc. 

Funktioniert nun ... *nerv

Danke!!

--

Kann man vielleicht im Select Case die xlSheetHidden & veryHidden auswählen, anstatt jedes Blatt einzelnd aufzulisten?

For Each wks In Worksheets
Select Case wks.Name

Case "Hidden"
wks.Visible = xlSheetHidden
wks.Visible = xlSheetVeryHidden

Case Else
..

funktioniert so wohl nicht...
nur wenn ich es wirklich einzelnd aufliste:

Case "Inhaltsverzeichnis", "Data", "Suppliers", "BUDGET", "JAN ALL", "FEB ALL", "MARCH ALL", "APRIL ALL", "MAY ALL", "JUNE ALL", "JULY ALL", "AUG ALL", _
"SEPT ALL", "OCT ALL", "NOV ALL", "DEC ALL", "Preise", ....

Vielleicht hat dazu noch jemand eine Idee?

[Steffl]

Hallo,

Kann man vielleicht im Select Case die xlSheetHidden & veryHidden auswählen, anstatt jedes Blatt einzelnd aufzulisten?

For Each wks In Worksheets
  Select Case wks.Name

Case "Hidden"
wks.Visible = xlSheetHidden
wks.Visible = xlSheetVeryHidden

Case Else

was bezweckst Du damit?

[jules]

Ich lasse alle Blätter durch eine Schleife laufen.

For Each wks In Worksheets
   Select Case wks.Name

und möchte aber Blätter die Hidden oder veryHidden sind, sowie 2-3 weitere Blätter davon ausschließen.


Aktuell wie folgt gelöst:

Code:

For Each wks In Worksheets
  Select Case wks.Name

Case "Inhaltsverzeichnis", "Data", "Suppliers", "BUDGET", "JAN ALL", "FEB ALL", "MARCH ALL", "APRIL ALL", "MAY ALL", "JUNE ALL", "JULY ALL", "AUG ALL", _
"SEPT ALL", "OCT ALL", "NOV ALL", "DEC ALL", "Preise", "

Case Else
          With wks
          Blatt = .Range("B1")

  Set Bereich = .Range("C2:N2")
  For Each Zelle In Bereich

(die Liste ist eigentlich noch etwas länger. Daher wäre es einfacher alle versteckten Blätter auf einen Schlag schonmal auszuschließen..

[Kuwer]

Hallo,

For Each wks In Worksheets
  If wks.Visible = xlVisible Then
    Select Case UCase(wks.Name)
      Case "Inhaltsverzeichnis", "Data", "Suppliers", "BUDGET", "JAN ALL", "FEB ALL", "MARCH ALL", "APRIL ALL", "MAY ALL", "JUNE ALL", "JULY ALL", "AUG ALL", _
           "SEPT ALL", "OCT ALL", "NOV ALL", "DEC ALL", "Preise", ""
      Case Else
        With wks
          Blatt = .Range("B1")
          Set Bereich = .Range("C2:N2")
          For Each Zelle In BereichTabelle1.Visible

Gruß Uwe

[jules]

Moin Uwe,

also es funktioniert bei mir nicht richtig. Er ignoriert zwar so die hidden und veryhidden sheets, jedoch nicht "inhaltsverzeichnis" und "data" (beide auch visible)

Code:

For Each wks In Worksheets
 If wks.Visible = xlSheetVisible Then
   Select Case UCase(wks.Name)

Case "Inhaltsverzeichnis", "Data"

Case Else
          With wks
          Blatt = .Range("B1")

  Set Bereich = .Range("C2:N2")
  For Each Zelle In Bereich

Case "Inhaltsverzeichnis", "Data" wird also nicht ignoriert...
Es liegt denke ich am Ucase() ...

Deine Zeile: For Each Zelle In BereichTabelle1.Visible
Funktioniert nur ohne .visible (Tabelle1 habe ich nicht verstanden..)

Ansonsten prima!!

---

Zu guter Letzt ein ganz merkwürdiges Geschehen:

Code:

Dim arr As Variant
Dim arr2 As Variant
 
   arr = Worksheets("Data").Range("G8:G13")
   arr2 = Worksheets("Data").Range("H8:H13")

arr klappt. bei arr2 funktioniert es nur, wenn ich den Array im Makro selbst definiere 
arr2 = Array("Verbrauchsstatistik L1 2020.xlsb","Verbrauchsstatistik L2 2020.xlsb", "Verbrauchsstatistik L3 2020.xlsb")

In der Range (H8:H13) stehen aber genauso:
H8: Verbrauchsstatistik L1 2020.xlsb
H9: Verbrauchsstatistik L2 2020.xlsb
etc.

hä?

[Steffl]

Hallo,

also es funktioniert bei mir nicht richtig. Er ignoriert zwar so die hidden und veryhidden sheets, jedoch nicht "inhaltsverzeichnis" und "data" (beide auch visible)

Code:

For Each wks In Worksheets
 If wks.Visible = xlSheetVisible Then
   Select Case UCase(wks.Name)

Case "Inhaltsverzeichnis", "Data"

Case Else
          With wks
          Blatt = .Range("B1")

  Set Bereich = .Range("C2:N2")
  For Each Zelle In Bereich

Case "Inhaltsverzeichnis", "Data" wird also nicht ignoriert...
Es liegt denke ich am Ucase() ...

Nein, am UCase liegt es nicht. Data und Inhaltsverzeichnis liegen im separaten Case-Zweig und nicht im Case Else-Zweig. Schau dir doch das Ganze mal im Einzelschrittmodus an.