Macro gezocht "Als numberformat van cel is ..., dan...."

[0Anje0]

Ik heb in het werkblad ‘allebestanden’ een uitdraai van TreeSize staan met alle bestanden op een gezamenlijke schijf. (zie bijlage voor voorbeeldbestand)

In kolom C staat de bestandsgrootte, maar deze staat niet goed. Je ziet bij de waarden KB, MB en GB in de kolom staan. Dit is omdat bij de celeigenschappen een tekststring is toegevoegd , #,##0,0 “KB” bijvoorbeeld.
Maar de numerieke waarde in de cel is dus niet in verhouding, een cel waar “150 KB” in staat, heeft net zo’n numerieke waarde als een cel waar “150 Bytes” in staat.
Dat is natuurlijk onhandig, want nu kan ik niet sorteren op bestandsvolume of uitrekenen wat het totale bestandsvolume is van bestanden met bepaalde eigenschappen. Dus ik wil graag alles omzetten naar MB.
(ik kan niet een nieuwe treesize export maken, ik heb daar de rechten niet voor en de persoon die het kan is op vakantie)
Nou is mijn VBA kennis beperkt en ook een beetje roestig…. Maar zou je een macro kunnen maken die dit doet:

Als numberformat van cel in kolom C = #,##0,0 “KB” , geef dan in kolom D de waarde van die cel /1000
Als numberformat van cel in kolom C = #,##0,0 “MB” , geef dan in kolom D de waarde van die cel
Als numberformat van cel in kolom C = #,##0,0 “Bytes” , geef dan in kolom D de waarde van die cel /1000000
Als numberformat van cel in kolom C = #,##0,0 “GB” , geef dan in kolom D de waarde van die cel *1000

 

[VenA]

Met een UDF

Function VenA(r As Range)
    ar = Split("Bytes KB MB GB")
    x = Split(r.Text)
    VenA = x(0) * (10 ^ (-6 + (Application.Match(x(1), ar, 0) - 1) * 3))
End Function

 

[roeljongman]

ik kwam ook op een udf..
iets andere benadering en mijn precize inborst vond ook dat de exacte waarde gebruikt moest worden dus 1kb = 1024 bytes.. 1 MB = 1048576 bytes

Function SizeMB(cl As Range)

    If cl.NumberFormat = "#,##0 ""Bytes""" Then
        tmp = cl / 1048576
    ElseIf cl.NumberFormat = "#,##0.0 ""KB""" Then
        tmp = cl / 1024
    ElseIf cl.NumberFormat = "#,##0.0 ""GB""" Then
        tmp = cl * 1024
    End If
    
    SizeMB = tmp

End Function

Celopmaak nog even op MB zetten en maak aantal decimalen 3 dus #.##0,000 MB want met maar 1 decimaal worden heel veel regels anders met 0,0 MB weergegeven.. dat staat wat vreemd

 

[VenA]

@roeljongman, rekenkundig correct maar volgens mij mis je 1 voorwaarde.

 

[roeljongman]

nee geen missende voorwaarde maar wel een onhandig foutje in de functie, had geen tmp variable moeten introduceren, nu worden MB waarden niet herkend.
maar als ik tmp door cl vervang gaat het wel goed.

MB is bewust weggelaten omdat die niet wordt omgerekend maar dan moet ik wel overal cl blijven gebruiken

Function SizeMB(cl As Range)

    If cl.NumberFormat = "#,##0 ""Bytes""" Then
        cl = cl / 1048576
    ElseIf cl.NumberFormat = "#,##0.0 ""KB""" Then
        cl = cl / 1024
    ElseIf cl.NumberFormat = "#,##0.0 ""GB""" Then
        cl = cl * 1024
    End If
    
    SizeMB = cl

End Function

 

[0Anje0]

Bedankt beiden! De functie van VenA snapte ik niet helemaal, maar vind ik nog wel interessant om nog even uit te zoeken. Maar m'n doel is bereikt: de functie van Roel werkt :d
Ik weet alleen niet zeker welke factor (1000 of 1024) TreeSize uitspuugt in de rapportage. Dat vind ik altijd erg verwarrend. Ga ik nog even uitzoeken (ik heb ook totalen van de hele schijf).

 

[0Anje0]

Ik had nog iets te vroeg gejuicht. De eerste code van roel gaf inderdaad een waarde van 0 bij de regels die al in MB stonden.
Maar de tweede code gaf de regels in MB wél terug, maar gaf een foutmelding #WAARDE bij de andere regels.

Ik heb daarom de eerste code aangepast, een ElseIf voor de MB-regels toegevoegd:

Function SizeMB(cl As Range)

    If cl.NumberFormat = "#,##0 ""Bytes""" Then
        tmp = cl / 1048576
    ElseIf cl.NumberFormat = "#,##0.0 ""KB""" Then
        tmp = cl / 1024
    ElseIf cl.NumberFormat = "#,##0.0 ""GB""" Then
        tmp = cl * 1024
    ElseIf cl.NumberFormat = "#,##0.0 ""MB""" Then
        tmp = cl
    End If
    
    SizeMB = tmp

End Function

Nu lijkt -ie het wel te doen. Nu nog aan het stoeien hoe ik het 20.000 + regels kan doortrekken zonder dat excel vastloopt :shocked:Maar daar maak ik , als ik er niet uitkom- wel een nieuw topic voor aan.

 

[roeljongman]

pff toch weer teveel aan het klooien geweest met de code zonder goed te testen..
heb ik wel eens vaker last van als ik denk dat het eenvoudig is.

 

[VenA]

Welke applicatie gebruik je? Als ik deze gebruik https://customers.jam-software.de/downloadTrialProcess.php dan kan ik instellen hoe de data weergegeven moet worden.

 

[VenA]

En toen bleef het stil. Graag gedaan hoor.