Datum wordt tekstveld

[Scripter]

Ik heb een kolom met tijden. Deze komt als data dump uit een order systeem. Alle tijden vanaf 10:00 worden keurig als tijd weergegeven, maar alle tijden daar onder worden voorafgegaan met een spatie (" 09:30" ipv "09:30" of "9:30"). Handmatig de spaties vervangen werkt prima, maar ik zou dit graag op willen nemen in een macro.
Echter als ik de macro dit uit laat voeren dan worden de tijden die voorheen een spatie hadden keurig als tijd genoteerd en de tijden zonder spatie krijgen een toevoeging AM of PM en worden neergezet als tekstveld. Probleem is dat ik dan geen verschil kan uitrekenen tussen start en eindtijd
De macro die ik nu gebruik is het volgende:

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

Hier zal vast een hele simpele oplossing voor zijn, maar ik zoek me radeloos.
Wie o wie kan me helpen?

 

[Hoornvan]

Scripter,

Dit heb ik gevonden misschien heb je er wat aan?
Het is door snb eens gemaakt.

Public Function RemoveSpaces(strInput As String)
   removespaces=replace(strinput," ","")
End Function

 

[Hoornvan]

Scripter,

Dit vond ik ook en even getest, werkt uitstekend.

- cel selecteren
- CTRL-H
- zoeken naar [spatie]
- vervangen door [niets]
- klikken op alles vervangen.

 

[Scripter]

Probleem omzeild

Hoornvan,
Bedankt voor de snelle reactie(s). De code getest, maar kreeg ik niet werkbaar. Het handmatig zoeken en vervangen werkt op zich prima, maar zodra je dat door een macro laat doen gaat het dus mis bij mij (zie mijn eerste bericht).
Probleem heb ik voor nu opgelost, door binnen een macro de kolom te splitsen in 2 kolommen (1 met de correcte tijden en 1 met de "corrupte" tijden), vervolgens haalt hij de spaties weg bij de "corrupte" tijden en voegt de 2 kolommen dan weer samen tot 1 kolom.
Beetje omslachtig, maar het werkt

Neemt niet weg dat ik het raar vind dat wanneer je een macro opdracht geeft om alleen spaties te verwijderen, dat hij dan de correcte tijdwaarden (waar dus geen spatie in zit) omzet naar tekstwaarden.

Probleem is voor nu dus omzeild, maar ik zou toch graag van iemand uitleg willen hoe dit kan, of dat ik toch iets niet helemaal goed doe.

 

[Hoornvan]

Scripter,

De handelingen heb ik even met de macro recorder opgenomen met dit als resultaat.
Hij zal nog vereenvoudigd moeten worden maar dat is voor later, zou dus moeten werken.

Sub Macro1()
    Range("A3").Select
    ActiveCell.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[Hoornvan]

Scripter,

Zo werkt de code ook, het kan misschien nog korter maar ik ben zo tevreden.
Hij werkt op kolom A.

Sub Macro1()
    Columns("A:A").Replace What:=" ", Replacement:="", ReplaceFormat:=False    
End Sub

 

[Hoornvan]

Je kunt deze code ook achter het blad zetten, als je dan even dubbel klikt voert hij de macro uitt.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Columns("A:A").Replace What:=" ", Replacement:="", ReplaceFormat:=False
End Sub

 

[Scripter]

Nog steeds niet

Beide versies uitgevoerd en nog steeds dezelfde (niet correcte) uitkomst.

" 8:30" wordt keurig omgezet naar "8:30" en wordt nu gezien als tijd
"10:00" wordt omgezet naar 10:00:00AM en is nu een tekstwaarde waar ik niks mee kan.

Getest op zowel Excel 2003 en 2007, dus daar zou het niet aan kunnen liggen...

 

[snb]

test eens:

with columns(1)
  .replace " ",""
  .value=.value
End With

 

[Hoornvan]

Heb de cel eigenschap op tijd staan?
Open mijn file eens en dubbelklik ergens, bij mij staat " 10:00" hierna als "10:00" en kijk ik dan bij eigenschap staat deze op tijd.
Dus snap ik het niet.

De code van snb ook geprobeerd en werkt correct (had niet anders verwacht)

 

[Scripter]

Helaas...

Volgens mij heb ik zelfs voor jullie nog een uitdaging te pakken...

Heb je bestandje getest en tja dat werkt, zolang de tijd tussen "" staat. Ik heb hem er nu als bijlage staan zoals ik hem nodig heb. Zoals je kan zien moet de tussenliggende tijd berekend worden. Voor de macro rekent regel 1 incorrect en 2 correct, na de macro is het andersom.

En helaas krijg ik met de code van SNB hetzelfde resultaat.

 

[Hoornvan]

Scripter,

Kijk nu of het zo is als je bedoeld.

 

[Scripter]

Scripter,

Kijk nu of het zo is als je bedoeld.

Jouw bestand werkt wel, maar jouw tijden staan tussen "". Die van mij niet en daar kan ik weinig aan doen aangezien ik de datadump van ons ordersysteem niet kan beinvloeden

 

[Hoornvan]

Scripter,

worden voorafgegaan met een spatie (" 09:30" ipv "09:30" of "9:30").

In het begin (Je start vraag) had je het hier over, dus ben ik daar van uitgegaan.

Dus is mijn werk voor niets geweest.

Zet anders een voorbeeld zonder gevoelige info en uitleg in het blad zodat we zien wat de bedoeling is.
Nu ben ik even de weg kwijt en zit te gissen, wat de bedoeling is.

 

[Scripter]

sorry, had het in de tekst tussen "" neergezet omdat je anders de spatie niet ziet
Zal zo even een bestande uploaden.

 

[Hoornvan]

Zal zo even een bestande uploaden.

Het komt toch niet met een postduif?

sorry, had het in de tekst tussen "" neergezet omdat je anders de spatie niet ziet

Zo zie je maar om misverstanden en kostbare tijd te voorkomen een voorbeeld bestandje duidelijker is dan een verhaal.

 

[Scripter]

Ok, bestandje staat er (kon zo snel geen postduif vinden). Dit is een stukje van de originele dump. De tijden in het rood worden dus niet gezien als tijd maar als tekst, waardoor ik de tussenliggende tijd niet kan berekenen.

 

[Hoornvan]

Scripter,

Het wil niet echt lukken, morgen ga ik wel verder, zo rond 6 uur is de grijze massa misschien wat frisser.

 

[snb]

Als je een xls bestand had geplaats had er wel naar willen kijken.

 

[Scripter]

Op verzoek in xls

Aangepast naar xls en gelijk de hopelijk tijdelijke work-around macro erin gezet.
Voor uitvoeren van mijn macro staan de tijden die vooraf worden gegaan met een spatie in het rood.
Ik heb voor nu alle overbodige kolommen leeg gehaald, vandaar dat de data misschien wat raar geplaatst staat.
En nee, ik verdien met deze macro zeker geen schoonheidsprijs maar het werkt zullen we maar zeggen.