Excel VBA absolute tekst omzetten in een formule

[Reignfall]

Beste,

Reeds ben ik bezig om in excel (middels een formule of VBA) een manier te vinden om een absolute formule te hervormen naar een formule, echter wil dit niet lukken.

VB:

in Tab1, Cel A1 staat "8+3"

nu is het de bedoeling middels een formule of een macro de uitkomst van de formule in een andere tab komt te staan

Ofwel:Tab2, Cel A1 wordt "11" weergegeven.

Middels de excelformule Indirect kwam ik er niet uit en met VBA loop ik vast, ik gebruik de VBA-code:
Sheets("Boeking").Range("D315000").Formula = "=" & Sheets("Formules").Range("I30").Value

Waarin Range D15000 nog wordt vervangen door Last Row, echter gaat het nu om het idee.

Kan iemand mij hiermee helpen?

Alvast bedankt.

Groeten,

 

[ExcelAmateur]

Als je de link even goed doorlees, dan weet je hoe je de code dient te plaatsen.
Dan krijg je geen smiley te zien.
https://www.helpmij.nl/forum/announcement.php?f=5

Je kunt het in je vraag nog aanpassen.

 

[SjonR]

Probeer hier eens wat mee:

Range("B1").Value = "=" & Range("A1").Value

 

[Reignfall]

Probeer hier eens wat mee:

Range("B1").Value = "=" & Range("A1").Value

@SjonR

dank voor uw reactie, echter is dit niet de oplossing.

1. De formule Range("D315000").Formula = "=" & Range("I30").Value doet het prima zolang er maar verwezen wordt naar cellen in dezelfde tab, het is de bedoeling dat hij verwijst naar de sheet("Boeking"), vanuit Sheet("Formules"). Het lijkt fout te gaan wanneer ik de verwijzing naar de andere tab in de regel zet.
2. Uw VBA-regel zegt dat de waarde in een bepaalde cel als waarde wordt gekopieerd, echter is het de bedoeling dat het een formule wordt. aldus denk ik dat het Range("B1").Formula moet zijn, indien dit niet het geval is mag je mij hierop wijzen.

 

[Reignfall]

Sub test()
    Sheets("Boeking").Range("D3:D15000").Formula = "=Formules!I30"
    Sheets("Boeking").Range("D3:D15000").Formula = "=Formules!$I$30"    'vast adres !
End Sub

Dank voor uw reactie, echter geeft dit niet het gewenste resultaat.

Indien ik deze sub in een macro zet, geeft hij als resultaat de waarde "8+3" ipv de uitkomst van de formule.

 

[jkpieterse]

Even een stapje terug.
Waarom wil je dit? waarom staan die getallen niet ieder in hun eigen cel?

 

[Reignfall]

Fout 1004 tijdens uitvoering

Indien ik de volgende sub uitvoer als er in tab "Formules" Range I30 de formule 8+3 staat, doet de macro wat hij moet doen.

Sheets("Boeking").Range("D315000").Formula = "=" & Sheets("Formules").Range("I30")

Echter als ik een wat complexere formule in cel I30 zet, geeft excel de volgende melding:

Fout 1004 tijdens uitvoering:

Door de toepassing of door object gedefinieerde fout

Weet iemand wat hiermee wordt bedoeld en hoe te veranderen in de bovenstaande formule?

 

[Reignfall]

Even een stapje terug.
Waarom wil je dit? waarom staan die getallen niet ieder in hun eigen cel?

Ik heb een Tab waar alle formules in staan, deze formules zijn opgebouwd uit een hoop cellen gekoppeld met een & teken. uiteindelijk zullen er aanpassingen gedaan worden in een deel van de formule (een deel wat tussen && tekens zal staan), indien dit wordt gedaan moet ik nu de hele formule kopieren en plakken in een andere tab, daarna een = teken ervoor zetten en naar beneden trekken.

Ik heb meerdere van deze formules dus deze handeling zal ik dan vaker moeten doen indien er iets aan wordt gepast. Nu wil ik middels een excelformule de uitkomst van deze lange formule plaatsen in de tab "Boeking" zonder dat ik dit moet kopieren en plakken etc. de formule Indirect komt naar mijn weten het dichtstbij maar geeft niet het gewenste resultaat. Daarom probeer ik het middels VBA. Ik wil een knop zodat ik in 1 klik deze formule update in de tab "Boeking".

Ik hoop dat het verhaal een beetje helder overkomt, indien er vragen zijn hoor ik het graag.

 

[Reignfall]

zie bijlage, van alles iets

edit; als je over "complexere" formules praat, dan zou ik toch even achterover leunen en denken aan een iets andere insteek.
Hoe complex is complex ? een voorbeeldje ...

Complex is misschien niet het goede begrip, een hoop "als" formules achter elkaar. ik zal een van de formules hieronder posten:

als(Boeking!$D3="";"";als.fout(als(Specificatie!$B$1=5;RECHTS(Specificatie!$K9;2)&"M"&LINKS(RECHTS(Specificatie!K9;6);2)&" "&SUBSTITUEREN(Specificatie!F9;"-";"");als(Specificatie!$B$1=2;TEKST(Specificatie!$I9;"jj")&"M"&TEKST(Specificatie!$I9;"mm")&" "&Specificatie!$AH9;als(Specificatie!$B$1=3;TEKST(DATUM(Specificatie!H9;Specificatie!I9;DAG(VANDAAG()));"jj")&"M"&TEKST(DATUM(Specificatie!H9;Specificatie!I9;DAG(VANDAAG()));"MM")&" "&Specificatie!N9;als(Specificatie!$B$1=4;TEKST(Specificatie!$H9;"jj")&"M"&TEKST(Specificatie!$H9;"mm")&" "&Specificatie!$C9;als(Specificatie!$B$1=6;Specificatie!$Z$6&" "&"TGG";als(Specificatie!$B$1=7;"";als(Specificatie!$B$1=8;"";als(Specificatie!$B$1=9;Specificatie!$Z$6;als(Specificatie!$B$1=10;TEKST(RECHTS(Specificatie!Q9;10);"jj")&"M"&TEKST(RECHTS(Specificatie!Q9;10);"mm");als(Specificatie!$B$1=11;"";als(Specificatie!$B$1=12;Specificatie!$Z$6&" "&"Eneco";als(Specificatie!$B$1=13;TEKST(Specificatie!I9;"jj")&"M"&TEKST(Specificatie!I9;"mm")&" Lloyd's";als(Specificatie!$B$1=14;TEKST(Specificatie!H9;"jj")&"M"&TEKST(Specificatie!H9;"mm");als(Specificatie!$B$1=15;Specificatie!$Z$6;als(Specificatie!$B$1=16;Specificatie!$Z$6&" "&Formules!$B$72;als(Specificatie!$B$1=17;Specificatie!$Z$6&" "&Formules!$B$73;als(Specificatie!$B$1=18;$B$67&" "&Specificatie!$Z$6;als(Specificatie!$B$1=19;Specificatie!$Z$6&" "&Formules!$B$75;als(Specificatie!$B$1=20;"";als(Specificatie!$B$1=21;TEKST(DATUM(JAAR(Specificatie!H9);MAAND(Specificatie!H9);DAG(Specificatie!H9));"jj")&"M"&TEKST(DATUM(JAAR(Specificatie!H9);MAAND(Specificatie!H9);DAG(Specificatie!H9));"mm")&" "&Formules!$B$77;als(Specificatie!$B$1=22;TEKST(Specificatie!$Q9;"jj")&"M"&tekst(specificatie!$Q9;"mm")&" "&Formules!$B$78;als(Specificatie!$B$1=0;""))))))))))))))))))))));""))

Ik snap dat je er niet veel wijzen van wordt, echter heb ik het toegevoegd als voorbeeld.

In ieder geval bedankt voor de evalueer functie, deze kende ik nog niet Hij werkt wel als er 8+3 staat maar bij de formule hierboven krijg ik een foutmelding

 

[VenA]

Even het document plaatsen lukt niet?

 

[Reignfall]

het ziet er enorm stoer uit, maar net daarom ben ik niet onder de indruk.
Hoe wil je dergelijke formules uberhaupt onderhouden ?
Ik zou voor zoiets een UDF (user defined function) schrijven, die heel goed documenteren en je bent vertrokken.
In mijn vorige bijdrage stond er overigens al zo'n UDF, nl. die Evalueer-functie.

Dank voor uw reactie.

Het feit dat ik die formule in de post plak is niet om "stoer" te doen, dit is omdat je ernaar vroeg.

Ook snap ik dat het sneller en efficiënter kan echter kon ik 3 maanden terug max de =som functie. Ik vind het leuk om hiermee bezig te zijn en ben gaandeweg aan het leren hoe alles werkt.

Ik zal mij eens inlezen in de user defined functions en hoop wellicht via die weg een antwoord te vinden op de vraag. Het onderhouden zal wellicht lastig worden en ik zal ook zeker tegen meer problemen aan lopen maar daar leer ik dan alleen maar van. Verder zijn er meerdere wegen naar Rome en omdat mijn kennis nog niet zo ver reikt heb ik voor deze weg gekozen.

Wellicht kunt u mij op weg helpen bij het maken van zo'n udf? indien dit naar het gewenste resultaat zal leiden.

 

[Reignfall]

Even het document plaatsen lukt niet?

Bijgevoegd vind u een voorbeeld bestand met dezelfde fout.

Alvast bedankt voor het meedenken

 

[jkpieterse]

Ik snap nog steeds niet waarom je nou deze methode gebruikt om formules als tekst op te bouwen, vooral niet gezien het gegeven dat invoegen van een rij of kolom (of verwijderen) op je tabje specificatie ervoor zal zorgen dat je formules onzin opleveren omdat de celverwijzingen zich niet aanpassen. Een eerste probleem is in ieder geval dat je .FormulaLocal moet gebruiken in plaats van .Formula (want die spreekt "Amerikaans" en wil dus een Engelse formule hebben met engelse schedingstekens). Wat mij op het volgende probleem brengt: als iemand zijn Excel op Engels zet dan werkt FormulaLocal mogelijk weer niet. Redenen genoeg om formules niet als tekst te bewaren, of je moet ze helemaal in de US syntax doen.

 

[VenA]

ipv een UDF een formule? Of klopt er niet zoveel van het voorbeeldbestand?

=VLOOKUP(specificatie!$A$1;Formules!$E$3:$G$14;3;0)

 

[Reignfall]

dit was wel heel simpel, 1 parameter

Dank voor uw reactie, het bijgevoegde bestand ziet er mooi uit echter geeft het nog niet het gewenste resultaat. De UDF geeft altijd een vaste waarde als resultaat. In het voorbeeld staat een tekst maar in het werkelijke bestand staat daar een formule als waarde, het is dus de bedoeling dat de uitkomst van de formule uit de UDF komt en indien ik deze UDF toepas, krijg ik de uitkomst als waarde (ofwel de formule als tekst). Ik heb een nieuw voorbeeld bestand toegevoegd om dit beter te beschrijven.

 

[Reignfall]

ipv een UDF een formule? Of klopt er niet zoveel van het voorbeeldbestand?

=VLOOKUP(specificatie!$A$1;Formules!$E$3:$G$14;3;0)

Dank voor uw reactie, echter levert deze formule niet het gewenste resultaat. Deze formule geeft het resultaat als waarde weer (wat niet goed in het voorbeeld beschreven stond, want daar staat een tekst tussen haakjes ipv een formule). De tekst tussen haakjes is dus eigenlijke en formule en alle subformules samen vormt de langere formule die onderin de tab Formules staat. Voor een aangepast voorbeeld, zie mijn bericht hierboven.

 

[Reignfall]

Ik snap nog steeds niet waarom je nou deze methode gebruikt om formules als tekst op te bouwen, vooral niet gezien het gegeven dat invoegen van een rij of kolom (of verwijderen) op je tabje specificatie ervoor zal zorgen dat je formules onzin opleveren omdat de celverwijzingen zich niet aanpassen. Een eerste probleem is in ieder geval dat je .FormulaLocal moet gebruiken in plaats van .Formula (want die spreekt "Amerikaans" en wil dus een Engelse formule hebben met engelse schedingstekens). Wat mij op het volgende probleem brengt: als iemand zijn Excel op Engels zet dan werkt FormulaLocal mogelijk weer niet. Redenen genoeg om formules niet als tekst te bewaren, of je moet ze helemaal in de US syntax doen.

Dank voor uw reactie en het meedenken. Ik doe het via deze weg omdat ik het nog niet middels een andere weg kan. De tab "specificatie" wordt echter gedeeltelijk beveiligd waardoor men niks kan toevoegen of verwijderen.

 

[jkpieterse]

Ik begrijp absoluut niet waarom je dit zou willen, maar deze routine werkt bij mij:

Sub UpdateFormule()

    Sheets("Boeking").Range("D3:D1000").FormulaLocal = "=" & Sheets("Formules").Range("H16").Value

End Sub

 

[Reignfall]

Ik begrijp absoluut niet waarom je dit zou willen, maar deze routine werkt bij mij:

Sub UpdateFormule()

    Sheets("Boeking").Range("D3:D1000").FormulaLocal = "=" & Sheets("Formules").Range("H16").Value

End Sub

Beste jkpieterse,

Dank voor uw antwoord, ik heb de macro toegepast en het lijkt te werken.

De reden is wat lastig uit te leggen maar de vraag is bij dezen beantwoord.

Eenieder bedankt voor het meedenken, ik zal deze topic sluiten.

Groeten,

 

[jkpieterse]

Terechte koudwatervrees zou ik zeggen. UDF's zijn zelden een echt goede oplossing, want:
- Traag
- Lastig te onderhouden tenzij je altijd beschikking hebt over iemand met gedegen VBA kennis
- Werken alleen op desktop Excel