VBA zoeken en vervangen van leestekens in datumnotitie

[poelie01]

Hallo,

Zou graag in (kolom A) alle punten die voorkomen in een datumnotitie willen vervangen voor een -

Weet iemand hier een vba code voor?

Alvast bedankt.

 

[edmoor]

Al je een macro opneemt en dan zoek en vervang alle doet heb je de vba code ervoor.

 

[poelie01]

alleen vervangt excel het niet correct.

Voorbeeld:

01.10.2012 wordt 10-01-2012

 

[edmoor]

Dan moet je even kijken in de regionale instellingen van Windows of daar de datumnotatie wel goed staat.

 

[poelie01]

Hallo Edmoor,

Weet niet hoe de instellingen zouden moeten staan.

 

[edmoor]

Die staan in ieder geval goed.
Je krijgt dus na die zoek- en vervang opdracht de amerikaanse datum notatie.
kijk eens in de instellingen van Excel of er daar iets over staat.

 

[poelie01]

ook daar kan ik niks vreemds vinden

wellicht in de code?

    Cells.Replace What:=".", Replacement:="-", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

 

[poelie01]

Wanneer ik het handmatig via zoeken en vervangen laat lopen gaat het wel goed.

 

[Cobbe]

Probeer deze code maar eens:

Cells.NumberFormat = "dd-mm-yyyy"
Cells.Replace What:=".", Replacement:="-", LookAt:=xlPart, SearchOrder _
 :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True

 

[poelie01]

Hallo Cobbe,

Bedankt voor je reactie maar ook dit levert 10-01-2012 op ipv 01-10-2012

Gr.

 

[Warme bakkertje]

With Columns(1)
    .NumberFormat = "mm-dd-yyyy"
    .Replace What:=".", Replacement:="-", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
End With

 

[Cobbe]

Het zal dan toch aan je instellingen te wijten zijn.

 

[poelie01]

Hallo Bakkertje,

Opzich werkt de code alleen word de andere data die al in de kolom staan onjuist veranderd.

Voorbeeld:

Oude data in kolom A:

01-01-2012 (getalnotatie = * 14-03-2001)

Nieuwe data wordt toegevoegd onder oude data in kolom A:

01.10.2012 (getalnotatie = standaard)

Heb jij hier ook een oplossing voor?

Alvast bedankt!

 

[roeljongman]

Ik krijg altijd jeuk van de manier waarop VBA omgaat met datums.. het gaat zo vaak fout, dat heeft te maken met het feit dat VBA altijd met engelse datum opmaak werkt tenzij je in de macro specifieke instructies opneemt voor een ander formaat, en blijkbaar is het instellen van een celopmaak niet voldoende.

Ik gebruik (of misbruik) altijd de tekst naar kolommen functionaliteit. daar kun je zelf aangeven welke volgorde de getallenreeksen staan DMY of MDY of YMD wat je maar wilt.. Dit gaat zelfs onafhankelijk van de PC instelling, alleen de presentatie zal altijd in de PC instellingen modus zijn MAAR hij zal dan nooit de datum 1 oktober omzetten in 10 januari. op voorwaarde dat je dus de juiste volgorde hebt gekozen bij tekst naar kolommen functie.. (in dit geval DMY omdat met 01.10.2012 de datum 1 oktober word bedoeld)

Ik heb onderstaande code net getest en dat gaat goed.

Sub TekstNaarkolom()

    Range("A1", Range("A" & Rows.Count).End(xlUp)).Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    Columns("D:D").EntireColumn.AutoFit
End Sub

zie bijlage om het zelf even te proberen..

 

[poelie01]

Hallo Roel,

Bedankt voor je reactie

Heb dit getest op mijn bestand maar het geeft nog niet het gewenste resultaat.

Heb een bestand bijgevoegd zoals ik de celeigenschappen heb staan.

Bekijk bijlage testvervangpntdoorstreep(1).xlsm

 

[Warme bakkertje]

Wat doet deze?

Sub tst()
For Each cl In Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    If InStr(cl, ".") > 0 Then
        With cl
            .Replace ".", "-"
            .NumberFormat = "mm-dd-yyyy"
        End With
     End If
Next
End Sub

 

[poelie01]

@ warme bakker: probleem hiermee is dat ik de waarde weer gebruik voor een andere formule om het weeknr. te berekenen

=INTEGER((A1-DATUM(JAAR(A1-WEEKDAG(A1-1)+4);1;3)+WEEKDAG(DATUM(JAAR(A1-WEEKDAG(A1-1)+4);1;3))+5)/7)

@ roeljongman: Wanneer je de macro voor een tweede maal uitvoert vervangt hij de nieuwe input correct maar de oude input veranderd ook.

 

[Warme bakkertje]

Sub tst()
lRow = Range("A" & Rows.Count).End(xlUp).Row
fRow = lRow
For i = lRow To 1 Step -1
    If InStr(Cells(i, 1), ".") > 0 Then fRow = fRow - 1
Next
For Each cl In Range("A" & fRow + 1 & ":" & "A" & lRow)
    cl.Value = DateSerial(Split(cl.Value, ".")(2), Split(cl.Value, ".")(1), Split(cl.Value, ".")(0))
    cl.NumberFormat = "dd-mm-yyyy"
Next
End Sub

 

[poelie01]

Bij het opnieuw uitvoeren van de macro komt er een foutmelding:

Sub tst()
lRow = Range("A" & Rows.Count).End(xlUp).Row
fRow = lRow
For i = lRow To 1 Step -1
    If InStr(Cells(i, 1), ".") > 0 Then fRow = fRow - 1
Next
For Each cl In Range("A" & fRow + 1 & ":" & "A" & lRow)
    [COLOR="#FF0000"]cl.Value = DateSerial(Split(cl.Value, ".")(2), Split(cl.Value, ".")(1), Split(cl.Value, ".")(0))[/COLOR]    cl.NumberFormat = "dd-mm-yyyy"
Next
End Sub

 

[ExcelAmateur]

poelie01,

Een tip.
Selecteer de volgende keer je code en klik dan boven in het menu op #.
Dan komt je code netjes in een apart vak zoals bij "Warme bakkertje" te staan.