[excel] [vba] bedragen blijven maar text

[Bzzje]

Ik heb in excel een vba lopen die op een database een sql statement uitvoert en die vervolgens in een worksheet plaatst.
nu zit tussen die gegevens een kolom (kolom "I") die bedragen bevat.
Deze bedragen worden gezien als ruwe tekst.

Het maakt niet uit of ik handmatig of via vba de formatting op currency of number zet, het blijft hetzelfde.

code:

ActiveSheet.Columns("I").NumberFormat = "#,##0.00_-"

als ik daarentegen op de cell dubbelklik, de cell vervolgens verlaat, ziet hij het wel als verandering en maakt hij er wel cijfer/bedrag van.

Wanneer ik de waarde van de cell met 1 vermenigvuldig,

code:

ActiveSheet.Range("I7") = ActiveSheet.Range("I7") * 1

Dan ziet hij het wel als een bedrag.
Dit lukt me niet met een hele kolom
Maar in dat geval zou ik dan dus iedere keer de rij moeten laten doorlopen en per cell met 1 vermenigvuldigen; da's niet echt werkzaam.

zowel via google als via GOT ben ik niet verder gekomen dan ik nu beschreven heb.

edit: whoops, volgens mij hoort dit eigenlijk in Software Algemeen

[ Voor 7% gewijzigd door Bzzje op 26-09-2003 14:51 ]

[Verwijderd]

Je zou wel eens een probleem kunnen hebben met de zogenaamde local settings. Excel hanteert intern altijd het formaat 0.00 maar indien je bijvoorbeeld de NL versie gebruikt, is de interface ingesteld op 0,00 en het 'leuke' is dan dat bijv. de importfuncties hierdoor radicaal in de fout gaan. Ik los dat meestal op door alle getallen door een conversiefunctie te halen die inmiddels zelfs in onze standaard lib zit.

[BtM909]

En als je het volgende gebruikt:

code:

    Columns("I:I").Select
    Selection.NumberFormat = "#,##0.00_-"

[ Voor 12% gewijzigd door BtM909 op 26-09-2003 15:15 ]

[Bzzje]

1: Zijn niet de local settings, wanneer ik de waarde niet uitlees uit die sql, pakt hij het wel prima.

2: doet precies hetzelfde. Hij veranderd de format wel, maar voert die wijziging pas door zodra ik in die cell geweest ben door te klikken.

[Verwijderd]

Zet 'voor de grap' de waarden eens om naar local, dus 0,00? Wat je bescchrijft is namelijk exact wat ik bedoel.

[Rataplan]

Ik denk niet dat dit iets met localization te maken heeft. Het volgende fenomeen zou dan namelijk niet mogen optreden:

Bzzje schreef op 26 September 2003 @ 14:49:
als ik daarentegen op de cell dubbelklik, de cell vervolgens verlaat, ziet hij het wel als verandering en maakt hij er wel cijfer/bedrag van.

Als ik een kolom waarden uit een externe source in excel zet, plaats ik er meestal (gemakzucht) een kolom naast met =VALUE(TRIM(#ref)).

Eventueel zou je dat ook met je vb kunnen oplossen: waarde = Val(Trim(waarde)) should do the trick.

edit:

En dan vergeet ik nog te vermelden dat ik meestal de thousand separator, indien aanwezig, d'r ook even uit-SUBSTITUTE. Maar of dat nodig is hangt van je query af.

[ Voor 13% gewijzigd door Rataplan op 26-09-2003 15:26 ]

[Bzzje]

beiden zelfde resultaat...

[Bzzje]

die =value werkt idd wel
maar dan nog zou ik dat dus per record langs moeten lopen

[Rataplan]

Bzzje schreef op 26 september 2003 @ 15:33:
die =value werkt idd wel
maar dan nog zou ik dat dus per record langs moeten lopen

Ja zeg, je kopieert die formule gewoon naar alle cellen in de kolom, klaar ben je

[Bzzje]

nou... nee... want het is maar een onderdeel in een procesje.
Mensen moeten met een druk op de knop die lijst kunnen zien en met een andere knop daar direct een pivottable uit kunnen trekken. en dan moeten ze niet eerst handmatig die formule toepassen

[Rataplan]

Bzzje schreef op 26 September 2003 @ 15:36:
Mensen moeten met een druk op de knop die lijst kunnen zien (...)

Rataplan schreef op 26 September 2003 @ 15:25:
(...) zou je dat ook met je vb kunnen oplossen: waarde = Val(Trim(waarde)) should do the trick (...)

Dan ben je er toch al? Wat mis ik?

[Bzzje]

omdat hij dan nu per cell in de kolom I de waarde moet uitlezen, trimmen, en weer terugzetten.
Dat leek/lijkt mij een nogal omslachtige methode. Het gaat namelijk om een behoorlijke waslijst

[Rataplan]

Bzzje schreef op 26 September 2003 @ 15:52:
omdat hij dan nu per cell in de kolom I de waarde moet uitlezen, trimmen, en weer terugzetten.
Dat leek/lijkt mij een nogal omslachtige methode. Het gaat namelijk om een behoorlijke waslijst

Uit je topicstart:

Bzzje schreef op 26 September 2003 @ 14:49:
Ik heb in excel een vba lopen die op een database een sql statement uitvoert en die vervolgens in een worksheet plaatst.

Pas die routine aan en je data komt de eerste keer correct in je worksheet.
Klaar is klara

edit:
@F_J_K: wedden dat 'ie zo naar P&W gaat?

[ Voor 6% gewijzigd door Rataplan op 26-09-2003 16:02 ]

[F_J_K]

WOS --> SA

[Bzzje]

was het maar zo simpel:

code:

Set rs = db.OpenRecordset("lookupcomp")
numberOfRows = Sheets("Bron").Cells(2, 1).CopyFromRecordset(rs)

waarbij lookupcomp dan de sql statement is om het op te roepen.
hier kan ik dus niet per cell wat aangeven

[Rataplan]

Bzzje schreef op 26 september 2003 @ 16:02:
was het maar zo simpel:

code:

Set rs = db.OpenRecordset("lookupcomp")
numberOfRows = Sheets("Bron").Cells(2, 1).CopyFromRecordset(rs)

waarbij lookupcomp dan de sql statement is om het op te roepen.
hier kan ik dus niet per cell wat aangeven

Nou, daaronder komt dan iets als

Visual Basic:

For teller = 1 To numberOfRows
  Sheets("Bron").Cells(teller, 9) = Val(Trim(Sheets("Bron").Cells(teller, 9)))
Next teller

[ Voor 28% gewijzigd door Rataplan op 26-09-2003 16:11 . Reden: tweede edit: nou moet 'ie het doen :)) ]

[Bzzje]

okay... dat werkt, nu eens kijken hoeveel tijd dat duurt met grote waslijst.

iig thanx so far

[Rataplan]

Bzzje schreef op 26 september 2003 @ 16:15:
okay... dat werkt, nu eens kijken hoeveel tijd dat duurt met grote waslijst.

Het is sneller dan met de hand Als het nog sneller moet, zal je waarschijnlijk het beste je query kunnen aanpassen.

[ Voor 5% gewijzigd door Rataplan op 26-09-2003 16:19 . Reden: nognognog ]

[Bzzje]

valt nog best wel mee met die traagheid...
ik hou het hier wel ff op

[Bzzje]

Zo.... weer even een omhoogschop.

Sheets("actBron").Cells(teller, 9) = CCur(Sheets("actBron").Cells(teller, 9))

werkt.
als ik = Val() gebruik, dan pakt hij de bedragen achter de komma niet mee.
Probleem is nu dat het voor kan komen dat mensen text in het veld hebben ingevuld. Helaas kan ik dat niet voorkomen en dan klapt hij er uit met een foutmelding omdat hij hem niet kan converteren.

Nu vind ik dat prima dat hij hem niet converteert, maar ik heb dan wel graag dat hij doorgaat met de volgende cell
(en natuurlijk moet hij dan wel een melding geven dat hij X niet heeft kunnen converteren, maar da's van later zorg)

hoe los ik dat op?
on error is daar een beetje te globaal voor lijkt me

[Rataplan]

Bzzje schreef op 10 december 2003 @ 11:59:
Zo.... weer even een omhoogschop.

Sheets("actBron").Cells(teller, 9) = CCur(Sheets("actBron").Cells(teller, 9))

werkt.
als ik = Val() gebruik, dan pakt hij de bedragen achter de komma niet mee.

Ik denk dat dat een komma/punt-probleem is. Even met (worksheet) substitute of (vba) replace aan de gang gaan om alle komma's in punten te veranderen - of omgekeerd natuurlijk

Probleem is nu dat het voor kan komen dat mensen text in het veld hebben ingevuld. Helaas kan ik dat niet voorkomen en dan klapt hij er uit met een foutmelding omdat hij hem niet kan converteren.

Nu vind ik dat prima dat hij hem niet converteert, maar ik heb dan wel graag dat hij doorgaat met de volgende cell
(en natuurlijk moet hij dan wel een melding geven dat hij X niet heeft kunnen converteren, maar da's van later zorg)

hoe los ik dat op?
on error is daar een beetje te globaal voor lijkt me

On error lijkt mij daar nou eens uitstekend voor geschikt Waarom zou je iets anders moeten hebben?

[Bzzje]

dat , . is het niet
het gaat om nu de komma 5,50 en niet om 5.000
in excel zelf herkend hij die komma namelijk wel en zet hij hem wel goed
alleen in het vba stuk niet

[Rataplan]

Bzzje schreef op 10 december 2003 @ 13:58:
dat , . is het niet
het gaat om nu de komma 5,50 en niet om 5.000
in excel zelf herkend hij die komma namelijk wel en zet hij hem wel goed
alleen in het vba stuk niet

Ik zou je graag geloven, maar weet je het *heel* zeker? Excel heeft er een handje van om zelf beslissingen te nemen ("dit zal wel een komma moeten zijn") terwijl vba bijna geen enkele conversie uit zichzelf doet. Als er komma's in de database gebruikt worden: zet er eens een waarde met een punt in, en post ff wat dat oplevert.

Daarnaast geeft mijn helpfile een fixed format voor CCur, waarbij de decimal point ook daadwerkelijk een punt is. Dat zou ruzie kunnen krijgen met je Nederlandse Excel.

[Bzzje]

damndamndamn....

hij knalde er uit door een verwijzing naar de verkeerde sheet.
hij werkt nu weer
bedankt voor de suggesties