Excel script werkt niet goed

[zetje01]

Goedemiddag,

Ik heb hier nog nooit wat gepost (ben ook nieuw op tweakers ) en weet niet zeker of dit het juiste subforum is.

Mijn vraag:
Ik heb in Excel in kolom A enkele tientallen cellen met het volgende format: AB-CDEFGH-IJ
In kolom B wil ik het eerste streepje/minteken/- verwijderd hebben uit de cellen van Kolom A.
(in rij 1 staan wat headers; die doen niet mee. Ik begin dus in A2 en B2)

Dus met Formule "Substitueren" aan de gang: In B2 zet ik =SUBSTITUEREN(A2; "-"; ""; 1)
Dat lukt prima!
Nu trek ik cel B2 naar beneden, en keurig krijg ik in kolom B alle cellen van kolom A, en dan zonder het minteken
Heel mooi (wat ben ik toch slim/handig) !

Maar...
Nu wilde ik van deze actie een script maken (om te automatiseren).
Dus ik ga het opnemen.
En als ik het script opnieuw run (op een schoon nieuw blad) DOET 'IE HET NATUURLIJK NIET!

Dit is de tekst in het script:
// Set range B2 on selectedSheet
selectedSheet.getRange("B2").setFormulaLocal("=SUBSTITUTE(A2,\"-\",\" \",1)");

En dit is wat de script-editor zegt:
Line 8: Range setFormulaLocal: Het argument is ongeldig, ontbreekt of heeft een onjuiste indeling.

Maar graag wil ik het wel met script werkend krijgen.

Wat ik al gedaan heb:
Verschillende dagen vele uren gegoogled... en ik kom er niet uit.

Iemand enig idee?

[DeKever]

Ik gebruikte vroeger nog wel eens worksheetfunction.substitute(...).

De range ophalen vanuit worksheet, bewerken in geheugen middels vba (worksheetfunction) bijvoorbeeld met een for loop en vervolgens resultaat in worksheet verwerken.

Er zijn vast veel elegantere methoden, maar het werkt prima.

[skate master]

Je kunt ook de functie "snel aanvullen" gebruiken.
Dan vul je in kolom B 1x handmatig in hoe je de gegevens wilt weergeven en drukt dan op Ctrl+e Excel zoekt dan zelf de juiste weergave voor de overige gegevens.

Snel aanvullen gebruiken in Excel - Microsoft Ondersteuning.

[zetje01]

skate master schreef op zaterdag 15 juni 2024 @ 08:59:
Je kunt ook de functie "snel aanvullen" gebruiken.
Dan vul je in kolom B 1x handmatig in hoe je de gegevens wilt weergeven en drukt dan op Ctrl+e Excel zoekt dan zelf de juiste weergave voor de overige gegevens.

Snel aanvullen gebruiken in Excel - Microsoft Ondersteuning.

HHmm..
Dat is waarschijnlijk hetzelfde als op B1 gaan staan, en dan de formule (als de dikke Plus zichtbaar is) naar beneden trekken?

[skate master]

zetje01 schreef op zaterdag 15 juni 2024 @ 09:29:
[...]

HHmm..
Dat is waarschijnlijk hetzelfde als op B1 gaan staan, en dan de formule (als de dikke Plus zichtbaar is) naar beneden trekken?

Heb je het geprobeerd?

Nee dat is niet hetzelfde.

[zetje01]

skate master schreef op zaterdag 15 juni 2024 @ 10:04:
[...]

Heb je het geprobeerd?

Nee dat is niet hetzelfde.

Oh,
Nee ik heb het nog niet geprobeerd, maar dat ga ik zeker doen.
En kijken of ik het ook "op kan nemen" zodat mijn collega's enkel het script hoeven te draaien.

[xminator]

Heb je Chatgpt al gebruikt?

[RobIII]

Waar hoort mijn topic?
Softwareontwikkeling -> Client software algemeen

zetje01 schreef op vrijdag 14 juni 2024 @ 18:35:
Dus met Formule "Substitueren" aan de gang: In B2 zet ik =SUBSTITUEREN(A2; "-"; ""; 1)
[...]
Dit is de tekst in het script:
// Set range B2 on selectedSheet
selectedSheet.getRange("B2").setFormulaLocal("=SUBSTITUTE(A2,\"-\",\" \",1)");

En dit is wat de script-editor zegt:
Line 8: Range setFormulaLocal: Het argument is ongeldig, ontbreekt of heeft een onjuiste indeling.

Je hebt nu komma's in je substitute commando ipv puntkomma's

[ Voor 88% gewijzigd door RobIII op 15-06-2024 11:40 ]

[F_J_K]

Het probleem is inderdaad waarschijnlijk de taal: Excel zelf 'praat' Nederlands, VBA 'praat' Engels. Zie ook SUBSTITUEREN versus SUBSTITUTE.

zetje01 schreef op zaterdag 15 juni 2024 @ 10:12:
Nee ik heb het nog niet geprobeerd, maar dat ga ik zeker doen.
En kijken of ik het ook "op kan nemen" zodat mijn collega's enkel het script hoeven te draaien.

Wel even opletten dat je je collega's niet aanleert dat het okay is om waarschuwingen over VBA-code weg te klikken. Risico op malware.

xminator schreef op zaterdag 15 juni 2024 @ 10:30:
Heb je Chatgpt al gebruikt?

offtopic:
Nog even los van de vraag of chatgpt etc goede code opleveren: hoe is dat een relevante reactie?

[dix-neuf]

Als de naam van het blad "Blad1" is:

code:

Sub Vervang()
With Sheets("Blad1")
For x = 2 To .Range("a" & .Rows.Count).End(xlUp).Row
.Range("B" & x).Value = WorksheetFunction.Substitute(.Range("A" & x).Value, "-", "", 1)
Next x
End With
End Sub

[RobIII]

F_J_K schreef op zaterdag 15 juni 2024 @ 11:10:
Het probleem is inderdaad waarschijnlijk de taal: Excel zelf 'praat' Nederlands, VBA 'praat' Engels. Zie ook SUBSTITUEREN versus SUBSTITUTE.

Dat heeft TS toch goed? De foutmelding is dan ook: "Het argument is ongeldig..." (dan is even de vraag of dat argument slaat op 't argument van setFormulaLocal of op 't argument van SUBSTITUTE, maar ik gok 't laatste).

[ Voor 7% gewijzigd door RobIII op 15-06-2024 11:43 ]

[zetje01]

- Ik snap de 'verwarring' over taal wel: Ik denk dat we op werk een Engelse Office hebben, met een Nederlands taalpakket eroverheen. Die bovenaan staat in de Opties.

- Ik gebruik in Excel het commando 'substitieren' met puntcomma's (wat achterlijk om syntax óók te laten afhangen van de taal) en bij het opnemen maakt Excel er SUBSTITUTE (met comma's van in het script... wat ik kan bekijken en bewerken.)

- Ben ook met vervangen(replace) aan de gang geweest, eerst.

- Ik snap niet:
- dat als ik het handmatig doe, dat het dan wél werkt, en in het opgenomen script níet
- de backslashes in ("=SUBSTITUTE(A2,\"-\",\" \",1)"); maar dat zal dan wel kloppende programmeertaal zijn.
- via Google ook bezig geweest met andere notering: i.p.v. A2 de notering C1R2

[zetje01]

F_J_K schreef op zaterdag 15 juni 2024 @ 11:10:
Het probleem is inderdaad waarschijnlijk de taal: Excel zelf 'praat' Nederlands, VBA 'praat' Engels. Zie ook SUBSTITUEREN versus SUBSTITUTE.


[...]

Wel even opletten dat je je collega's niet aanleert dat het okay is om waarschuwingen over VBA-code weg te klikken. Risico op malware.

Ja, is een goed tip. Zeker waar ík werk.
Maar het gekke is: ikzelf heb helemaal geen waarschuwingen gekregen.
Misschien omdat ik geen Macro gebruik, maar enkel een opgenomen script?

[zetje01]

dix-neuf schreef op zaterdag 15 juni 2024 @ 11:13:
Als de naam van het blad "Blad1" is:

code:

Sub Vervang()
With Sheets("Blad1")
For x = 2 To .Range("a" & .Rows.Count).End(xlUp).Row
.Range("B" & x).Value = WorksheetFunction.Substitute(.Range("A" & x).Value, "-", "", 1)
Next x
End With
End Sub

Hier moet ik even over nadenken.
Dit is zeker iets wat ik in een Macro moet doen?

EDiT:
Of kan ik dit aan één van mijn bestaande, halfwerkende scripts toevoegen

[ Voor 8% gewijzigd door zetje01 op 15-06-2024 12:56 ]

[RobIII]

zetje01 schreef op zaterdag 15 juni 2024 @ 12:09:
=- Ik snap niet:
- de backslashes in ("=SUBSTITUTE(A2,\"-\",\" \",1)"); maar dat zal dan wel kloppende programmeertaal zijn.

Dat is om de " te escapen.
Heb je al eens geprobeerd de komma's te vervangen door puntkomma's? Ik denk (nu) niet dat 't helpt; ik was me niet bewust dat dit (óók) afhankelijk is van je locale maar jeweetnooit

[zetje01]

RobIII schreef op zaterdag 15 juni 2024 @ 12:18:
[...]

Dat is om de " te escapen.
Heb je al eens geprobeerd de komma's te vervangen door puntkomma's? Ik denk (nu) niet dat 't helpt; ik was me niet bewust dat dit (óók) afhankelijk is van je locale maar jeweetnooit

Ja, ben best ook wel in het (opgenomen) script aan het editten geweest en syntaxen veranderd.
Oh, ik begon bij script nummer 1 een paar weken geleden, en zit nu op script nummer 35.
Het is dus een dingetje wat ik steeds, tussendoor, af en toe, een paar uurtjes mee bezig ben
(bedankt voor de escape link; is me duidelijk.)

Wél leerzaam.... geef me nog een paar weken, en dan kan ik júllie helpen


EDiT:
'computerproblemen' google ik waarschijnlijk deze hele eeuw al standaard in het Engels.
Zelf merk ik dat niet, maar word er soms op gewezen door anderen die met me meekijken.

[ Voor 16% gewijzigd door zetje01 op 15-06-2024 12:35 ]

[xminator]

F_J_K schreef op zaterdag 15 juni 2024 @ 11:10:

offtopic:
Nog even los van de vraag of chatgpt etc goede code opleveren: hoe is dat een relevante reactie?

Omdat als je dit in chatgpt gooit je een reactie krijgt wat je kan proberen als code en daarna nog andere wensen kan laten toevoegen aan je code en dit direct dan kan gebruiken?

Als je dan een uitkomst krijgt wat je niet zint geef je dit terug en wordt de code weer aangepast? Zeker voor zulke vraagstukken werkt chatgpt uitstekend.

[dix-neuf]

zetje01 schreef op zaterdag 15 juni 2024 @ 12:13:
Hier moet ik even over nadenken.
Dit is zeker iets wat ik in een Macro moet doen?

Dat IS een macro, waar je niks anders mee moet doen dan in een module plaatsen en hem daarna uitvoeren.
Niets aan wijzigen, tenzij de bladnaam als die naam niet Blad1 is.

[Reptile209]

zetje01 schreef op zaterdag 15 juni 2024 @ 12:09:
[...] (wat achterlijk om syntax óók te laten afhangen van de taal) [...]

Ja en nee. Binnen Excel wordt het decimaalscheidingsteken van Windows en/of zoals ingesteld in Excel gebruikt. Default bij Nederlandse instelling is dus een komma.

Maar dan moet je dus wel een ander scheidingsteken in je formules gaan gebruiken, anders wordt het dubbelzinnig of je met Foo(1,2) een functie met 2 hele getallen als parameters aanroept, of eentje waarin je maar één floating-point parameter hebt. En daarom is dus gekozen voor de puntkomma, op zich ook niet onlogisch. Dan is dat onderscheid veel makkelijker. Maar het is een keuze, en eentje die bloed irritant is als je met meerdere taal versies werkt...

* Reptile209 heeft ook een mening over het vertalen van de functienamen in Excel

[zetje01]

dix-neuf schreef op zaterdag 15 juni 2024 @ 13:20:
[...]

Dat IS een macro, waar je niks anders mee moet doen dan in een module plaatsen en hem daarna uitvoeren.
Niets aan wijzigen, tenzij de bladnaam als die naam niet Blad1 is.

Dank je; ik kom er op terug.

(ik zit nl. met allerlei problemen: Werkt Macro überhaupt wel op onze computers? Het is niet voor mij maar voor collega's! Er moet veel meer met het excelblad gebeuren en dat deed ik al met script-opname (maar het blijkt dat je ook met Macro kan opnemen) Ik weet niet of mijn macro wel makkelijk op andermans computer te gebruiken is (het is hier allemaal flink beveiligd). Enzovoort.)

[Reptile209]

zetje01 schreef op zaterdag 15 juni 2024 @ 15:14:
[...]

Dank je; ik kom er op terug.

(ik zit nl. met allerlei problemen: Werkt Macro überhaupt wel op onze computers? Het is niet voor mij maar voor collega's! Er moet veel meer met het excelblad gebeuren en dat deed ik al met script-opname (maar het blijkt dat je ook met Macro kan opnemen) Ik weet niet of mijn macro wel makkelijk op andermans computer te gebruiken is (het is hier allemaal flink beveiligd). Enzovoort.)

Je kan ook (bijna) alles wat je in een macro kan doen, met formules in cellen oplossen. Desnoods met gebruik van wat hulp cellen of -kolommen die je later kan verbergen.

[zetje01]

Reptile209 schreef op zaterdag 15 juni 2024 @ 15:21:
[...]

Je kan ook (bijna) alles wat je in een macro kan doen, met formules in cellen oplossen. Desnoods met gebruik van wat hulp cellen of -kolommen die je later kan verbergen.

Hoe krijgen mijn collega's die formules in die cellen zonder script of macro?

[RobIII]

Door de sheet aan te leveren mét de formules of een template te gebruiken

[sypie]

zetje01 schreef op zaterdag 15 juni 2024 @ 09:29:
[...]
…naar beneden trekken?

Probeer de volgende keer ook eens te dubbelklikken in plaats van naar beneden te trekken. Dat werkt nog sneller...

[zetje01]

Ik ben er uit!

Of nee, ik ben Macro's gaan gebruiken en mijn acties op die manier gaan opnemen.
Daarin werkt "substitueren" wel gewoon zonder vreemde foutmeldingen..

@dix-neuf
Doordat jij over Macro's begon, ben ik daar mee aan de gang gegaan.
Ook (achteraf pas) jouw code getest. Die werkt mooier dan mijn substitueren commando's.
Want als ik mijn B-Kolom kopieer en in een leeg excel-sheet plak, krijg ik een kolom met foutmeldingen, want dan heb ik een kolom met substitueren-commando's in een lege sheet, die z'n informatie uit een A-kolom moet halen.. die er niet is.
Jouw code genereert wél keurig de cijfers en letters ... als tekst ... en daar valt dus beter mee te werken.
Ongetwijfeld ga ik later nog eens proberen jouw code in mijn macro te integreren... maar nu even niet, want ik ben wel effe klaar met dit projectje.
Wel oprecht bedankt!

[zetje01]

@sypie Sorry, niet gelukt.


.

.

Iedereen bedankt voor de aandacht en het meedenken en de suggesties!

.

.

Mag ik nog de groeten doen aan mijn tante Annie? Oh... dat mag niet.
Dan mag dit topic wel dicht... Oh nee, dat doen we niet.

[ Voor 20% gewijzigd door zetje01 op 16-06-2024 21:32 ]

[sypie]

zetje01 schreef op zondag 16 juni 2024 @ 21:25:
@sypie Sorry, niet gelukt.

Hier, ter inspiratie. De tip die ik gaf zit aan het eind van dit filmpje.
YouTube: Snelle Korte Tip: 3x dubbelklikken op een cel