Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1720to1724
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Straße/Hausnr. von Excelformeln.de mit VBA

Straße/Hausnr. von Excelformeln.de mit VBA
13.11.2019 08:20:13
Excelformeln.de
Guten Morgen,
ich habe öfter mit Adressdaten zu tun und muss auch immer wieder Straße / Hausnummer trennen.
Die beste Lösung ist: http://www.excelformeln.de/formeln.html?welcher=350
Allerdings bräuchte ich es als kleines VBA Tool, das ich jederzeit flexibel einsetzen kann.
Vorarbeiten incl. GUI sind geleistet, aber zu mehr fehlt es mir an VBA Wissen.
- Wenn die Str./Hausnr. nicht in Spalte A steht, erscheint #NV als Fehler, weil die aufgezeichnete Formel nicht für Spalte F oder G... ausgelegt ist
- Es müsste noch der Cursor entspr. GUI positioniert werden, damit 2 Spalten eingefügt werden können.
- Start Zeile entspr. GUI Eingabe
- Schleife bis zum Ende…fertig
Das ist zwar jede Menge an todos, aber für euch bestimmt kein großer Aufwand.
Ich hoffe, dass mir jemand helfen kann.
Anbei eine kleine Testdatei: https://www.herber.de/bbs/user/133158.xlsm
Danke schon mal im Voraus
Erwin

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Formel Korrektur (ohne VBA)
13.11.2019 08:27:58
Klaus
Hallo Erwin,
dieser Teil der Originalformel von Excelformeln.de
SPALTE(A1:Z1)
zählt von 1 bis 26 (Spalte A=1, Spalte B=2 und so weiter). Dies darfst du NICHT an deine Bereich anpassen! Wenn du in deiner Musterdatei die korrekte Formel
=LINKS(F5;LÄNGE(F5)-VERWEIS(2;1/LINKS(RECHTS(F5&1;SPALTE(A1:Z1)))/ISTFEHLER(SUCHEN(".";RECHTS(F5&0; SPALTE(A1:Z1))));SPALTE(A1:Z1)-1))
eingibst, funktioniert es auch auf Spalte F.
Die Formel einmal Makrorekordern und in VBA weiter machen.
LG,
Klaus
ich lass mal offen, falls jemand Bock hat den VBA Teil zu übernehmen.
Anzeige
AW: instrRev
13.11.2019 08:31:10
Fennek
Hallo,
es macht keinen Sinn solche komplexen Excel-Formeln 1:1 in VBA zu übersetzen.
Die Position des letzten Leerzeichens kann man mit 'pos = instrRev(cells(1,1), " ")' ermitteln und damit den String trennen.
mfg
(welche Koordinaten sind kodiert: IbAjPaSbk3ePRHtS6hz5DWtEIsxudnHPtMNhDhxK/Zs79O84/d8/EiESmCX3O4x+iGBozFVQ86iLJKWIiqy1KTolDvSi8Vf8OjM1i1ZxWJ7MAGn8olQ11OJy4VLUlVZ0Z6pgzsrdXPmjjZOZTCI/60HwxqUaMIFhOJ4P7vT/u+ZioL1SuhHsL7f0GlbGbnCH4AskcBE27qcvInPsX4CsXdnyGPJJwpXkYbNZEPf+lE/Jz3lsCvpWvKMfevzMUjJ)
AW: instrRev
13.11.2019 08:41:27
Erwin
Hallo Klaus, hallo Fennek,
danke für eure Rückmeldungen.
Für die Länge der Formel kann ich nichts, das ist in jedem Fall die Beste, die ich getestet habe.
Ich würde das ganze schon gerne in VBA haben, da ich gerade was Adressen betrifft, viel zu tun habe.
Da Straße und Hausnummer öfter in verschiedenen Spalten stehen müsste es eben flexibel handhabbar sein, wobei ich den Hinweis von Klaus jetzt verstanden habe.
Damit fehlt nur noch der VBA Teil mit der bereits vorhandenen GUI, also Start und Schleife bis zum Ende.
Grüße - Erwin
Anzeige
AW: instrRev
13.11.2019 08:44:03
Klaus
Damit fehlt nur noch der VBA Teil mit der bereits vorhandenen GUI, also Start und Schleife bis zum Ende.
Die Spalte 1-26 einfach mit $ fixieren. Die Formel einmal Makrorekordern. Fertig.
=LINKS(H54;LÄNGE(H54)-VERWEIS(2;1/LINKS(RECHTS(H54&1;SPALTE($A$1:$Z$1)))/ISTFEHLER(SUCHEN("."; RECHTS(H54&0;SPALTE($A$1:$Z$1))));
Getestet mit einer Straße in Spalte H.
LG,
Klaus
AW: instrRev
13.11.2019 08:48:51
Erwin
Hallo Klaus,
das habe ich schon verstanden, aber das ist ja nur eine Zeile.
Aber welchen Code brauche ich damit die Schleife bis zum Ende aller Straßennamen durchläuft (ich will ja dann nicht die eingesetzte Formel mit der Hand runterziehen).
Der Code muss ja prüfen, wie viele Zeilen sind mit Straßennamen belegt, ...
Grüße - Erwin
Anzeige
AW: instrRev
13.11.2019 08:52:51
Klaus
Folgendes Makro schreibt die Formel in alle Zellen von H1 bis H100, davon ausgehend dass die Straßennahmen in G1:G100 stehen:
Sub Makro1()
Dim i As Long
For i = 1 To 100
Cells(i, 8).FormulaR1C1 = _
"=LEFT(RC[-1],LEN(RC[-1])-LOOKUP(2,1/LEFT(RIGHT(RC[-1]&1,COLUMN(R1C1:R1C26)))/ISERROR( _
SEARCH(""."",RIGHT(RC[-1]&0,COLUMN(R1C1:R1C26)))),COLUMN(R1C1:R1C26)-1))"
Next i
End Sub
bekommst du das selber auf deine Datei abgewandelt?
LG,
Klaus
AW: instrRev
13.11.2019 08:59:00
Klaus
Habs mal kurz fertig gebaut ....
Private Sub CommandButton1_Click()                  'OK Button
'Straße - Hausnummer trennen
'http://www.excelformeln.de/formeln.html?welcher=350
'B1-Optimierung von Franz Pölt (also ohne obige Einschränkungen):
'Straße abtrennen: =LINKS(A1;LÄNGE(A1)-VERWEIS(2;1/LINKS(RECHTS(A1&1;SPALTE(A1:Z1)))/ISTFEHLER( _
SUCHEN(".";RECHTS(A1&0;SPALTE(A1:Z1))));SPALTE(A1:Z1)-1))
'Hausnummer gesondert: =GLÄTTEN(WECHSELN(A1;B1;))
Dim i As Long
Dim mySpalte As Long
Dim MyZeile As Long
'Spalte und Zeile aus Userform feststellen
mySpalte = TextBox1.Value
MyZeile = TextBox2.Value
'Aufruf Formular Straße_Hausnummer
'Was soll das? Keine Ahnung, darum lasse ich es stehen
ActiveCell.Offset(0, 1).Select
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
'Straßenname separieren
lrow = Cells(Rows.Count, mySpalte).End(xlUp).Row    'letzte Zeile feststellen
For i = MyZeile To lrow     'von gewählter bis letzter Zeile durchschleifen
'MySpalte + 1 = eine Spalte rechts von der gewählten, Formel rekordert.
Cells(i, mySpalte + 1).FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-LOOKUP(2,1/LEFT(RIGHT(RC[- _
1]&1,COLUMN(R1C1:R1C26)))/ISERROR(SEARCH(""."",RIGHT(RC[-1]&0,COLUMN(R1C1:R1C26)))),COLUMN(R1C1:R1C26)-1))"
'ab hier Hausnummer / MySpalte + 2 für 2 Spalten rechts von der gewählten
Cells(i, mySpalte + 2).FormulaR1C1 = "=TRIM(SUBSTITUTE(RC[-2],RC[-1],))"
Next i
End Sub

Anzeige
AW: instrRev
13.11.2019 09:17:29
Erwin
Hallo Klaus,
vielen Dank (...hab's mal kurz fertig gebaut klingt bei euch immer so leicht / ohne Probleme).
Nach der Eingabe der Spalte (Buchstabe g) und der Zeile (Ziffer 5) in die GUI erscheint bei der Codezeile
mySpalte = Textbox1.Value noch der Laufzeitfehler 13 - Typen unverträglich, da passt etwas noch nicht.
Was muss da noch angepasst werden?
Grüße - Erwin
AW: instrRev
13.11.2019 09:27:51
Klaus
mySpalte = Textbox1.Value noch der Laufzeitfehler 13 - Typen unverträglich
Dann steht in TextBox1 etwas drin, was da nicht stehen darf. mySpalte ist als "long" definiert, du musst also eine Zahl rein schreiben - Spalte A = 1, Spalte B = 2 usw.
Was hast du denn rein geschrieben? H?
LG,
Klaus
Anzeige
AW: instrRev
13.11.2019 09:36:26
Klaus
Achso, du hast ja geschrieben du schreibst den Spaltenbuchstaben "G". Hab ich erst jetzt gelesen. Quick and Dirty:
'Spalte und Zeile aus Userform feststellen
mySpalte = Range(TextBox1.Value & "1").Column
MyZeile = TextBox2.Value

LG,
Klaus M.
Vielen herzlichen Dank
13.11.2019 09:44:55
Erwin
Hallo Klaus und Helfer,
vielen Dank für eure Geduld und natürlich für die geniale Hilfe.
Wieder ein kleiner Baustein zur Vereinfachung der Arbeit.
Grüße - Erwin
Danke für die Rückmeldung! owT.
13.11.2019 09:46:46
Klaus
.
Gerne u. Danke für die Rückmeldung. o.w.T.
13.11.2019 10:48:50
Werner
AW: instrRev
13.11.2019 09:35:48
Werner
Hallo Erwin,
wenn du in der Textbox die Spalte als Buchstabe eingeben willst, dann mußt du im Code den Spaltenbuchstaben in eine Zahl umwandeln.
1. Ändere folgende Codezeile
Dim mySpalte As Long

um in
Dim mySpalte As Variant

2. Unter dieser Codezeile
mySpalte = TextBox1.Value

noch zusätzlich diese Codezeile einfügen
mySpalte = Columns(mySpalte).Column
Gruß Werner
Anzeige

67 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige