VBA - Datum aus Range mit Offset suchen

Diskutiere und helfe bei VBA - Datum aus Range mit Offset suchen im Bereich Microsoft Office im Windows Info bei einer Lösung; Liebe Community, da mir hier schon einige Male schnell geholfen wurde, wäre ich begeistert, wenn dies noch einmal klappt. Ich arbeite an einem Skript... Dieses Thema im Forum "Microsoft Office" wurde erstellt von equipment-studios, 28. Oktober 2019.

  1. VBA - Datum aus Range mit Offset suchen


    Liebe Community,


    da mir hier schon einige Male schnell geholfen wurde, wäre ich begeistert, wenn dies noch einmal klappt.
    Ich arbeite an einem Skript zur Aufarbeitung von Daten (Umformatierung eines festen Layouts mit variabler Länge).


    Dafür ist u.a. eine Spalte "EventDay" nötig (diese läuft von -5 bis +5). Diese lasse ich aktuell durch eine Schleifenkonstruktion laufen (keine Schleifendiskussion an dieser Stelle - es ist nicht elegant, aber muss erstmal laufen, da Zeitdruck herrscht).


    Nun hatte ich bis vor kurzem eine Spalte "EventDate", die das echte Datum anzeigt. Diese Spalte wurde gefüllt, indem das Basisdatum (bspw. 01.10.2017) aus einer dritten Zelle genommen wird.

    Die Daten oberhalb und unterhalb habe ich per Schleife -5 + i (i++) eingetragen, indem ich vom EventDate einfach den EventDay abziehe/hinzurechne.

    Nun habe ich aber festgestellt, dass ich Feiertage und Wochenenden auf diese Weise nicht berücksichtigen kann.

    Deshalb habe ich eine zweite Datei aus welcher ich die Daten bereinigt entnehmen kann. Sozusagen rngDateImport


    Per UsedRange habe ich diese Spalte in einer Range gespeichert und möchte jetzt wie folgt vorgehen:


    Ich möchte aus rngDateImport die Zelle EventDate=0 (hier also 01.10.2017) suchen und dann die Zellen EventDate (-5 bis +5) mit den Werte aus rngDateImport (-5 bis +5 um EventDate 0).


    Wie stelle ich dies am geschicktesten an? Ich kann den Code unten posten.


    Dim wbkDates As Workbook

    Dim wksDates As Worksheet

    Dim varFile As Variant

    Dim rngDates As Range

    Dim rngDate As Range

    varFile = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx; *.xlsm; *.csv; *.xlt; *.xlst; *.xlm),*.xls; *.xlsx; *.xlsm; *.csv; *.xlt; *.xlst; *.xlm", , , , False)

    If varFile = False Then

    Exit Sub

    Else

    Set wbkDates = Workbooks.Open(varFile)

    End If

    Set rngDates = wbkDates.ActiveSheet.UsedRange

    wbkDates.Close (False)


    intCounterDateV = p_intCounterDateV

    intCounterH = wksIn.UsedRange.Columns.Count


    Debug.Print intCounterDateV

    Debug.Print intCounterH


    intTempI = 1

    intTempII = 1

    intTempIII = 0

    lngTempIV = 1


    Do Until intTempI = intCounterH


    m_varSplitHeaderCell = Split(wksIn.Cells(1, intTempI + 1), " ")

    m_strISIN = UCase(m_varSplitHeaderCell(1))

    varEventDay = Split(m_varSplitHeaderCell(2), "/")

    m_datEventDay = varEventDay(1) & "." & varEventDay(0) & "." & varEventDay(2)


    Do Until intTempII = intCounterDateV

    wksOut.Cells(intTempII + lngTempIV, 1) = m_strISIN

    wksOut.Cells(intTempII + lngTempIV, 4) = wksIn.Cells(intTempII + 1, intTempI + 1)

    wksOut.Cells(intTempII + lngTempIV, 3) = p_intRDLB + intTempIII

    wksOut.Cells(intTempII + lngTempIV, 2) = HIER WÄRE DAS DATUM EventDate ANZUZUZEIGEN

    intTempII = intTempII + 1

    intTempIII = intTempIII + 1

    Loop


    lngTempIV = lngTempIV + intTempII - 1

    intTempII = 1

    intTempIII = 0

    intTempI = intTempI + 1


    Loop



    Ich bedanke mich vorab für eure Unterstützung!

    Ich hatte bereits mit der Methode .Find versucht zu arbeiten. Oder aber auch mit Cells(find,1).Offset, aber komme einfach nicht weiter.


    VG

    Florian
     
  2. Claus Busch Win User

    Excel VBA Problem mit Find

    Hallo Susanne,

    die Feiertagsnamen stehen in Spalte A und das zugehörige Datum in Spalte B?

    Dann ist das nicht RESIZE sondern OFFSET.

    Probiere es doch einfach mit ZÄHLENWENN:

    Function IstArbeitsTag(rngDatum As Range) As IntegerSet rngfeiertage = Range("Holidays").Offset(, 1)If Application.CountIf(rngfeiertage, rngDatum) > 0 Then
    
       IstArbeitsTag = 0
    
    Else
    
        IstArbeitsTag = 1
    
    End IfEnd Function
    Claus
  3. Albrecht_913 Win User

    Vergleichen und Zuordnen von Werten in Spalten und Zeilen

    Hallo Rainer, danke für deine ausführlichen Informationen.

    'Set rng = ActiveCell.Offset(0, 0) steht mit Apostroph und war ein Rudiment aus einem alten Programm

    Range("A1").Select ist passend.

    Neben der Sortierung, der Zuordnung der Vergleichs zu den Quellwerten besteht für mich die zweite Aufgabe:

    Hafen von F12 nach D10 und Hamburg von E12 nach E10.

    Ich habe hierzu eine Lösung gefunden. Die komplette Prozedur untenstehend

    Die "ActiveCell.Offset..." waren ein untauglicher Versuch die nach Hamburg folgenden Zellen zu kopieren und der Quelle zuzuordnen.

    Ich habe das z.B. mit

    Range("AF3").Offset(j, 0).Copy Destination:=Range("D2").Offset(i, 1)

    gelöst. Danke nochmal für deine Unterstützung.

    Hier die komplette Prozedur. Ich habe sie nun nicht nochmal auf das Muster umgeschrieben:

    -------------------------------------------------------------------------------------------------------

    Range("A1").Select

    'Set rngQuelle = Range("C2")

    'Set rngVergleich = Range("AF3")

    For i = 0 To 500 - 1 'äußere Schleife (bea.: beginnt bei 0)

    j = 0 'Initialisierung der inneren Schleife

    Do Until j = 500 - 1 'innere Schleife, läuft maximal bis Endwert

    If Range("C2").Offset(i, 0).Value = Range("AF3").Offset(j, 0).Value Then

    Range("AF3").Offset(j, 0).Copy Destination:=Range("D2").Offset(i, 1)

    Range("AK3").Offset(j, 0).Copy Destination:=Range("E2").Offset(i, 1)

    Range("AV3:BB3").Offset(j, 0).Copy Destination:=Range("F2").Offset(i, 1)

    Range("BN3:BP3").Offset(j, 0).Copy Destination:=Range("M2").Offset(i, 1)

    Exit Do

    End If

    j = j + 1

    Loop

    Next i

    End Sub
  4. Claus Busch Win User

    Excel Macro mit dynamischen Formeln

    Hallo Roland,



    die VBA-Hilfe kann ich auch empfehlen.

    Zum Definieren von Ranges kannst du dir auch mal OFFSET und RESIZE anschauen.

    Mit einer festgeschriebenen Zelle kannst du mit OFFSET den Versatz beschreiben. Auch hier ist die erste Zahl der Versatz in Zeilen und die zweite der Versatz in Spalten, z.B:

    With Range("C3")

    .Value = 5 'C3 wird der Wert 5 zugewiesen

    .Offset(0, 1) = 4 'D3 wird der Wert 4 zugewiesen

    .Offset(1, 0) = 3 'C4 wird der Wert 3 zugewiesen

    End With

    Mit postiven Zahlen ist der Versatz abwärts bzw. nach rechts, negativ umgekehrt.



    Mit RESIZE kannst du einen Bereich vergrößern. Auch hier wieder erste Zahl Zeilenanzahl, zweite Spaltenanzahl. Deinen Bereich A1:BO3 könntest du damit also auch so definieren:

    Range("A1").Resize(3, 67)



    Mit freundlichen Grüßen

    Claus
  5. Andreas Killer Win User

    Such Erweiterung anhand von Zellinhalt

    Mein Fehler, sorry.

    Andreas.

    Private Sub FarbCode()

    Dim Zeile As Range, R As Range

    Dim Datum As Date, Auftragsnummer

    Dim i As Integer, j As Integer

    'Suche nach der Farbe

    Application.FindFormat.Interior.ColorIndex = 44

    With Worksheets("Arbeitsplan")

    'Daten lesen

    Set Zeile = .Rows(TextBox1.Value)

    Auftragsnummer = .Cells(Zeile.Row, 1)

    For j = 0 To 1 'Ändere 1 zu 3 um in +/- 3 Zeilen zu suchen

    For i = -1 To 1 Step 2

    Set Zeile = .Rows(TextBox1.Value).Offset(j * i)

    If Auftragsnummer = .Cells(Zeile.Row, 1) Then

    Set R = Zeile.Find("*", Zeile.Cells(Zeile.Cells.Count), SearchFormat:=True)

    If Not R Is Nothing Then

    If Datum = 0 Then

    Datum = .Cells(23, R.Column)

    Else

    'Datum kleiner?

    If .Cells(23, R.Column) < Datum Then Datum = .Cells(23, R.Column)

    End If

    End If

    If j = 0 Then Exit For

    End If

    Next

    Next

    End With

    If Datum = 0 Then

    'Zellinhalt löschen wenn nicht gefunden

    Worksheets("Arbeitszeiten").Cells(11, 9).ClearContents

    Else

    'Datum übertragen

    Worksheets("Arbeitszeiten").Cells(11, 9) = Datum

    End If

    End Sub
  6. Claus Busch Win User

    MS Excel- Spalte mit Datum aktualisieren; MAKRO

    Hallo Dominik,

    viel hilft es nicht, denn ich weiß nicht, wo die Daten herkommen und nicht, ob diese gelben Zellen mit dem Datum ein Formular-Button sind oder ob du mit Klicken die Events BeforeDoubleClick oder BeforeRightClick meinst und auch nicht wie die Tabellenblätter
    heißen.

    Ich gehe mal davon aus, dass es Button aus den Formularsteuerelementen sind. Das Datum darauf ist hoffentlich nicht nur die Beschriftung sondern auch der Name des Steuerelementes.

    Dann könntest du mit rngSource deinen Quellbereich in der Pivot-Tabelle festlegen und über den Namen des Steuerelements die entsprechende Spalte zur Aktualisierung auswählen, z.B. so:

    Dim rngCmd As Range

    Dim rngSource As Range, rngTarget As Range

    Dim arrSource As Variant

    Set rngSource = Sheets("Tabelle2").Range("D1:D14")

    Set rngTarget = ActiveSheet.Shapes(CStr(Date)).TopLeftCell _

    .Offset(1, 0).Resize(14, 1)



    arrSource = rngSource

    rngTarget = arrSource

    Edit: Es wäre einfacher, wenn das gelb markierte nur Zellen mit Datumswerten wären. Du würdest nur einen einzigen Button brauchen, um alle Spalten gemäß dem Datum aktualisieren zu können.

    Mit freundlichen Grüßen

    Claus
  7. User Advert


    Hi,

    willkommen im Windows Forum!
Thema:

VBA - Datum aus Range mit Offset suchen - Microsoft Office

Die Seite wird geladen...

VBA - Datum aus Range mit Offset suchen - Similar Threads - VBA Datum Range

Forum Datum

Datum in Dateinamen suchen

Datum in Dateinamen suchen: Hallo. Ich habe mir heute den Kopf zermartert. Ich habe einen Link ([Verzeichnispfad] + [Dateiname]). Manche Dateinamen enthalten ein Datum. Ich möchte nun das Datum isolieren. SUCHEN in...
Microsoft Office 5. November 2019

Suche Werte und davon das älteste Datum

Suche Werte und davon das älteste Datum: Hallo mein nNme ist Johnny und ich schreibe zum ersten mal in einem Forum:) Mein Problem: Ich habe eine Exceltabelle mit 2 übergeordneten spalten. 1. Spalte Name 2. Spalte Datum In Spalte...
Microsoft Office 17. September 2019

Excel VBA, Datum in Spalte suchen und Zellinhalt von anderem Tabellenblatt kopieren

Excel VBA, Datum in Spalte suchen und Zellinhalt von anderem Tabellenblatt kopieren: Hallo, ich habe in Tabelle "Test" eine Spalte B mit fortlaufendem Datum. Ich möchte ein Makro erstellen, welches das heutige Datum findet und in die entsprechende Zeile in Spalte F den...
Microsoft Office 19. Januar 2018

Excel Datum eintragen bei Änderung in Range

Excel Datum eintragen bei Änderung in Range: Guten Morgen, ich hätte gern, das automatisch das aktuelle Datum eingetragen wird in der Spalte x wenn ein Wert der jeweiligen Zeile geändert wird, ich habe schon diverse Foren durchsucht und...
Microsoft Office 16. Januar 2018

Verwendung von Range Objekten in VBA

Verwendung von Range Objekten in VBA: Hallo Da ich nicht beliebig aus VBA auf Excel Tabellen zugreifen kann (schmerzliche Erfahrung und Hilfeleistung eines anderen Microsoft Anwenders), bin ich nun genötigt, alle relevanten Zellen...
Microsoft Office 13. November 2017

Wie kann ich per vba den offset bei Hintergrundbildern verändern?

Wie kann ich per vba den offset bei Hintergrundbildern verändern?: Wie kann ich per vba den offset bei Hintergrundbildern verändern? Viele Grüße W.
Microsoft Office 16. Mai 2017

Excel 2010: Datum in VBA immer ein Tag daneben (bei Datum im jahr 1900)...

Excel 2010: Datum in VBA immer ein Tag daneben (bei Datum im jahr 1900)...: In meinen Daten im VBA Marko sind Datumsangaben immer um ein Tag daneben... In meinem Excel steht definitiv das korrekte Datum z.B. 19.1.1900 rufe ich es aber ab, so steht aber in VBA dort...
Microsoft Office 13. Januar 2017
VBA - Datum aus Range mit Offset suchen solved
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.