Excel kent trim/spaties.wissen niet

[Paultje3181]

Ik heb een nogal grote macro geschreven die doet wat ie moet doen, met 1 uitzondering: hij herkent de opdracht trim niet.

Op internet legio topics te vinden die het verschil aangeven tussen spaties en   en 160, maar zover komt ie bij mij niet eens.

Ik krijg #naam terug en in de formule staat letterlijk =trim(deel(A1;12;7)) ipv
=spaties.wissen(deel(A1;12;7))

Ik heb dit probleem op meerdere pc's met Excel 2013 en office 365.

Iemand enig idee waar de oorzaak zou kunnen liggen?

De macro opent een tekstbestand zonder extensie en doet daar vervolgens zijn ding op. Gewoon standaard trek wat gegevens uit a en zet deze in de volgende cel. Weinig berekeningen, maar wel een groot bestand (70000 regels+)

[Snake]

Kan je het probleem herproduceren in een kleinere Excel file?

https://1drv.ms/x/s!ArD5hqFzHvQphY9B0KLJjOGluzF0rQ

A2 neemt A1, vanaf 4 karakters, dus 4,5,6, en spatie, en trimt dat dan.

Werkt dat?

[ Voor 54% gewijzigd door Snake op 27-01-2018 00:40 ]

[Lustucru]

Paultje3181 schreef op zaterdag 27 januari 2018 @ 00:24:
macro herkent de opdracht trim niet.
[...]

Ik krijg #naam terug en in de formule staat letterlijk =trim(deel(A1;12;7)) ipv
=spaties.wissen(deel(A1;12;7))

Ik snap er niks van. Trim is een VBA instructie en ook een engelstalige worksheetfunction, die in het Nederlands spaties.wissen heet. Wat een werkbladfunctie in een macro doet snap ik niet, en ook niet waarom je in een formule expres Engels en Nederlandse benamingen door elkaar gebruikt?

Post eens de relevante coderegels?

[ Voor 3% gewijzigd door Lustucru op 27-01-2018 01:38 ]

[Paultje3181]

In de VBA staat trim. Normaal gesproken wordt dit omgezet in spaties.wissen in het werkblad. Dit gebeurt nu dus niet, maar ik krijg een #naam

Visual Basic:

Open workbook as csv, using ; as delimiter
    Workbooks.OpenText FileName:=fn, Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:= _
        False, Space:=False, Other:=False, _
        TrailingMinusNumbers:=True
Application.ScreenUpdating = False
Dim lastrow As Long, i As Long, behouden As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B1").FormulaR1C1 = "=IF(OR(MID(RC1,30,5)=""HP-nr"",MID(RC1,30,6)=""INK-pr"",MID(RC1,16,10)=""Korte naam""),""N"",IF(OR(LEFT(RC1,14)=""          Cod:"",LEFT(RC1,14)=""          Ver:"",LEFT(RC1,14)=""          Pre:"",MID(RC1,7,2)=""SP""),""J"",""N""))"
.Range("B1").AutoFill Destination:=Range("B1:B" & lastrow)
.Range("B:B").Calculate
.Range("A1:B" & lastrow).Sort key1:=Range("B1"), order1:=xlAscending, Header:=xlNo
.Range("B:B").Copy
.Range("B:B").PasteSpecial xlPasteValues
behouden = Application.WorksheetFunction.CountIf(.Range("B:B"), "J")
.Rows(behouden + 1 & ":" & lastrow).EntireRow.Delete
lastrow = behouden + 1
...
Do While i <= lastrow
If Mid(.Cells(i, 1).Value, 11, 4) = "Cod:" Then
    .Rows(i).EntireRow.Delete
    lastrow = lastrow - 1
ElseIf Mid(.Cells(i, 1).Value, 11, 4) = "Ver:" Then
    .Rows(i).EntireRow.Delete
    lastrow = lastrow - 1
ElseIf Mid(.Cells(i, 1).Value, 11, 4) = "Pre:" Then
    .Rows(i).EntireRow.Delete
    lastrow = lastrow - 1
ElseIf Mid(.Cells(i, 1).Value, 7, 2) = "SP" Then
    .Cells(i, 2).FormulaR1C1 = "=LEFT(RC1,5)"
    If Right(Trim(Mid(.Cells(i + 2, 1).Value, 15, 10)), 2) = "ST" Or Right(Trim(Mid(.Cells(i + 2, 1).Value, 15, 10)), 2) = "ML" Then
    .Cells(i, 3).FormulaR1C1 = "=SUBSTITUTE(LEFT(TRIM(MID(R[2]C1,15,10)),LEN(TRIM(MID(R[2]C1,15,10)))-2),""."","","")/1"
    ElseIf Right(Trim(Mid(.Cells(i + 2, 1).Value, 15, 10)), 1) = "G" Then
    .Cells(i, 3).FormulaR1C1 = "=SUBSTITUTE(LEFT(TRIM(MID(R[2]C1,15,10)),LEN(TRIM(MID(R[2]C1,15,10)))-1),""."","","")/1"
    Else
    .Cells(i, 3).FormulaR1C1 = "=TRIM(MID(R[2]C1,15,10))"
    End If
    .Cells(i, 4).FormulaR1C1 = "=TRIM(RIGHT(RC1,LEN(RC1)-63))"
    .Cells(i, 5).FormulaR1C1 = "=MID(R[3]C1,74,8)"
    .Cells(i, 6).FormulaR1C1 = "=MID(R[1]C1,37,6)"
    .Cells(i, 7).FormulaR1C1 = "=MID(R[1]C1,106,1)"
    .Cells(i, 8).FormulaR1C1 = "=MID(R[1]C1,100,5)"
    .Cells(i, 9).FormulaR1C1 = "=MID(R[1]C1,108,2)"
    .Cells(i, 10).FormulaR1C1 = "=(SUBSTITUTE(TRIM(MID(R[2]C1,27,9)), ""."", "","")/1)/RC3"
    .Cells(i, 10).NumberFormat = "€ 0.00"
    .Cells(i, 11).FormulaR1C1 = "=TRIM(MID(R[1]C1,79,14))"

Op deze laatste blijft ie dus de foutmelding geven. Op eerdere niet.

[Harrie]

Is het bewust dat je alle cellen met formules vult? Je kan misschien ook in VBS al het resultaat bepalen en dat dan in de cel schrijven.
Of dat gaat hangt beetje van het doel af.

[emnich]

Het ligt in elk geval niet aan Trim want die wordt in die regels er voor ook al gebruikt. Is de tekst in die cel niet gewoon te klein zodat Mid faalt?

[Lustucru]

Niks mis met regel 48. En hoewel Excel niet bugvrij is, denk ik dat de kans op een fout in jouw code groter is dan een bug in Excel. Debuggen dus. Gaat het vanaf regel 1 al mis, of gaat het pas later fout? Wordt de cel verderop in de code nog een keer gezet? Wat geeft formulalocal terug als je die direct opvraagt na regel 48?

[hihansvdijk]

Wellicht heeft het hier mee te maken. in office 365 is de functie spaties.wissen veranderd in trim
Zie post die ik indertijd heb gesteld in Microsoft Answers
(Iets wat je als leverancier m.i. nooit mag doen!!)
https://answers.microsoft...-b086-b78b9a8239cd?auth=1

[Paultje3181]

hihansvdijk schreef op zondag 28 januari 2018 @ 13:06:
Wellicht heeft het hier mee te maken. in office 365 is de functie spaties.wissen veranderd in trim
Zie post die ik indertijd heb gesteld in Microsoft Answers
(m.i. iets wat je als leverancier m.i. nooit mag doen!!)
https://answers.microsoft...-b086-b78b9a8239cd?auth=1

Dit zou best eens kunnen zijn, maar verklaard niet dat dit ook in Excel 2013 is. Daarnaast schrijft VBA in het engels, dus zou het moeten vertalen naar óf Trim óf spaties.wissen.

Wat zou kunnen, is dat er geen spaties in zitten, en dat het resultaat een getal is. Maar normaal gesproken doet ie daar dan niets mee...

[breew]

@Paultje3181

1. Wat is de waarde van lastrow?
2. Wat is de waarde van ActiveSheet.Cells(lastrow + 1, 1).Value ?

Ik ben trouwens benieuwd naar de usecase waarom je door VBA formules in een werkblad laat zetten.
Ik weet dat het kan, en soms onvermijdelijk is, maar het maakt zaken vaak nodeloos gecompliceerd (zoals nu wellicht ).

[Paultje3181]

breew schreef op maandag 29 januari 2018 @ 08:26:
@Paultje3181

1. Wat is de waarde van lastrow?
2. Wat is de waarde van ActiveSheet.Cells(lastrow + 1, 1).Value ?

Ik ben trouwens benieuwd naar de usecase waarom je door VBA formules in een werkblad laat zetten.
Ik weet dat het kan, en soms onvermijdelijk is, maar het maakt zaken vaak nodeloos gecompliceerd (zoals nu ).

lastrow = 74k+ en wordt steeds minder. Na het schrijven van de formules gaan worden de waarden gekopieerd en geplakt.

De regel lastrow +1 is leeg (en komt ie ook niet)

En ik schrijf formules omdat ik dat zo (mezelf) geleerd heb. Ben vanuit de recorder ooit begonnen en die schrijft uiteraard formules... Uiteindelijk gaat het om de waarde, dus als je een betere oplossing hebt, graag.

[breew]

breew schreef op maandag 29 januari 2018 @ 08:26:
De regel lastrow +1 is leeg (en komt ie ook niet)

wel... dat is toch wat het R[1]-gedeelte doet? De vierkante haken geven een offset ten opzichte van de huidige cel aan.

En ik schrijf formules omdat ik dat zo (mezelf) geleerd heb. Ben vanuit de recorder ooit begonnen en die schrijft uiteraard formules... Uiteindelijk gaat het om de waarde, dus als je een betere oplossing hebt, graag.

Mijn aanvlieg route bij dit soort zaken is een bereik instellen, gebaseerd op lastrow, en dan cel voor cel door dit bereik 'loopen'...

Visual Basic:

Option Explicit

Sub Probeersel()

  'declaratie
  Dim c As Range   'cel in rng
  Dim rng As Range   'bereik van A1 t/m de onderete rij met data in kolom A
  Dim lonLAatsteRij As Long   'rijnummer van onderste cel met data in kolom A
  
  'initialisatie
  With ActiveSheet
    'vind de onderste rij met data in kolom a
    lonLAatsteRij = .Cells(Rows.Count, "A").End(xlUp).Row
    'stel het berik in van cel A1 t/m de onderste rij in kolom A met data
    Set rng = .Range(.Cells(1, 1), .Cells(1, lonLAatsteRij))
  End With
  
  'loop van boven naar beneden door de cellen uit bereik 'rng'
  For Each c In rng
    'doe je ding voor elke cel/rij/...
    'hier gaat vba dingen voor je doen en invullen.
  Next c

End Sub

Om zaken overzichtelijk te houden, zou ik ook eerst de rijen wissen die je niet nodig hebt, en pas als dat helemaal gereed is, de data verder gaan bewerken. Nu doe je dit in één While-loop... Het kan uiteraard, maar het maakt het ook niet overzichtelijker

[ Voor 12% gewijzigd door breew op 29-01-2018 09:24 ]

[Paultje3181]

Jij gebruikt een for, ik gebruik een do while. Maakt dat veel verschil qua snelheid of geheugengebruik?

Mijn R[1] slaat op i, niet op lastrow. Maar het principe is inderdaad duidelijk.
Leeghalen is al gedaan, maar dan nog zijn het 74000 rijen. En iedere rij heeft data nodig van de volgende rij.

[breew]

Paultje3181 schreef op maandag 29 januari 2018 @ 11:51:
Jij gebruikt een for, ik gebruik een do while. Maakt dat veel verschil qua snelheid of geheugengebruik?

Mijn R\[1] slaat op i, niet op lastrow. Maar het principe is inderdaad duidelijk.
Leeghalen is al gedaan, maar dan nog zijn het 74000 rijen. En iedere rij heeft data nodig van de volgende rij.

Niet presé sneller, maar IMHO wel beter leesbaar en in overeenstemming met je doel.
Een Do While gebruik ik doorgaans als ik werk met condities, als ik door een vast bereik moet akkeren, vind ik een for-next beter leesbaar.

Ik vind dit een redelijke uitleg:

There are appropriate uses for the while, the for, and the foreach constructs:

while - Use this if you are iterating and the deciding factor for looping or not is based merely on a condition. In this loop construct, keeping an index is only a secondary concern; everything should be based on the condition

for - Use this if you are looping and your primary concern is the index of the array/collection/list. It is more useful to use a for if you are most likely to go through all the elements anyway, and in a particular order (e.g., going backwards through a sorted list, for example).

foreach - Use this if you merely need to go through your collection regardless of order.

Obviously there are exceptions to the above, but that's the general rule I use when deciding to use which. That being said I tend to use foreach more often.

Als je door cellen in een bereik loopt, dan kun je waarden uit de volgende rij pakken op basis van de huidige cel

.Cells(1, 1).Offset(3, 4).Value

Haalt de celwaarde uit de cel: A1 + 3 rijen omlaag + 4 kolommen naar rechts = E4

[ Voor 34% gewijzigd door breew op 29-01-2018 12:38 ]

[Paultje3181]

Ok, ik snap je punt. En voor wat betreft de formules? Zou je dan zeggen B2.value = evaluate(formule)
Of zou je dit ook anders doen?

[breew]

Paultje3181 schreef op maandag 29 januari 2018 @ 13:03:
Ok, ik snap je punt. En voor wat betreft de formules? Zou je dan zeggen B2.value = evaluate(formule)
Of zou je dit ook anders doen?

Ja en nee
achter de "=" gebruik je dan de "gewone" vba-functies als MID, LEFT, INSTR, etc.....
Uiteraard kun je EVALUATE gebruiken, maar dan ga je weer "terug" naar gebruik van worksheet-functies. Dat is soms handig, maar niet altijd.
Bijna alles kan je prima met vba-functies opvangen.

[ Voor 5% gewijzigd door breew op 29-01-2018 17:36 ]

[TomDeKikvorsman]

De opdracht Trim of Spaties wissen werkt op een andere manier dan de opdracht doet uitschijnen.
Ze wist enkel spaties tussen de tekst, indien er meer dan 1 spatie staat.
Enkele spaties laat ze ongemoeid.

Ik gebruik : Application.Substitute(Range("A1"), " ", "_")
Met in A1 de cel waar de spaties uit moeten

[ Voor 8% gewijzigd door TomDeKikvorsman op 07-02-2018 13:45 ]