Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Zahl finden | Herbers Excel-Forum


Betrifft: Zahl finden von: alifa
Geschrieben am: 08.12.2009 19:35:51

Hallo,
in Spalte A1 bis A68 befinden sich achtundsechzig 4-stellige Zahlen. Wie kann ich die Zahl finden, die wenigstens eine Ziffer mit allen anderen(67) gemeinsam hat? Z.B. von den Zahlen 1234, 2345, 4567, 8964, 9999, 8888, 7890 würde die 8964 entsprechen. Mir fällt da nichts Vernünftiges ein.
Gruß, Erhard

  

Betrifft: mit VBA von: Tino
Geschrieben am: 08.12.2009 20:07:51

Hallo,
habe was mit VBA zusammengebastelt.
In ein Modul kommt dieser Code

Function CheckZahlen(rngBereich As Range, SollAnzahl As Long)
Dim meAr()
Dim A&, AA&, tmpCounter&

meAr = rngBereich.Value2

For A = 0 To 9
 For AA = 1 To UBound(meAr)
  If InStr(CStr(meAr(AA, 1)), CStr(A)) > 0 Then tmpCounter = tmpCounter + 1
  If tmpCounter >= SollAnzahl Then CheckZahlen = meAr(AA, 1): Exit Function
 Next AA
  tmpCounter = 0
Next A

CheckZahlen = "nichts gefunden"
End Function
In einer Zelle verwendest Du diese Formel.
=CheckZahlen(A1:A68;68)
A1:A68 ist der Bereich und 68 die Anzahl Übereinstimmungen die gesucht werden sollen.

Mit einer Excelformel kann ich Dir nicht helfen.

Gruß Tino


  

Betrifft: AW: mit VBA von: alifa
Geschrieben am: 09.12.2009 09:41:50

Hallo Tino,
Habe eine Prozedur zum Aufrufen Deiner Function erstellt. Es erscheint die Meldung: Laufzeitfehler 91.
Objektvariable oder With-Block Variable nicht festgelegt. Beim Debuggen wird die Zeile: meAr=rngBereich.Value2 angemeckert.
Gruß, Erhard


  

Betrifft: AW: mit VBA von: Tino
Geschrieben am: 09.12.2009 15:09:27

Hallo,
habe mir nochmal Deine Frage durchgelesen, hatte Sie falsch verstanden.

Hier meine Version als Beispiel, kannst ja mal testen.

https://www.herber.de/bbs/user/66460.xls

Gruß Tino


  

Betrifft: stimmt nicht von: Erich G.
Geschrieben am: 09.12.2009 18:41:38

Hi Tino,
deine Fkt. liefert, nachdem ich in A4 den Wert 8966 geschrieben habe,
eben diesen Wert als Lösung. Ist aber falsch - hat keine Ziffer gemeinsam mit 1234.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: stimmt mehrfach vorkommen einer Zahl von: Tino
Geschrieben am: 09.12.2009 19:03:15

Hallo,
habe wohl nicht daran gedacht, dass auch Zahlen in einer Zahl doppelt sein können.
So müsste es funktionieren.

Function CheckZahlen(rngBereich As Range, SollAnzahl As Long)
 Dim meAr(), MerkZahlen(1 To 4)
 Dim A&, AA&, AAA&, tmpCounter&
 Dim strZahl As String
 meAr = rngBereich.Value2
 
 For A = Lbound(meAr) To Ubound(meAr)
    For AA = 1 To Len(CStr(meAr(A, 1)))
      strZahl = Mid$(meAr(AA, 1), AA, 1)
       If Not IsNumeric(Application.Match(strZahl, MerkZahlen, 0)) Then
          MerkZahlen(AA) = strZahl
          
          For AAA = 1 To Ubound(meAr)
              If InStr(CStr(meAr(AAA, 1)), strZahl) > 0 Then tmpCounter = tmpCounter + 1
              If tmpCounter >= SollAnzahl Then CheckZahlen = meAr(AA, 1): Exit Function
              If ((tmpCounter + (Ubound(meAr) - AAA)) * 4) < SollAnzahl Then Exit For
          Next AAA
        End If
    Next AA
    Erase MerkZahlen
    tmpCounter = 0
 Next A
 
CheckZahlen = "nichts gefunden"
End Function
Gruß Tino


  

Betrifft: stimmt auch nicht von: Erich G.
Geschrieben am: 09.12.2009 19:21:10

Hi Tino,
mit 8699 in A4 bringt dein neuer Code "nichts gefunden".

Bei 8694 in A4 bringt er das richtige Ergebnis 8964,
das auch nach Eintrag der 8966 in A3 noch genauso richtig ist.

Deinen Code habe ich nicht verstanden.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: weil keine 68 Treffer. von: Tino
Geschrieben am: 09.12.2009 19:31:54

Hallo,
nicht gefunden weil keine 68 Zahlen zusammenkommen.

In Zelle A1 steht 1234 und 8, 9 oder 6 nicht darin vorkommt,
also kann ich bei insgesamt 68 Zahlen keine 68 Treffer mehr bekommen,
weil die Zahl in A1 schon rausfällt.

Oder ich habe die Frage komplett falsch verstanden.

Gruß Tino


  

Betrifft: @Erich: Deiner aber auch nicht, gleiche Falle? von: Tino
Geschrieben am: 09.12.2009 19:13:42

Hallo,
habe Deinen Code auch mal bei mir getestet,
dieses bringt wenn ich in Zelle A4 8966 eintrage als Treffer 8964, kann eigentlich auch nicht sein?
Bestimmt auch wegen zweimal vorkommender 6 in der Zahl 8966.

Gruß Tino


  

Betrifft: @Tino: nicht gleiche Falle 8964 ist richtig! (owT) von: Erich G.
Geschrieben am: 09.12.2009 19:23:04




  

Betrifft: Egal irgendwas wird schon passen oT. von: Tino
Geschrieben am: 09.12.2009 19:38:07




  

Betrifft: AW: Zahl finden von: werni
Geschrieben am: 08.12.2009 21:02:19

Hi Erhard

mit Formel
https://www.herber.de/bbs/user/66445.htm

Gruss Werner


  

Betrifft: Zahl finden von: Erich G.
Geschrieben am: 09.12.2009 01:10:35

Hi Erhard,
das braucht einigen Platz, funzt aber:

 ABCDEFGHIJ
1123441111000 
2234541111000 
3456751111001 
4468971111111 
5999930001101 
6888830001011 
7789050011111 
8          
9         4689

Formeln der Tabelle
ZelleFormel
B1=SUMME(C1:I1)
C1{=--(SUMME(--ISTZAHL(FINDEN(TEIL(INDEX($A$1:$A$999;SPALTE()-2); SPALTE($A1:$D1); 1); $A1)))>0)}
J9=INDEX(A:A;VERGLEICH(ANZAHL(A:A); B:B;0))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

B1 kannst du nach unten, C1 nach rechts und unten kopieren.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Zahl finden von: alifa
Geschrieben am: 09.12.2009 09:04:02

Hallo Erich,
wie kopiert man nach rechts? (C1) Was bedeuten die 7 Zahlen (1,0)? Geht es nicht einfacher mit VBA?
Zwar gibt es unter den Antworten hier ein Makro, doch leider klappt das mit meinen Zahlen (noch) nicht.
Gruß, Erhard


  

Betrifft: Formel kopieren, VBA-Lösung von: Erich G.
Geschrieben am: 09.12.2009 10:00:40

Hi Erhard,
"wie kopiert man nach rechts? (C1)"
Formel in C1 schreiben, C1 auswählen (markieren), Strg+C, D1:I1 markieren, Eingabe/Enter/Return drücken.

Die Formel wird bis in Spalte I gebraucht, weil sie dann in 7 Spalten steht - entsprechend den 7 Zeilen in Spalte A.

Danach kannst du C1:I1 markieren, Strg+C, C2:I7 markieren, Eingabe/Enter/Return drücken -
das kopiert die Formel dann nach unten.

In den Zellen C1:I7 steht also immer die selbe Formel.
Ergebnis 0 in C1 bedeutet: in der 1. Zahl in Spalte A kommt mindestens eine Ziffer der Zahl in A1 vor.
Ergebnis 0 in D1 bedeutet: in der 2. Zahl in Spalte A kommt mindestens eine Ziffer der Zahl in A1 vor.
...
Ergebnis 0 in I1 bedeutet: in der 7. Zahl in Spalte A kommt mindestens eine Ziffer der Zahl in A1 vor.

Ob das in VBA einfacher geht? Was einfacher ist, richtet sich auch danach, wie fit man im jeweiligen Bereich ist.
Für manche ist eine Matrixformel undurchschaubar, für andere ein VBA-Code.

Tinos Code berechnet wohl nicht das was du möchtest.
Hier ein anderer Vorschlag:

Sub tst()
   Dim lngZ As Long, zz As Long, cc As Long, ss As Integer

   lngZ = Cells(Rows.Count, 1).End(xlUp).Row
   For zz = 1 To lngZ
      For cc = 1 To lngZ
         For ss = 1 To 4
            If InStr(1, Cells(cc, 1), Mid(Cells(zz, 1), ss, 1)) > 0 Then Exit For
         Next ss
         If ss > 4 Then Exit For
      Next cc
      If cc > lngZ Then
         MsgBox "Treffer: " & Cells(zz, 1)
         Exit Sub
      End If
   Next zz
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Formel kopieren, VBA-Lösung von: alifa
Geschrieben am: 09.12.2009 14:01:41

Erich,
natürlich ist das Makro die elegantere Lösung! Das ist genau das, was ich suchte. Danke!!
Wenn diese 68 Zahlen über dieses Makro erzielt wären:

Sub DieSzahl()
Dim A%, z%
For A = 1000 To 9999
If A ^ 0.5 = Int(A ^ 0.5) Then
Cells(z + 1, 1) = A
z = z + 1
End If
Next
End Sub

wie würde Dein Makro dann lauten? Ich habe da Einiges versucht, leider ohne Erfolg. Eine kurze Erklärung wäre nett!
Gruß, Erhard


  

Betrifft: Quadratzahlen von: Erich G.
Geschrieben am: 09.12.2009 18:52:03

Hi Erhard,
die Quadratzahlen bekommst du leichter damit in Spalte A:

Sub Quadrate()
   Dim A%, z%
   For A = Application.RoundUp(Sqr(1000), 0) To Int(Sqr(9999))
      z = z + 1
      Cells(z, 1) = A * A
   Next
End Sub
An meinem Makro tst() muss sich überhaupt nichts ändern.
Es liefert bei den Quadratzahlen das Ergebnis 6241. Einfach so.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: Quadratzahlen von: alifa
Geschrieben am: 09.12.2009 20:17:03

Hallo Erich,
ich meinte ein Makro, das ohne Excel Tabelle auskommt. Also intern diese Werte speichert und die richtige Zahl findet. Interessant Dein Makro "Quadrate". Es müsste jetzt weiter gehen ohne ...Cells(z + 1....
irgendwie mit InString...
Gruß, Erhard


  

Betrifft: Quadratzahlen auswerten ohne Tabelle von: Erich G.
Geschrieben am: 09.12.2009 23:41:36

Hi Erhard,
so werden die Quadratzahlen nur in eine Arrayvariable geschrieben und ausgewertet,
ohne Tabelle:

Sub tst2()
   Dim aStr() As String, lngZ As Long, zz As Long, cc As Long, ss As Integer
   '                                                           Array erzeugen
   lngZ = Int(Sqr(9999)) + 1 - Application.RoundUp(Sqr(1000), 0)
   ReDim aStr(1 To lngZ)
   For cc = Application.RoundUp(Sqr(1000), 0) To Int(Sqr(9999))
      zz = zz + 1
      aStr(zz) = CStr(cc * cc)
   Next cc
   '                                                           Array auswerten
   For zz = 1 To lngZ
      For cc = 1 To lngZ
         For ss = 1 To 4
            If InStr(1, aStr(cc), Mid(aStr(zz), ss, 1)) > 0 Then Exit For
         Next ss
         If ss > 4 Then Exit For
      Next cc
      If cc > lngZ Then
         MsgBox "Treffer: " & aStr(zz)
         Exit Sub
      End If
   Next zz
   MsgBox "Kein Treffer"
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: Quadratzahlen auswerten ohne Tabelle 2. von: Erich G.
Geschrieben am: 10.12.2009 00:05:48

Hi Erhard,
bei dieser Version kannst du die Anzahl Stellen leicht variieren,
hier beispielsweise mit 7stelligen Quadratzahlen:

Sub tst3()
   Dim lngV As Long, lngB As Long
   Dim aStr() As String, lngZ As Long, zz As Long, cc As Long, ss As Integer

   Const lngA As Long = 7
   '                                                        von/bis berechnen
   lngV = Application.RoundUp(Sqr(10 ^ (lngA - 1)), 0)
   lngB = Int(Sqr(10 ^ lngA - 1))
   '                                                        Array füllen
   lngZ = lngB + 1 - lngV
   ReDim aStr(1 To lngZ)
   For cc = lngV To lngB
      zz = zz + 1
      aStr(zz) = CStr(cc * cc)
   Next cc
   '                                                        Array auswerten
   For zz = 1 To lngZ
      For cc = 1 To lngZ
         For ss = 1 To lngA
            If InStr(1, aStr(cc), Mid(aStr(zz), ss, 1)) > 0 Then Exit For
         Next ss
         If ss > lngA Then Exit For
      Next cc
      If cc > lngZ Then
         MsgBox "Treffer: " & aStr(zz)
         Exit Sub
      End If
   Next zz
   MsgBox "Kein Treffer"
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


Beiträge aus den Excel-Beispielen zum Thema "Zahl finden"