Thema: variable Zelladresse finden | Herbers Excel-Forum

Hallo zusammen,
Ich möchte, dass untere Makro das gefundene Ergebnis (in diesem Fall "RIC Aktie" aus dem Datenblatt 2) nicht ins Datenblatt 1 in C8 einträgt wie das Makro unten anzeigt, sondern: Das Makro soll in Tabellenblatt 1 in Spalte C die lezte mit Text befüllte Zeile suchen und das gefundene Ergebnis in der nächsten leeren Zeile schreiben. Macht das für Euch Sinn? Ich tue mich selber ziemlich schwer damit.
Bsp.: Über die Inputbox wird der RIC (ALVG.DE) im Datenblatt 2 Spalte C gesucht. Dieser kann mehrmals vorkommen. Der gefundene RIC soll nicht ins Datenblatt 1 C8 eingetragen werden (da C8 bereits befüllt mit Text), sondern soll in Spalte C die letzte volle Zeile bzw. Zelle suchen und in der nächsten leeren eintragen. D.h. das Ergebnis wird immer in einer leeren Zelle eingetragen, die das Makro selber rausfinden muß. Ich kann ihr keine feste Zelladdresse vorgeben.
Mein PRoblem ist, das das untere Makro auf einer fixen Zelladressen (C8) aufbaut, diese aber eigentlich variabel sein soll. Die letzte befüllte Zelle soll in der Spalte C1:C100 gesucht werden.
Vielen Dank für die Hilfe.
Dani.
Sub Austausch_1_Bezugsrecht()
'Variablen definieren
Dim strRIC As String, rngSuch As Range
Dim rngF As Range, lngZ As Long, lngErst As Long, lngU As Long
' RIC wird gesucht in AlleBestände_alleFonds in Spalte C; Eingabe von RIC für das erste Bezugsrecht in A35, C8
strRIC = InputBox("Bitte RIC Aktie eingeben:", "Dateneingabe:")
With Workbooks("Template_alle_Kapitalmaßnahmen in Arbeit.xls")
Set rngSuch = .Worksheets(2).Columns(3)
Set rngF = rngSuch.Find(What:=strRIC, LookAt:=xlPart)
If Not rngF Is Nothing Then
lngErst = rngF.Row
Do
lngZ = rngF.Row
With .Worksheets(1)
.Range("c8") = strRIC
End With
Sheets(5).Range("B5").Value = strRIC
'alle Informationen zu dem jeweilligen RIC (ISIN, NAME, BESTAND) aus dem Datenblatt AlleBestände_alle Fonds werden
'kopiert in und in Datenblatt "KapMaßnahme" eingetragen
With .Worksheets(1)
.Cells(8 + lngU, 3) = strRIC ' c8
.Cells(8 + lngU, 1) = rngF.Offset(0, -2) ' a8
.Cells(8 + lngU, 2) = rngF.Offset(0, -1) ' b8
.Cells(8 + lngU, 4) = rngF.Offset(0, 1) ' d8
.Cells(8 + lngU, 5) = rngF.Offset(0, 2) ' e8
.Cells(8 + lngU, 6) = rngF.Offset(0, 3) ' f8
.Cells(8 + lngU, 17) = rngF.Offset(0, 7) ' n8
.Cells(8 + lngU, 12) = rngF.Offset(0, 5) ' l8
.Cells(8 + lngU, 20) = rngF.Offset(0, 13).FormulaR1C1 ' q8
.Cells(8 + lngU, 18) = rngF.Offset(0, 12).FormulaR1C1 ' q8
End With
lngU = lngU + 1
Set rngF = rngSuch.FindNext(rngF) ' ### korrigiert
Loop While Not rngF Is Nothing And rngF.Row <> lngErst
Else
MsgBox "Die Aktie ist nicht vorhanden bzw. die Eingabe wurde abgebrochen!", _
vbCritical, "Nur Zahlen eingeben!"
End If
End With

Hi Dani
Ersetz mal dein
.Range("c8") = strRIC
durch
.Range("c" & .Cells(Rows.Count, 3).End(xlUp).Row + 1) = strRIC
diese Zeile + 1 verwendet immer die nächste freie Zeile in der angegebenen Spalte (hier 3 = C).
Hilfts?
Wenn nicht, weiß ich leider auch nicht weiter
Ciao
Thorsten

Hallo Thorsten,
Du bist ein VBA - Genie :-). Klappt einwandfrei
Hab mir ergebnislos das Gehirn zermattert.
Brauche noch einmal kurz deine Hilfe.
Soll ich in den unteren Makrozeilen, dann die 8 ersetzen durch .Range("c" & .Cells(Rows.Count, 3).End(xlUp).Row + 1) = strRIC?
Das Problem ist der Verweis auf Zelle 8 durzieht sich durchs gesamte Makro.
Vielen Grüße
Dani
Cells(8 + lngU, 3) = strRIC ' c8
.Cells(8 + lngU, 1) = rngF.Offset(0, -2) ' a8

Hi Dani
versteh ich nich ganz
wenn lngU NICHT 0 ist, dann ist
Cells(8 + lngU, 3) = strRIC ' c8
auch nich C8
weil 8 + lngU immer größer als 8 ist (wenn lngU nich 0 is)
das Gleiche gilt auch für
.Cells(8 + lngU, 1) = rngF.Offset(0, -2) ' a8
Und ob du in den unteren Makrozeilen, dann die 8 ersetzen sollst durch die andere Code-Zeile, kann ich so nicht beantworten, weil (angenommen, nächste freie Zeile in C ist 8) ....
...der erste Befehl mit
.Range("c" & .Cells(Rows.Count, 3).End(xlUp).Row + 1) = strRIC
belegt Zelle C9
...der zweite Befehl mit
.Range("c" & .Cells(Rows.Count, 3).End(xlUp).Row + 1) = strRIC
belegt Zelle C10
wenn aber in deinem Code die zweite Wertzuweisung auch erst mal an C9 gehen soll, dann wäre die Verwendung im zweiten Befehl schon falsch.
Ich kenne deinen Code nicht.
Aber bitte poste nich deinen ganzen Code (zumindest nich für mich). Ich bin nicht gut im Code-Korrekturlesen.
Eine Bsp-Datei mit deinem Code, in der deine Frage nachvollziehbar ist, wäre glaube ich viel hilfreicher.
Ciao
Thorsten

ja, das Makro ist eigentlich noch viel viel länger, habe nur einen Teil gepostet, weil ich dachte das wäre hilfreich.
Habe mittlerweile schon eine Lsg. von Erich.
Aber trotzdem Danke, denn Du hast mir bereits sehr geholfen!!!!!
Viele Grüße
Dani

Hi Dani,
da hab ich mich doch ein wenig erinnert...
Jetzt muss es aber etwas anders aussehen. Probier mal

Option Explicit
Sub Austausch_1_Bezugsrecht()
Dim strRIC As String, rngSuch As Range                         'Variablen deklarieren
Dim rngF As Range, lngZ As Long, lngErst As Long
' RIC wird gesucht in AlleBestände_alleFonds in Spalte C;
' Eingabe von RIC für das erste Bezugsrecht in A35, C8
strRIC = InputBox("Bitte RIC Aktie eingeben:", "Dateneingabe:")
With Workbooks("Template_alle_Kapitalmaßnahmen in Arbeit.xls")
Set rngSuch = .Worksheets(2).Columns(3)
Set rngF = rngSuch.Find(What:=strRIC, LookAt:=xlPart)
If Not rngF Is Nothing Then
lngErst = rngF.Row
With .Worksheets(1)
lngZ = .Cells(.Rows.Count, 3).End(xlUp).Row  'letzte in Sp.C belegte Zeile
Do
Sheets(5).Range("B5").Value = strRIC ' Soll das noch so sein?
'alle Informationen zu dem jeweilligen RIC (ISIN, NAME, BESTAND)
'aus dem Datenblatt AlleBestände_alle Fonds werden
'kopiert in und in Datenblatt "KapMaßnahme" eingetragen
lngZ = lngZ + 1            ' nächste (leere) Zeile
.Cells(lngZ, 1) = rngF.Offset(0, -2)               ' a
.Cells(lngZ, 2) = rngF.Offset(0, -1)               ' b
.Cells(lngZ, 3) = strRIC                           ' C
.Cells(lngZ, 4) = rngF.Offset(0, 1)                ' d
.Cells(lngZ, 5) = rngF.Offset(0, 2)                ' e
.Cells(lngZ, 6) = rngF.Offset(0, 3)                ' f
.Cells(lngZ, 17) = rngF.Offset(0, 7)               ' Q
.Cells(lngZ, 12) = rngF.Offset(0, 5)               ' L
.Cells(lngZ, 20) = rngF.Offset(0, 13).FormulaR1C1  ' T
.Cells(lngZ, 18) = rngF.Offset(0, 12).FormulaR1C1  ' R
Set rngF = rngSuch.FindNext(rngF)
Loop While Not rngF Is Nothing And rngF.Row <> lngErst
End With
Else
MsgBox "Die Aktie ist nicht vorhanden bzw. die Eingabe wurde abgebrochen!", _
vbCritical, "Nur Zahlen eingeben!"
End If
End With
End Sub

Noch ein Tipp:
Wenn du hier im Forum Code postest, kannst du ganz gut dafür sorgen, dass Einrückungen usw. sichtbar bleiben.
Du musst nur vor die erste Codezeile ein <pre> und hinter die letzte ein </pre> schreiben.
Du kannst es einfach ausprobieren - es gibt ja die Vorschau.
Dabei ist wichtig: Wenn du eine pre-Klammer aufmachst, solltest du sie später wieder mit /pre schließen,
jeweils mit den spitzen Klammer drumherum.
Bei diesem Post beispielsweise steht bei mir oben (beim Beginn des Codes) folgender Text, einfach hintereinander:
Probier mal<pre>Option Explicit
Das siehst du auch, wenn du dir im Firefox (oder auch IE oder ...) den Quelltext ansiehst.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Erich, das ist echt der Hammer. Funktioniert super. Keine einzige Fehlermeldung. Danke! Danke! Danke!
Viele Grüße
Dani

Hallo Erich,
habe noch eine Frage zu dem Problem:
dabei handelt es sich um das gleiche Problem wie oben. Obiger Code geht wie folgt weiter...Muß den Verweis auf F8 loswerden in For Each Zelle In Range("F8:F40") da wieder letzte volle Zelle benötigt.
Vielen Dank.
dani
Worksheets(1).Range("b35").FormulaR1C1 = Worksheets(4).Range("b3").FormulaR1C1
specialdividend = Application.InputBox("Bitte Betrag der special Dividend oder Capital Return eingeben:", "Dateneingabe:", , , , , , 1)
If specialdividend = False Then Exit Sub
For Each Zelle In Range("F8:F40") (HIER ist VERWEIS WIEDER AUF F8!!!!), aber cells(lngZ, 6) klappt nicht
If Zelle <> "" Then Zelle.Offset(0, 15) = specialdividend
Next Zelle

Hi Dani,
deine Frage ist mir nicht klar. Auch die Code-Bruchstücke helfen da nicht weiter.
lngZ wird bisher berechnet als die Nummer der jeweils letzten belegten Zeile in Spalte C.
Jetzt willst du "F8 loswerden in For Each Zelle In Range("F8:F40") da wieder letzte volle Zelle benötigt."
Geht es um die letzte volle Zelle in Spalte F? Deren Zeile kann kleiner als 8 sein,
zwischen 8 und 40 liegen oder auch größer als 40 sein.
Von wo bis wo genau soll die Schleife laufen,
in welche Zeilen soll der Wert von specialdividend geschrieben werden? Bis in Zeile 40?
Soll das Ganze passieren, nachdem die Find-Schleife (suche nach strRIC) komplett abgeschlossen ist?
Oder soll das innerhalb dieser Schleife sein?
Welche Tabellenblätter sind hier eigentlich beteiligt?
Was hat die Zeile

Worksheets(1).Range("b35").FormulaR1C1 = Worksheets(4).Range("b3").FormulaR1C1

mit dem Problem zu tun?
Sinnvoll wäre hier sicher eine Beispielmappe.
Noch eins:
Hast du meine Tipp zur Codedarstellung hier im Forum nicht wahrgenommen?
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich,
unten ein Beispiel mit dem Problem.
1. Durchlauf Makro
mein Problem ist: 1 Durchlauf des Makros funktioniert einwandfrei. Die Infos werden bspw. von range(c8:Y11) eingetragen. c8 ist die erste leere Zeile. Alles richtet sich danach und alle Felder zwischen C8:Y11 werden anhand des Makros befüllt.
2. Durchlauf Makro
Hier werden die Infos in (C12 : Y15) eingetragen. C12 ist die erste leere Zeile. C8:y11 wurden ja schon beim ersten Durchlauf befüllt. Mein Problem ist, das die Spalte F beim zweiten Durchlauf nicht von F12: F15 befüllt wird, sondern F8:F15. Die Infos aus dem 1. Durchlauf des Makros (F8:F11) werden durch den zweiten Durchlauf überschrieben in der Spalte F (F8:F15). Die Infos sollen aber nur in F12:F15 eingetragen werden.
Macht das für Dich Sinn? Sorry, das ist ein für mich ziemlich wichtiges Makro, verliere aber langsam wg. der Komplexität den Überblick. Hilft Dir das weiter?
Tatsächlich gibt es insgesamt vier Tabellenblätter auf die ich zugreife.
Habe soeben deinen Rat gelesen. Danke nochmals für den Hinweis...
Viele Grüße
Dani

Hi Dani,
du schreibst:
"ziemlich wichtiges Makro, verliere aber langsam wg. der Komplexität den Überblick."
Ich kenne diejenigen Teile des Makros, die hier im Thread geschrieben wurden.
Ich weiß NICHT, wo sich deine neuen Zeilen (die mit F8:F40) jetzt in dem Code befinden.
Wenn du nicht den Code im Zusammenhang postest, ist das reines Rätselraten.
Du schreibst:
"Mein Problem ist, das die Spalte F beim zweiten Durchlauf nicht von F12: F15 befüllt wird, sondern F8:F15."
Ja - du hast das so programmiert: For Each Zelle In Range("F8:F40")
Der Teil des Makros, der in der Suchschleife ausgehend von der letzten in Spalte C gefüllten Zeile
weitere Zeilen füllt, scheint ja ordentlich zu laufen.
Warum werden hier nicht auch gleich die Zellen in Spalte F mitgefüllt?
Handelt es sich nicht um jeweils um die selben Zeilen?
Noch einmal: Ohne kompletten Code oder eine Beispielmappe ist keine wirkliche Hilfe möglich.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich,
anbei der komplette Code unten. Ich weiß nicht, ob Dir das wirklich hilft, aber vielen Dank nochmals...
Mein Problem ist genau For Each Zelle In Range("F8:F40") glaube ich zumindest. Da statt in F12 beim zweiten Durchlauf was einzutragen die bereits befüllte Zelle von F8 und folgende überschrieben werden.
For Each Zelle In Range("F8:F40"): Frage: wie muß ich das abändern, damit nicht in F8 etwas eingetragen wird, sondern in der nächsten leeren Zelle, also F12?
Viele Grüße
Dani

Sub special_dividend()
Dim strRIC As String, rngSuch As Range                         'Variablen deklarieren
Dim rngF As Range, lngZ As Long, lngErst As Long
' RIC wird gesucht in AlleBestände_alleFonds in Spalte C;
' Eingabe von RIC für das erste Bezugsrecht in A35, C8
strRIC = InputBox("Bitte RIC Aktie eingeben:", "Dateneingabe:")
With Workbooks("Template_alle_Kapitalmaßnahmen in Arbeit.xls")
Set rngSuch = .Worksheets(2).Columns(3)
Set rngF = rngSuch.Find(What:=strRIC, LookAt:=xlPart)
If Not rngF Is Nothing Then
lngErst = rngF.Row
With .Worksheets(1)
lngZ = .Cells(.Rows.Count, 3).End(xlUp).Row  'letzte in Sp.C belegte Zeile
Do
Sheets(5).Range("B5").Value = strRIC
lngZ = lngZ + 1            ' nächste (leere) Zeile
.Cells(lngZ, 1) = rngF.Offset(0, -2)               ' a
.Cells(lngZ, 2) = rngF.Offset(0, -1)               ' b
.Cells(lngZ, 3) = strRIC                           ' C
.Cells(lngZ, 4) = rngF.Offset(0, 1)                ' d
.Cells(lngZ, 5) = rngF.Offset(0, 2)                ' e
.Cells(lngZ, 6) = rngF.Offset(0, 3)                ' f
.Cells(lngZ, 17) = rngF.Offset(0, 7)               ' Q
.Cells(lngZ, 12) = rngF.Offset(0, 5)               ' L
.Cells(lngZ, 20) = rngF.Offset(0, 13).FormulaR1C1  ' T
.Cells(lngZ, 18) = rngF.Offset(0, 12).FormulaR1C1  ' R
Set rngF = rngSuch.FindNext(rngF)
Loop While Not rngF Is Nothing And rngF.Row <> lngErst
End With
Else
MsgBox "Die Aktie ist nicht vorhanden bzw. die Eingabe wurde abgebrochen!", _
vbCritical, "Nur Zahlen eingeben!"
End If
End With
For Each Zelle In Range("F8:F40")
last_trade_Aktie = Sheets("Parameter").Range("b3").FormulaR1C1
If Zelle <> "" Then Zelle.Offset(0, 1) = last_trade_Aktie
Next Zelle
specialdividend = Application.InputBox("Bitte Betrag der special Dividend oder Capital Return  _
eingeben:", "Dateneingabe:", , , , , , 1)
If specialdividend = False Then Exit 

Sub
For Each Zelle In Range("F8:F40")
If Zelle <> "" Then Zelle.Offset(0, 15) = specialdividend
Next Zelle
For Each Zelle In Range("F8:F40")
AS_Geschäft = Sheets("Parameter").Range("h8").FormulaR1C1
If Zelle <> "" Then Zelle.Offset(0, 10) = AS_Geschäft
Next Zelle
Worksheets(5).Range("c17").FormulaR1C1 = Worksheets(4).Range("b12")
Worksheets(5).Range("c11").Value = Worksheets(4).Range("b7").Value
Sheets(4).Range("a3").Value = Sheets(5).Range("b5").Value
End Sub


							


Hallo Erich,
das Problem habe ich jetzt gelöst.
Vielen Dank für alles.
Dani