Microsoft Excel

Herbers Excel/VBA-Archiv

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

Benötige Hilfe für VBA-Schleife | Herbers Excel-Forum


Betrifft: Benötige Hilfe für VBA-Schleife von: Martin S.
Geschrieben am: 31.01.2010 16:49:35

Liebe Excel-Profis,

ich habe eine Liste mit zwei Spalten (Spalte 1 mit Festnetzvorwahlen und Spalte 2 mit den dazugehörigen Orten) in ein Array eingelesen:

Dim ArrVorwahlen As Variant
ArrVorwahlen = ThisWorkbook.Worksheets("Vorwahlen").Range("Vorwahlen").Value

Nun möchte ich gerne eine Function schreiben, über die ich die Ortsherkunft von Festnetznummer abfragen kann:
Sub test()
    MsgBox Ortsvorwahl("0301234567") 'Beispiel für Potsdam
End Sub
Public Function Ortsvorwahl(TelefonNr As String) As String
    'Hier sollte das Array durchsucht und der richtige Ort gefunden werden
    'Durchsucht wird  i = 1 to Ubound(ArrVorwahlen, 1)
    'Treffer: Ortsvorwahl = ArrVorwahlen(i, 2)
End Function

Da die Exceldatei mit den Ortsvorwahlen und Orten leider größer als 300 KB ist, konnte ich diese nur als Textdatei (im CSV-Format) hochladen: https://www.herber.de/bbs/user/67661.txt

Ich befürchte, dass hier eine in sich verschachtelte und/oder rekursive Schleife/Funktion geschrieben werden muss, da die Vorwahlen ja unterschiedlich lang sind und daher nicht so einfach verglichen werden können. Daher wäre ich für Hilfe sehr dankbar!

LG Martin

  

Betrifft: AW: Benötige Hilfe für VBA-Schleife von: Josef Ehrensberger
Geschrieben am: 31.01.2010 16:57:59

Hallo Martin,


wozu für eine so leichte Aufgabe eine UDF?
Tabelle1

 ABCDEFG
10201Essen  Vorwahl:02054 
20202Wuppertal  Ort:Essen-Kettwig 
30203Duisburg     
402041Bottrop     
502043Gladbeck Westfalen     
602045Bottrop-Kirchhellen     
702051Velbert     
802052Velbert-Langenberg     
902053Velbert-Neviges     
1002054Essen-Kettwig     
1102056Heiligenhaus     
1202058Wülfrath     
1302064Dinslaken     
1402065Duisburg-Rheinhausen     
1502066Duisburg-Homberg     
160208Oberhausen     
170209Gelsenkirchen     
1802102Ratingen     
1902103Hilden     
2002104Mettmann     
210211Düsseldorf     
220212Solingen     
2302129Haan Rheinl     
2402131Neuss     
2502132Meerbusch-Büderich     

Formeln der Tabelle
ZelleFormel
F2=INDEX(B:B;VERGLEICH(F1;A:A;0))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4




Gruß Sepp



  

Betrifft: AW: Benötige Hilfe für VBA-Schleife von: Martin S.
Geschrieben am: 31.01.2010 17:24:14

Hallo Josef,

vielen Dank für Deine Hilfe. Das Problem ist, dass mir die Nummern nur am Stück (ohne Trennung der Vorwahl von der Festnetznummer) vorliegen. Hätte ich die Vorwahlen separat zu den Telefonnummern, wäre das alles sogar für mich kein Problem gewesen.
Mir ist eine Makrolösung lieber, da ich andere Daten auch über eine UserForm verwalte und die dazugehörigen Orte in der UF anzeigen lassen möchte.
Ich bin dir für deine Hilfsbereitschaft trotzdem sehr dankbar!

LG Martin


  

Betrifft: AW: Benötige Hilfe für VBA-Schleife von: Josef Ehrensberger
Geschrieben am: 31.01.2010 17:31:09

Hallo Martin,


du schreibst "habe eine Liste mit zwei Spalten", was stimmt den nun ?



Gruß Sepp



  

Betrifft: AW: Benötige Hilfe für VBA-Schleife von: Martin S.
Geschrieben am: 31.01.2010 17:41:25

Hallo,

ich hatte gehofft, dass ich mich verständlich ausgedrückt hätte. Mit "Spalte 1 mit Festnetzvorwahlen und Spalte 2 mit den dazugehörigen Orten" habe ich mich auf die Quelle bezogen, aus denen ich die Orte "zurückverfolgen" kann. Aber die Festnetznummern selbst liegen mir leider (wie in dem Beispiel für Potsdam mir Tel.-Nr. "0301234567") nur am Stück vor.

LG Grüße


  

Betrifft: AW: Benötige Hilfe für VBA-Schleife von: Josef Ehrensberger
Geschrieben am: 31.01.2010 18:48:49

Hallo Martin,



Gruß Sepp



  

Betrifft: Tausend Dank, das läuft super! von: Martin S.
Geschrieben am: 31.01.2010 19:41:01

Hallo Josef,

vielen lieben Dank für Deine Mühe, das läuft super! Du hast mir wirklich sehr geholfen!
Ich wünsche Dir noch einen schönen Abend!

Viele Grüße

Martin


  

Betrifft: und falls es doch mit Makro sein soll... von: MichaV
Geschrieben am: 31.01.2010 17:11:59

..dann vielleicht so:

Option Explicit

Sub test()

Dim ArrVorwahlen
Dim ArrOrte
Dim i As Long

ArrVorwahlen = WorksheetFunction.Transpose(Range("Vorwahlen").Value)
ArrOrte = WorksheetFunction.Transpose(Range("Orte").Value)

i = WorksheetFunction.Match("030", ArrVorwahlen, 0)
MsgBox ArrOrte(i)

End Sub
Gruss Micha


  

Betrifft: AW: und falls es doch mit Makro sein soll... von: MichaV
Geschrieben am: 31.01.2010 17:31:33

...für zusammengesetzte Nummern (gem. Deiner Antwort an Sepp)

i = WorksheetFunction.Match("03317473221", ArrVorwahlen, 1)  '<--nur eine kleine Änderung

Gruss- Micha


  

Betrifft: Sorry, aber ich komme trotzdem nicht weiter... von: Martin S.
Geschrieben am: 31.01.2010 18:06:15

Hallo Micha,

vielen Dank für deine Geduld & Hilfsbereitschaft. Vermutlich mache ich einen dummen Fehler, denn ich bekomme es trotzdem nicht hin. Bitte sieh dir mal meinen Code an:

Public Function Ortsvorwahl(TelefonNr As String) As String
    Dim ArrVorwahlen As Variant
    Dim i As Integer
    
    ArrVorwahlen = ThisWorkbook.Worksheets("Vorwahlen").Range("Vorwahlen").Value
    
    i = WorksheetFunction.Match(TelefonNr, ArrVorwahlen, 1)
    Ortsvorwahl = ArrVorwahlen(i, 2)
    
End Function


Sub test()
    MsgBox Ortsvorwahl("03451234567")
End Sub
LG Martin


  

Betrifft: AW: Sorry, aber ich komme trotzdem nicht weiter... von: MichaV
Geschrieben am: 31.01.2010 18:09:30

öhm, Du hast aber schon meine erste Antwort gelesen, oder? Nimm den ganzen Code und ändere die eine Zeile. Ich bin davon ausgegangen dass Du in einer Spalte alle Vorwahlen und in der anderen Spalte die dazugehörigen Orte hast.


  

Betrifft: Vorwahl ist von Rufnummer NICHT getrennt von: Martin S.
Geschrieben am: 31.01.2010 17:36:03

Hallo Micha,

vielen lieben Dank für deine Makro-Lösung. Leider geht deine Lösung nicht, da ich aus der Festnetznummer nicht die Trennung von der Vorwahl und der Rufnummer vorliegen habe. Da Vorwahlen und Rufnummern unterschiedlich lang sein können, müssen die Zahlen (vermutlich einzeln) von links nach rechts verglichen werden. Dass das Array bereits nach Vorwahlen sortiert ist macht es eventuell einfacher.

LG Martin


  

Betrifft: woher soll Excel denn die Vorwahl kennen ? von: Matthias L
Geschrieben am: 31.01.2010 18:58:27

Hallo

Habe diesen Beitrag auch verfolgt.

aber:
woher soll Excel denn die Vorwahl kennen ?
woher soll Excel denn die Länge der Vorwahl kennen ?

Ob es Dir hilft weiß icht nicht, aber ein Versuch ist es ja wert.
Die Profis schmunzeln jetzt sicher, aber egal ;o)

Ich denke also, Du brauchst unbedingt eine Liste aller Ortsvorwahlen
Ob das dann aber auch zuverlässig funktioniert, habe ich nicht getestet.





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

Gruß Matthias


  

Betrifft: AW: woher soll Excel denn die Vorwahl kennen ? von: MichaV
Geschrieben am: 31.01.2010 19:22:28

hehe, woher kennt denn Dein Telefon die Vorwahlen? Drückst Du immer auf die Leertaste wenn Du die Vorwahl eingegeben hast?

Sachtmal, hat eigentlich irgendjemand mein (bzw. Sepps) Vorschlag ausprobiert?

Gruss- Micha


  

Betrifft: Problem gelöst! von: Martin S.
Geschrieben am: 31.01.2010 19:53:00

Hallo,

ich bedanke mich bei allen, die versucht haben mir zu helfen.
Sepp hat mein Problem perfekt gelöst: https://www.herber.de/forum/messages/1134652.html

@Matthias: Die Liste mit den Ortsvorwahlen hatte ich doch bereits in meine Fragestellung als CSV-Datei (im Textformat) hochgeladen gehabt. Ich wusste leider nicht, wie ich die - unterschiedlich langen Vorwahlen - mit der Telefonnummer abgleiche, da mir von der Festnetznummer keine Trennung von Vorwahl und Rufnummer vorliegt.

Allen noch einen schönen "Rest-Sonntag"!

Martin