Thema: OFFSET Eigenschaft | Herbers Excel-Forum

Hallo zusammen,
habe ein Problem mit dem unteren Makro. In sheets(2).range("C:C") wird der RIC Aktie gesucht und dann in Sheets(4).range("A12") eingetragen. Mein Problem ist, dass ich möchte das der Wert, der sich zwei Spalten weiter von sheets(2).range("C:C") also in Spalte A befindet auch in Sheets(4).range("b12") eingetragen werden soll. Nur irgendwie klappt das nicht mit dem unteren Makro. Ich bekomme die Fehlermeldung. Laufzeitfehler 438. Objekt unterstützd die Eigenschaft oder Methode nicht.
Vielen Dank im Voraus für Tips.
Dani
Sub Austausch()
Dim RICAktie As String
RICAktie = InputBox("Bitte RIC Aktie eingeben:", "Dateneingabe:")
With Workbooks("Template BZR in Arbeit.xls")
If Not .Worksheets(2).Range("c:c") _
.Find(What:=RICAktie, LookAt:=xlPart) Is Nothing Then
.Worksheets(1).Range("p8") = RICAktie
.Worksheets(4).Range("A3") = RICAktie
.Worksheets(4).Range("b12") = Worksheets(2).Offset(-2, RICAktie) (HIER IST DAS Problem)
.Worksheets(4).Range("A12") = RICAktie
Else
MsgBox "Die Aktie ist nicht vorhanden bzw. die Eingabe wurde abgebrochen!", _
vbCritical, "Nur Zahlen eingeben!"
End If
End With

Hallo,
was machst du denn da?
Schau doch mal in der Hilfe, wie der Offset Befehl zu verwenden ist.

Ausdruck.Offset(RowOffset, ColumnOffset)

Deine Variable RICAktie ist als String definiert und wird per Inputbox gefüllt.
Du nimmst nun als RowOffset - 2 und als ColumnOffset die String-Variable.
Wie soll das denn funktionieren?
Ich schreib dir jetzt bewusst nicht die Lösung hin, das findest du selber raus.
Hilfe zur Selbsthilfe. Wie gesagt, einfach mal in der Hilfe schauen.
Gruß
Björn B.

Ja, Du hast Recht. In der Hilfe habe ich schon nachgeschaut. Es müßte richtig heißen offset(-2, 0) aber das löst leider mein Problem nicht.
danke trotzdem
dani

Warum löst das Dein Problem nicht?
Vielleicht solltest Du richtig lesen ;-)
Row = Zeile und Column = Spalte.
Du willst 2 Spalten weiter links.
Offset(-2,0) ist aber 2 Zeilen weiter oben.
Gruß
Björn

Sorry, aber ich glaube Du hast Dir einfach mein Problem nicht gründlich durchgelesen. Egal, ob zwei Zeilen weiter oben, oder links, die Fehlermeldung ist gleich geblieben, also kann das nicht die Lösung sein.
Wäre super wenn mir jemand helfen könnte.
Danke.
Dani

Hallo,
Offset ist eine Eigenschaft des Range- und des TickLabels-Objektes. Du versuchst sie sie aber bei einem Worksheet-Objekt zu setzen.
Worksheets(2).Offset(-2, RICAktie)
Gruß
Nepumuk

nicht gründlich durchgelesen? Das hast glaube ich Du mit der Hilfe gemacht...
Au Mann, ich habe mir dein Problem schon angesehen und ich habe auch getestet, bei mir funktioniert es.
Ich finde es nur nicht gut, dass sich manch einer oder eine nicht die Mühe macht, selbst nachzudenken und es vielen zu viel ist, die Hilfe aufmerksam zu lesen.
Ich werde mal wieder die Hilfe zitieren:

Ausdruck.Offset(RowOffset, ColumnOffset)

Das hatten wir ja schon. Row und Column habe ich dir auch schon übersetzt.
Nun ist da noch dieser "Ausdruck". Was könnte das denn sein?
Auch hierzu ein Satz aus der Hilfe:

Ausdruck      Erforderlich. Ein Ausdruck, der ein Range-Objekt zurückgibt.

Hier ist das einzig wichtige Wort : Range-Objekt
Was machst Du?
Du schreibst:
Worksheets(2).Offset(-2, RICAktie)
Worksheets(2) ist also ein ungültiger Ausdruck für Offset, da es sich nicht um ein Range-Objekt handelt.
Versuch doch mal selber zu "übersetzen". Du sagst "Arbeitsblatt Nummer 2 und dann 2 Spalten nach links".
Aber links von was? Ein Arbeitsblatt hat viele Spalten? Von wo soll VBA nun wissen, was du willst, wenn du es ihm nicht sagst?
Lösung:
Du musst also zwischen das "Worksheets(2)." und das "Offset" noch die Range angeben, von welcher aus du 2 Spalten nach links willst.
Verstanden?
Gruß
Björn B.

Auf die Hilfe zu verweisen ist mir keine Hilfe. Diese kenne ich selber auch. Schaue eh vorher immer rein. Meine VBA Kenntnisse sind wie schon angegeben nicht sehr hoch, deswegen ist mir die Hilfe auch nicht immer eine Hilfe. Den meisten anderen wohl auch nicht, sonst wären wir wohl kaum hier.
Also BITTE, wenn Du mir nicht helfen MÖCHTEST erspar Dir bitte weitere Kommentare und wenn Du mir helfen möchtest (indem Du zum 20x Mal auf die Hilfe verweist) dann BITTE auch.
Dani

Hallo zusammen,
das untere klappt leider auch nicht . Ich möchte, dass
sobald VBA in Datenblatt2 Spalte c den Wert RIC Aktie gefunden hat (z.B. C5). das Makro den Wert zwei Spalten nach links (also A5) in Sheets(4). range("b12") reinschreibt.
Bekomme Laufzeitfehler 1004. Anwendungs - oder objektdef. Fehler.
.Worksheets(4).Range("b12") = Sheets(2).Range("C:C").Offset(-2, 3)
Danke für die Hilfe.
Dani

Hallo,
einen Offset auf eine komplette Spalte gibt es nicht. Du musst da eine einzelne Zelle angeben.
Range("C:C").Offset(-2, 3)
Gruß
Nepumuk

Hallo,

einen Offset auf eine komplette Spalte gibt es nicht.

Sub test()
Dim r As Range
Set r = Columns(2).Offset(, 2)
MsgBox r.Address
End Sub

Nur für seine Anforderung passt das natürlich auch nicht.
Gruß
Rudi

Hallo Rudi,
da hast du Recht. :-)
Gruß
Nepumuk

Hi Dani,
das kann nicht gehen:
Sheets(2).Range("C:C") ist die gesamte Spalte C. Wie sollte VBA die um 2 Zeilen nach oben verschieben können?
Schreibst du mal bitte (besser nicht in VBA, sondern in deutschen Worten),
welcher Wert der Zelle Range("b12") eigentlich zugewiesen werden soll?
Hat das vielleicht irgend etwas zu tun mit dem Find-Ergebnis?
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

ja, genau. Der "RIC Aktie" wird in Datenblatt 2 Spalte C gesucht und über .Find in z.B. C5 gefunden. Ich brauche, aber zusätzlich den Wert zwei Spalten nach links von C5 also A5. Den Wert aus A5 soll in das Datenblatt 4 in B12 reingeschrieben werden. Hilft Dir das weiter? Ich füge mal unten das Makro komplett ein. Das Problem besteht aber in Makro Zeile 9. Jetzt bekomme ich keine Fehlermeldung mehr, aber er schreibt mir den Wert aus A5 auch nicht ins Datenblatt 4 "B12".
Vielen Dank.
Dani

Sub Austausch()
Dim RICAktie As String
RICAktie = InputBox("Bitte RIC Aktie eingeben:", "Dateneingabe:")
With Workbooks("Template BZR in Arbeit.xls")
If Not .Worksheets(2).Range("c:c") _
.Find(What:=RICAktie, LookAt:=xlPart) Is Nothing Then
.Worksheets(1).Range("p8") = RICAktie
.Worksheets(4).Range("A3") = RICAktie
.Worksheets(4).Range("A12") = RICAktie
.Worksheets(4).Range("b12") = Worksheets(2).Range("C:C").Offset(0, -2) (HIER IST DAS PROBLEM)
Else
MsgBox "Die Aktie ist nicht vorhanden bzw. die Eingabe wurde abgebrochen!", _
vbCritical, "Nur Zahlen eingeben!"
End If
End With
Worksheets(1).Range("q8").FormulaR1C1 = Worksheets(4).Range("b3").FormulaR1C1
Bezugspreis = InputBox("Bezugspreis bitte eingeben:", "Dateneingabe:")
Worksheets(1).Range("q9") = Bezugspreis
alte_Aktien = InputBox("BEZUGSVERHÄLTNIS: alte Aktien eingeben", "Dateneingabe:")
Worksheets(4).Range("b4") = alte_Aktien
neue_Aktien = InputBox("BEZUGSVERHÄLTNIS: neue Aktien eingeben", "Dateneingabe:")
Worksheets(4).Range("b5") = neue_Aktien
Dividendennachteil = InputBox("Dividendennachteil eingeben:", "Dateneingabe:")
Worksheets(1).Range("q10") = Dividendennachteil
Worksheets(1).Range("q14").Value = Worksheets(4).Range("b7").Value
End Sub

Hi Dani,
du willst also nicht den Wert zwei Spalten links der Spalte C (was nicht geht), sondern links vom Suchergebnis.
Dann musst du das auch hinschreiben. Das das Suchergebnis jetzt 2 mal gebraucht wird,
hab ich ihm eine Range-Variable rngF gegönnt:

Option Explicit
Sub Austausch()
Dim RICAktie As String, rngF As Range
RICAktie = InputBox("Bitte RIC Aktie eingeben:", "Dateneingabe:")
With Workbooks("Template BZR in Arbeit.xls")
Set rngF = .Worksheets(2).Range("c:c").Find(What:=RICAktie, LookAt:=xlPart)
If Not rngF Is Nothing Then
.Worksheets(1).Range("p8") = RICAktie
.Worksheets(4).Range("A3") = RICAktie
.Worksheets(4).Range("b12") = rngF.Offset(-2, 0)
.Worksheets(4).Range("A12") = RICAktie
Else
MsgBox "Die Aktie ist nicht vorhanden bzw. die Eingabe wurde abgebrochen!", _
vbCritical, "Nur Zahlen eingeben!"
End If
End With
' usw.
End Sub

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

Hi Dani,
hast du den Fehler gefunden, den ich da eingebaut hatte?
Schreib mal
rngF.Offset(0, -2) oder rngF.Offset(, -2)
statt
rngF.Offset(-2, 0)
Schließlich soll rngF um zwei Spalten verschoben werden, nicht um Zeilen.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich,
Den Fehler habe ich gemerkt, da das Makro nicht funktioniert hat :-), aber den Fehler nicht gefunden :-)
Vielen vielen Dank für die Hilfe.
Dani

Hallo zusammen, hätte doch noch eine zusätzliche Frage zu dem Makro oben.
Das Problem ist das RIC Aktie in Datenblatt 2 Spalte C öfters vorkommt. Ist es möglich, dass es alle RIC Aktie die er in Spalte C Datenblatt 2 finden kann ins Datenblatt 4 kopiert? Würde so etwas gehen mit .FindNext gehen? Wenn ja, wie sieht das aus?
Vielen Dank für die Hilfe.
Dani

Hi Dani,
was genau meinst du mit "ins Datenblatt 4 kopiert"?
Bisher sind die Zielzellen (auch in Worksheets(1), nicht nur in Worksheets(4)) feste Zellen.
Sollen bei weiteren Fundstellen für RICAktie diese werde immer wieder überschrieben werden,
so dass nachher nur noch die Werte der letzten Fundstelle in diesen Zellen stehen?
Das halte ich für wenig sinnvoll.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Hallo Erich,
nein, die sollen nicht überschrieben werden sondern, eine Zeile weiter nach unten eingetragen werden.
D.h. der nächste RIC Aktie, der in Datenblatt 4 Spalte C gefunden wird, soll in der nächsten Zeile in Datenblatt 4 stehen hinter dem ersten gefunden RIC Aktie. Ich habe dein Makro ein bisschen noch ergänzt. Ich füge es mal hinzu.
Mein Problem ist, dass RIC Aktie ungefähr 5x in Tabellenblatt 2 Spalte c vorkommt. Ich möchte das jedesmal, wenn er diesen RIC findet (insgesamt müssten es z.B 5x mal sein). dieser ins Tabellenblatt 4 in der nächsten leeren ZEile eingetragen wird. Macht das Sinn für Dich?
Vielen Dank.
Dani
Sub Austausch3()
Dim RICAktie As String, rngF As Range
RICAktie = InputBox("Bitte RIC Aktie eingeben:", "Dateneingabe:")
With Workbooks("Template BZR in Arbeit.xls")
Set rngF = .Worksheets(2).Range("c:c").Find(What:=RICAktie, LookAt:=xlPart)
If Not rngF Is Nothing Then
.Worksheets(1).Range("p8") = RICAktie
.Worksheets(1).Range("c8") = RICAktie
.Worksheets(4).Range("A3") = RICAktie
.Worksheets(4).Range("a8") = rngF.Offset(0, -2)
.Worksheets(4).Range("d8") = rngF.Offset(0, 1)
.Worksheets(4).Range("e8") = rngF.Offset(0, 2)
.Worksheets(4).Range("f8") = rngF.Offset(0, 3)
.Worksheets(4).Range("f8") = rngF.Offset(0, 3)
.Worksheets(4).Range("b8") = rngF.Offset(0, -1)
.Worksheets(4).Range("m8") = rngF.Offset(0, 7)
Else
MsgBox "Die Aktie ist nicht vorhanden bzw. die Eingabe wurde abgebrochen!", _
vbCritical, "Nur Zahlen eingeben!"

Hallo Dani,
so?

.Worksheets(4).Range("a" & .Cells.Count).End(xlUp).Offset(1, 0) = rngF.Offset(0, -2)
.Worksheets(4).Range("d" & .Cells.Count).End(xlUp).Offset(1, 0) = rngF.Offset(0, 1)
.Worksheets(4).Range("e" & .Cells.Count).End(xlUp).Offset(1, 0) = rngF.Offset(0, 2)
.Worksheets(4).Range("f" & .Cells.Count).End(xlUp).Offset(1, 0) = rngF.Offset(0, 3)
.Worksheets(4).Range("b" & .Cells.Count).End(xlUp).Offset(1, 0) = rngF.Offset(0, -1)
.Worksheets(4).Range("m" & .Cells.Count).End(xlUp).Offset(1, 0) = rngF.Offset(0, 7)

Gruß Ingolf

Hallo Ingolf,
das klappt leider auch nicht. Bekomme die Fehlermeldung mit Laufzeitfehler 438. Objekt unterstützt diese Eigenschaft oder Methode nicht.
Viele Grüße
dani

Hi Dani,
wenn solche Rückfragen nötig sind, ist das immer etwas aufwändiger...
Nun denn, probier mal den folgenden ungetesteten Code:

Option Explicit
Sub Austausch3()
Dim strRIC As String, rngF As Range, lngZ As Long, lngErst As Long, lngU As Long
strRIC = InputBox("Bitte RIC Aktie eingeben:", "Dateneingabe:")
With Workbooks("Template BZR in Arbeit.xls")
Set rngF = .Worksheets(2).Range("c:c").Find(What:=strRIC, LookAt:=xlPart)
If Not rngF Is Nothing Then
lngErst = rngF.Row
Do
lngZ = rngF.Row
With .Worksheets(1)
.Range("p8") = strRIC
.Range("c8") = strRIC
End With
With .Worksheets(4)
.Cells(3 + lngU, 1) = strRIC                 ' A3
.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, 6) = rngF.Offset(0, 3)      ' f8 doppelt
.Cells(8 + lngU, 13) = rngF.Offset(0, 7)     ' m8
End With
lngU = lngU + 1
Set rngF = .FindNext(rngF)
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
End Sub

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

Hallo Erich,
das klappt leider. Bekomme die Fehlermeldung mit Laufzeitfehler 438. Objekt unterstützt diese Eigenschaft oder Methode nicht?
Viele Grüße und nochmals vielen Dank.
dani

Hî Dani,
sorry, da war ein Fehler drin.
Ihn zu finden, wäre leichter und schneller gegangen, wenn du auch die fehlerhafte Codezeile mitgeteilt hättest.
Nun ja, anders als viele andere hier hast du ja die Fehlernachricht gepostet.
Ich vermute, die Prozedur blieb bei ".FindNext(rngF)" stehen.
Dem FindNext fehlt das richtige Objekt - das wäre der zu durchsuchende Bereich,
nicht nur "Workbooks("Template BZR in Arbeit.xls").
Hier eine korrigierte und vielleicht jetzt lauffähige Version (ungetestet):

Option Explicit
Sub Austausch3()
Dim strRIC As String, rngSuch As Range
Dim rngF As Range, lngZ As Long, lngErst As Long, lngU As Long
strRIC = InputBox("Bitte RIC Aktie eingeben:", "Dateneingabe:")
With Workbooks("Template BZR 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("p8") = strRIC
.Range("c8") = strRIC
End With
With .Worksheets(4)
.Cells(3 + lngU, 1) = strRIC                 ' A3
.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, 6) = rngF.Offset(0, 3)      ' f8 doppelt
.Cells(8 + lngU, 13) = rngF.Offset(0, 7)     ' m8
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
End Sub

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

Hallo Erich,
das funktioniert jetzt.
super vielen Dank für die Hilfe.!!
Dani

Hallo,

If Not .Worksheets(2).Range("c:c") _
.Find(What:=RICAktie, LookAt:=xlPart) Is Nothing Then
.Worksheets(1).Range("p8") = RICAktie
.Worksheets(4).Range("A3") = RICAktie
.Worksheets(4).Range("A12") = RICAktie
.Worksheets(4).Range("b12") = .Worksheets(2).Range("c:c") _
.Find(What:=RICAktie, LookAt:=xlPart).Offset(0, -2) 

Gruß
Rudi

Hallo Rudi,
vielen Dank für deine Hilfe.
Dani

... was in so ner Stunde Mittagspause alles passiert.
Liebe(r) Dani.
ich habe (wenn du meine Beträge aufmerksam gelesen hast) Dir nicht nur die Hilfe zitiert, sondern Dir die entsprechenden Passagen ausführlich erklärt. Komischerweise haben dir andere genau das gleiche geschrieben.
Wenn Du nicht so emotional meine Beiträge lesen würdest, dann wüsstest Du eine Hilfe zu schätzen.
Ich habe Dir genau die richtigen Hinweise gegeben und erklärt, woran es liegt.
Ich finde es eine Unverschämtheit zu behaupten, dass ich Dir nicht helfen will. Ich nehme mir für meine Antworten sehr viel Zeit und versuche sie so zu schreiben, dass du es gut nachvollziehen kannst (in dem Du dann selber auch noch mal in der Hilfe nachschaust).
Nur so kanns Du doch was lernen (auch wie man die Hilfe liest und verstehen muss).
Was bringt es Dir, wenn ich Dir gleich zu Beginn die richtige Code-Zeile gebe und Du es aber nicht verstehst, was an Deinem Code falsch war? Wäre Dir das lieber gewesen?
Ich hoffe nicht.
Ich wollte Dich nicht persönlich angreifen.
Ich wollte Dir nur sagen, wo Du die Hilfe findest. Und habe Dir geholfen, diese Hilfe auch zu verstehen.
Wenn Dir die Hilfe keine Hilfe ist, so wie Du schreibst, dann liegt das sicher nicht an der Hilfe, sondern daran, dass Du sie (noch) nicht richtig lesen kannst. Und genau das kannst Du doch jetzt ein Stückchen besser, oder nicht?

Den meisten anderen wohl auch nicht, 

Das glaube ich nicht, die meisten die hier sind, kennen sich ziemlich gut aus und viele sind in manchen Bereichen Experten. Da tust Du den anderen Teilnehmern ein bisschen unrecht.
Aber nix für ungut.
Wie schon gesagt, ich wollte Dir helfen (und habe das auch getan).
Manchmal braucht man vielleicht nen kleinen Klapps.
Fallls ich Dich damit verärgert habe, dann tut es mir leid.
Funktioniert denn jetzt alles so wie Du wolltest?
Gruß
Björn B.

Hallo Björn,
ein bisschen habe ich es persönlich genommen, da hast Du recht. Aber ich bin keine Expertin, was VBA anbelangt. Leider! Natürlich wäre ich es gerne! Schaue auch wie schon erwähnt, regelmäßig in der Hilfe - Funktion rein. Hilft mir aber wie gesagt nicht immer weiter, da mein Kenntnisstand noch gering ist, deswegen hat es mir nicht geholfen das Du mich ständig draufverwiesen hast.
Hinzu kommt (wie ich versucht habe Dir zu erklären), war mein Problem noch ein anderes! Es war nicht nur die Offset Eigeschaft.
Danke für deine Mühe, die Zeit und der Nachricht!!!
Das die Leute hier im Forum was drauf haben ist mir auch klar :-).
Ja, der Erich hat mir die Lösung gezeigt.
Viele Grüße
Dani