[Excel 2003] Functies variabel plakken met visual basic

[LtMarx]

Ik probeer dus functies variabel te plakken in excel. Ik heb daarvoor al het volgende stukje script:

code:

Sub Uitbetalenrij()
Dim payrow2 As Integer
Dim teller1 As Integer
Dim teller2 As Integer
Dim payrow1 As Integer
Dim saverow As Integer
Dim laatstegeld As Integer


'dit is de laatste rij waarin staat hoeveel ik of de panatarij nog geld schuldig is
saverow = Cells(1, 9)
laatstegeld = Cells(saverow, 12)

'deze teller begint op de 100e rij in kolom 11 (rij k) en werkt naar boven totdat hij een waarde vind,
'het rijnummer van deze rijonthoudt hij
teller1 = Cells(1, 11)
While Cells(teller1, 11) = ""
teller1 = teller1 - 1
Wend
payrow1 = teller1
Cells(1, 9) = payrow1

'deze teller telt op dezelfde manier door als de vorige teller alleen met een ander beginpunt
'deze teller kijkt waar de een na laatste waarde zich bevind in de 11e rij oftewel rij k
teller2 = payrow1 - 1
While Cells(teller2, 11) = ""
teller2 = teller2 - 1
Wend
payrow2 = teller2 + 1
Cells(1, 10) = payrow2

'Beide tellers schrijven het eindresultaat weg in 2 cellen namelijk I1 (de een na laatste rij met een waarde)
'En J1, de laatste rij met een waarde, deze rijen zijn onzichtbaar omdat de tekst wit is
ActiveCell.Offset(i, 0).Formula = "=Dagen360(G22,Vandaag())"

'Nu ervoor zorgen dat de variabele SOM functie in excel alleen komt op de plekken
'naast een uitbetaling van de pantarij in rij L
Cells(1, 11) = ""

'Nu nog alleen even de laatste waarde terug zetten zodat de vorige betalingsdata niet verloren gaat
Cells(saverow, 12) = laatstegeld
End Sub

Nu probeer ik om bij regel 38 in te bouwen dat hij deze excel functie:

code:

=SOM((INDIRECT("J"&$J$1)):((INDIRECT("J"&$I$1))))-K6

plakt op de rij die door regels 14 t/m 21 wordt bepaald (de var payrow 1 dus)

Bovenstaande excel functie is compleet niet relatief op de laatste verwijzing na (die naar K6). Deze moet in de kolom K blijven en de rijen aflopen die bepaald zijn in de var payrow1

Mijn vraag is: Hoe kan ik deze functie plakken met visual basic in de cellen waarvan de kolom bekend is de rij variabel bepaald (nogmaals payrow1) is en er nog een relatieve verwijzing in de functie staat? Ik kan op een variabele manier wel cellen aanroepen maar dan alleen de waarde en niet zozeer de functie. Anders zou het geen probleem zijn.

Ik kan wel functie elementen in excel vinden en misschien zou ik daarmee een eind moeten komen maar in bovenstaande functie zitten ook nog eens de " in de weg omdat vb deze herkent als programma code

Ik hoop dat bovenstaande niet al te onduidelijk is

[coenbijlsma]

Je kunt natuurlijk de formule in de cel in een String zetten:

'Ik noem maar een voorbeeld:

code:

dim sFormula as String
sFormula = Cells(1,1).FormulaLocal

Je kunt je formule ook zelf opbouwen in code natuurlijk. Een kleine tip: de ASCII-code voor dubbele aanhalingstekens is Chr$(34)

Laat ff weten of dit je geholpen heeft.

[ Voor 4% gewijzigd door coenbijlsma op 31-01-2005 14:57 . Reden: zo is het natuurlijk duidelijker waar de code staat ;) ]

[onkl]

Idd,

Visual Basic:

Cells(1,11).FormulaLocal = "=SOM((INDIRECT(" & chr$34 & "J" & chr$34 & "&$J$1)):((INDIRECT(" & chr$34 & "J" & chr$34 & "&$I$1))))-K"& payrow1
'de payrow1 even naar eigen inzicht invullen lijkt met zo

[LtMarx]

Hmmm met die laatste formule geeft vb een syntax error ik denk zelf dat dat komt door de & tekens die er nu nog instaan en dus ook via zp'n chr$(xx) weggewerkt moet worden. Voor de rest alvast bedankt. Ik ga nu nog wel ff opzoek naar die chr set.... Als ik dat gevonden heb post ik dat ook nog wel even hier. Als jullie eerder zijn met dat weten/vinden....
Would u b so kind ???

[LtMarx]

ok heb het al gevonden chr$38 maar nu krijg ik nog steeds een syntaxis fout en ik kan maar niet ontdekken wat er fout gaat

code:

Cells(payrow1,11).FormulaLocal = "=SOM((INDIRECT(" & chr$34 & "J" & chr$34 & chr$38 & "$J$1)):((INDIRECT(" & chr$34 & "J" & chr$34 & chr$38 & "$I$1))))-K" & payrow1

Ik heb het opgelost door een loopje te maken met alle chr dingen....

ik heb inmiddels dus ook de & tekens eruit

hij ziet er nu zo uit:

code:

Cells(payrow1, 11).FormulaLocal = "=SOM((INDIRECT(" & Chr$(34) & "J" & Chr$(34) & Chr$(38) & Chr$(36) & "J" & Chr$(36) & "1)):((INDIRECT(" & Chr$(34) & "J" & Chr$(340 & Chr$(38) & Chr$(36) & "I" & Chr$(36) & "1))))-K" & payrow1)

Maar nu krijg ik de fout dat de types niet overeenkomen. De rest van het hele script is getest en werkt dus het moet aan die regel liggen....

[ Voor 53% gewijzigd door LtMarx op 28-01-2005 21:38 ]

[LtMarx]

hij doet het nu
Chr$(340 --> Chr$(40)

Daarnaast stonden er in de eerste formule van de vorige post niet overal haakjes om de getallen die de chr aangaven