=als formule in VBA?

[carloschouw]

Goedemorgen!

Vraagje: ik heb een Excel bestand met formules opgebouwd =als Bekijk bijlage TEST.xlsm

In dit voorbeeld zijn het er slechts een aantal en werkt het prima. Echter worden deze formules in werkelijkheid geplaatst in ca. >5000 cellen en dan wordt het bestand erg zwaar - traag (Excel loopt bijna vast)

Ik vroeg mij af of er misschien een andere mogelijkheid is de cellen te markeren met een "x" via b.v. VBA? Dus de =als formule gebruiken in VBA voor de betreffende cellen..

Heeft iemand een idee of een ander idee om mij te helpen?

 

[cow18]

J7

=ALS(OF($G7=1;$G7=10;$G7=30);"x";"")

 

[carloschouw]

Dank voor je snelle reactie!

Wordt met deze formule Excel minder traag? De cellen die ik dan voorzie van deze formule zijn er erg veel, zeker wanneer de cellen> 5000 er straks >10.000 worden. IK heb n.l. 500 kolommen waar ik de dagen in kan aanpassen, de formules zitten in een veelvoud aan cellen naast de kolommen. Daarom wordt Excel traag..

wanneer ik een dag aanpas moeten alle cellen 'gaan rekenen' en loopt Excel vast. Ik vermoed dat met een andere Excel formule Excel niet sneller wordt of zie ik het verkeerd misschien?

 

[SjonR]

vba:

Sub SjonR()
arr = Range("G7:P" & Range("C" & Rows.Count).End(xlUp).Row)

    For i = 1 To UBound(arr)
        If arr(i, 1) = 1 Or arr(i, 1) = 30 Or arr(i, 1) = 10 Then
            For j = 4 To 10
                arr(i, j) = "x"
            Next
        Else
            For j = 4 To 10
                arr(i, j) = ""
            Next
        End If
    Next
Cells(7, 7).Resize(UBound(arr), 10) = arr
End Sub

 

[carloschouw]

Dat ziet er top uit SjonR!

Dank voor je hulp..!

 

[carloschouw]

Hoi SjonR,

Ik heb de code in VBA gezet maar ik zie geen functionaliteit, zie bijlage de uitwerking hiervan. Weet jij wat ik verkeerd doe?

Bekijk bijlage TEST.xlsm

Hiernaast heb ik nog een vraagje: ik wil b.v. in kolom J, L en P de getallen 1, 10 en 30 gebruiken, in kolom K, N, O de getallen 1, 7, 15. Hoe kan ik dit wijzigen?

 

[SjonR]

De code zal wel door "iets" moeten worden getriggerd, in dit geval een worksheet_change event lijkt mij.

Ik zal nog ff je tweede vraagje (dit verandert alles kijken.

 

[carloschouw]

Nee, helaas. Kom er nog niet helemaal uit. Zie hierboven mijn vraag

 

[SjonR]

probeer deze eens:

Private Sub Worksheet_Change(ByVal Target As Range)

arr = Range("G7:P" & Range("C" & Rows.Count).End(xlUp).Row)

    For i = 1 To UBound(arr)
        If arr(i, 1) = 1 Then
            For j = 4 To 10
                arr(i, j) = "x"
            Next
        ElseIf arr(i, 1) = 10 Or arr(i, 1) = 30 Then
                arr(i, 4) = "x"
                arr(i, 5) = ""
                arr(i, 6) = "x"
                arr(i, 7) = ""
                arr(i, 8) = ""
                arr(i, 9) = ""
                arr(i, 10) = "x"
        ElseIf arr(i, 1) = 7 Or arr(i, 1) = 15 Then
                arr(i, 4) = ""
                arr(i, 5) = "x"
                arr(i, 6) = ""
                arr(i, 7) = "x"
                arr(i, 8) = "x"
                arr(i, 9) = "x"
                arr(i, 10) = ""
        Else
            For j = 4 To 10
                arr(i, j) = ""
            Next
        End If
    Next
Cells(7, 7).Resize(UBound(arr), 10) = arr
End Sub

 

[cow18]

ik verwacht niet veel verschil tussen je werkblad op handmatig herrekenen zetten en dat tussendoor even laten herrekenen, eventueel deels (bv. op rijniveau) en de macro nu en dan laten lopen.
Het wordt nog leuker als de formules soms per kolom gaan wijzigen.

Ik blijf voorstander om het met formules te doen en slim spelen met herrekenen.

 

[zapatr]

@Carlo,
de macro hoort
1. ofwel in een module: dan moet je steeds zelf opdracht geven om de macro uit te voeren;
2. ofwel je laat hem staan op dezelfde plaats als waar je hem geplaatst hebt, maar dan moet je er een "Private Sub Worksheet_Change (...) van maken (zie hieronder). In het laatste geval kan hij dan worden uitgevoerd telkens als er in kolom G een wijziging plaatsvindt. Dat lijkt erg praktisch, maar als als er bij elke wijziging in kolom G steeds meer dan 10.000 rijen moeten worden bijgewerkt, dat maakt het werken in het werkblad waarschijnlijk niet aangenamer. Het hangt er natuurlijk ook vanaf hoe vaak er wijzigingen plaatsvinden. Dat moet je zelf even beoordelen.

En verder: als een hele range dezelfde waarde krijgt dan hoef je die niet cel per cel langs te gaan, en als je start met een blanco tabel dan hoef je ook geen cellen blanco te maken als ze niet gelijk zijn aan 1, 10, of 30. Je kunt daarom volstaan met een korte macro.

als je optie 1 wil (in een module):

Sub macro1()
Dim lr As Long, x as long
lr = Cells(Rows.Count, 8).End(xlUp).Row
Cells(7, 10).Resize(lr, 7).ClearContents
For x = 7 To lr
Select Case Cells(x, 7).Value
Case 1, 10, 30
Cells(x, 10).Resize(, 7) = "x"
End Select
Next x
End Sub

Als je optie 2 wil (=automatisch uivoeren van macro zodra in kolom G wat wordt gewijzigd):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Dim lr As Long, x As Long
lr = Cells(Rows.Count, 8).End(xlUp).Row
Cells(7, 10).Resize(lr, 7).ClearContents
For x = 7 To lr
Select Case Cells(x, 7).Value
Case 1, 10, 30
Cells(x, 10).Resize(, 7) = "x"
End Select
Next x
End If
End Sub

Opm: regel 2 waarin ik de hele kolom beschouw, is formeel niet juist, het moet eigenlijk zijn Range(Cells(7, 7), Cells(lr, 7)), maar omdat kolom G boven rij 7 waarschijnlijk niet vaak gewijzigd zal worden, is voor het gemak de hele kolom gebruikt.

 

[carloschouw]

@SjonR,

Het werkt super! Bedankt voor je moeite en hulp.

@Cow18, Zapatr, dank voor jullie reacties. Helpt mij ook verder in het denken en uitwerken van mogelijkheden.

 

[VenA]

Ik kan mij niet voorstellen dat een simpele als formule op de paar cellen die je noemt vertragend kan werken. Zijn er geen andere boosdoeners zoals voorwaardelijke opmaak over hele kolommen/rijen in het spel?

 

[cow18]

100.000 rijen bij 7 kolommen gevuld met bovenstaande formule = 700.000 formules
Dan een macrootje willekeurig 1.000 cellen in de 7e kolom vullen met 30.
Gemiddeld klaar in 6.297 sec, dus 1 verandering is 6 milliseconde op mijn computer.

Met jouw formule was dat 6.38 sec, dus net geen 0.10 sec trager op 1000 veranderingen, verwaarloosbaar.

Ik denk dat er iets anders aan de hand is/was.
(tijdsmetingen gebeurden met een microtimer, ivm. de nauwkeurigheid)

PS. Ik zie dat VenA net voor mij dezelfde bedenkingen maakte !

 

[carloschouw]

Geen idee, het bestand is in de eerder genoemde bijlage die ik gebruik. Dit voorbeeld zijn enkele cellen, de andere vele malen groter. Aantal komt in de buurt van hetgeen wat Cow18 vermeld.

Enige wat nog in mijn werkbestand zit zijn andere formules maar die zijn niet 'gekoppeld' aan het "x" versus "frequentie in dagen". Wanneer ik enkel de dagen aanpas van b.v. 1 naar 7 dan loopt Excel zeer traag (processoren gebruik %) Bij b.v. G7 verandering van 1 naar 7 gaat Excel enkel 365 cellen nalopen en wijzigen, meer niet maar dat is wel hetgeen waardoor het zo traag wordt. Verder is er niets meer of minder aangepast. Geen idee waarom Excel dan traag wordt??

 

[zapatr]

De opmerkingen van VenA en cow 18 zijn belangrijk om mee rekening te houden, ikzelf zou ook de voorkeur geven aan formules in plaats van een macro, zelfs als die macro van mezelf was. Maar als je dan toch een macro wil gebruiken en op snelheid bent gesteld, dan zou ik toch voor de snelste macro kiezen. Ik heb de macro's van SjonR en die van mezelf vergeleken door die over 100.000 rijen te laten lopen. Welnu, die van SjonR doet er dubbel zo lang over dan die van mij. Dat is op grond van de code's ook te verwachten: bij de macro met de grootste tijd worden immers veel meer cellen doorlopen (zoals ik hierboven al probeerde duidelijk te maken in mijn opmerking "En verder...").

 

[VenA]

Altijd handig die bestandjes die maar de halve waarheid bevatten.

 

[cow18]

als het toch met een macro moet, dan is dit de meest geschikte, volgens mijn bescheiden mening

Private Sub Worksheet_Change(ByVal Target As Range)
    Set isect = Intersect(Target, Columns("G"))
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        For Each c In isect.Cells
            Select Case c.Value
                Case 1, 10, 30: s = "x"
                Case Else: s = "-"
            End Select
            c.Offset(, 3).Resize(, 7).Value = s
        Next
        Application.EnableEvents = False
    End If
End Sub

0.07 sec op 1000 veranderingen

 

[HSV]

Bart, Jammer dat je code het maar een keer doet en dat de voorwaarden zijn gewijzigd in #6.

 

[cow18]

HSV, je hebt gelijk de 2e enable moest true zijn
Ik kijk er anders vanavond nog een keer naar.