Invoer cel aanpassen dmv VBA

[koster1984]

Hallo,

Ik probeer met deze code, als ik bijvoorbeeld in D2 1,001 heb staan en in D3 1 invoer, te zorgen dat hij daar dan 1,002 (invoer + aantal van [invoer afgerond naar heel getal] / 1000) van maakt:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [D2:D21]) Is Nothing Then
    If Target = "" Then Exit Sub
        Target.Offset(0, 1) = WorksheetFunction.RoundDown(Target, 0)
        Target = Target.Offset(0, 1) + (WorksheetFunction.CountIf([E2:E21], Target.Offset(0, 1)) / 1000)
    End If
End Sub

Hier gebeurt iets vreemds (ps. ik heb ook nog gebruik van een hulp kolom moeten maken, waar ik het getal afrond naar beneden, en daar wil ik eigenlijk ook vanaf), ik krijg in principe wel het resultaat maar dan krijg ik een 'fout 28 - Onvoldoende stackruimte'. Heel Excel gaat ook tekeer ervan, ik maak vast ergens een stomme fout.. maar weet iemand welke?

 

[koster1984]

Niemand?

 

[Niels28]

Geduld is een schone zaak.

Voeg aan het begin het volgende toe.

application.enableevents = false

Dit voorkomt dat je macro blijft rekenen omdat je telkens door de macro iets in het opgegeven bereik verandert.

Niels

 

[koster1984]

Bedankt Niels, dat werkt.. (Ja sorry, ik heb dit eigenlijk vanavond nog werkend nodig).

Maar heb jij (of iemand anders) nog een oplossing voor die hulpkolom die ik moest gebruiken? Of überhaupt een betere code?

 

[Roncancio]

Als je Application.EnableEvents = False gebruikt moet je niet vergeten om ook weer Application.EnableEvents = True te gebruiken.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, [D2:D21]) Is Nothing Then
    If Target = "" Then Exit Sub
    Target = Target + (WorksheetFunction.CountIf([E2:E21], Target) / 1000)
End If
Application.EnableEvents = True
End Sub

Met vriendelijke groet,


Roncancio

 

[koster1984]

Dag Ron,

Dank voor je reactie.. echter was dat al logisch genoeg om duidelijk te zijn.
Maar weet jij (of iemand) dan misschien een betere code? Dat is het enige wat ik nog nodig heb... (ik wil van die hulpkolom af, anders kan ik het net zo goed met een gewone formule doen).

Gr,
Daniel

 

[Roncancio]

Bij deze.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, [D2:D21]) Is Nothing Then
    If Target = "" Then Exit Sub
    Target = Target + Evaluate("= SumProduct((D2:D21" & ">=" & Target & ") * (D2:D21" & "<=" & Target + 1 & "))") / 1000
End If
Application.EnableEvents = True
End Sub

Met vriendelijke groet,


Roncancio

 

[koster1984]

Dat is wat ik voor ogen had, Ron duizend maal dank!
Ik zet de boel op 'Opgelost'.

Gr,
Daniel

 

[snb]

Het verwarrende aan je code is, dat het lijkt alsof je met getallen werk terwijl je dat niet doet.
Mijn voorkeur zou zijn om tekst te gebruiken:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Or Intersect(Target, [D2:D21]) Is Nothing Then Exit Sub
    If Target.Value = "" Or Val(Target) = 0 Then Exit Sub
    
    Application.EnableEvents = False
    Target = Val(Target) & "_" & Format(2 + UBound(Filter(Application.Transpose(Range("D2:D12")), Val(Target) & "_")), "000")
    Application.EnableEvents = True
End Sub

 

[koster1984]

Hoi snb,

Ik gebruik in het daadwerkelijke bestand een rang functie om ze op volgorde van dit nummer in een ander blad te tonen, met een tekst doet hij dat niet. Als ik in je code het '_' teken verander naar een komma krijg ik een punt (dus als ik in het voorbeeldbestandje, met mijn aanpassing van jouw code, een 1 zet in D13 krijg ik als resultaat 1.005.. ofwel duizendvijf).

Hoe zou jij het dan doen?

Gr,
Daniel

Ps. Wat doet 'UBound' eigenlijk?

 

[koster1984]

@ snb

Gezien jouw code sneller lijkt dan die van Ron en ik sowieso wel geïnteresseerd ben in jouw methode heb ik deze in ieder al aan weten te passen naar het volgende:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Or Intersect(Target, [D2:D21]) Is Nothing Then Exit Sub
    If Target.Value = "" Or Val(Target) = 0 Then Exit Sub    
    Application.EnableEvents = False
    Target = Val(Target) + (2 + UBound(Filter(Application.Transpose(Range("D2:D21")), Val(Target) & ","))) / 1000
    Application.EnableEvents = True
End Sub

Hierdoor is het probleem van mijn vorige post aan jou verholpen en is het weer het getal zoals ik hem nodig heb.
Echter gebeurt er weer iets anders verkeerd (ook in jouw originele code):
De code telt (of filtert) in alle cellen zowel het hele getal als slechts het eerste getal vóór de komma, dus als ik bijvoorbeeld eerst een 12 invoer (welke netjes in 12,001 veranderd) en in de cel eronder een 2 dan telt hij die 2 van de 12 er ook bij op (en krijg ik i.p.v. 2,001 dus 2,002).

Heb je misschien ook een oplossing daarvoor?

Alvast bedankt.

Gr,
Daniel

 

[snb]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Or Intersect(Target, [D2:D21]) Is Nothing Then Exit Sub
    If Target.Value = "" Or Val(Target) = 0 Then Exit Sub
    
    Application.EnableEvents = False
    Target = Int(Target) + [sumproduct((int(D2:D21)=offset(D1,counta(D1:D100),0))*1)] * 10 ^ -3
    Application.EnableEvents = True
End Sub

 

[koster1984]

Beste snb,

Als ik nu iets in D1 heb staan telt hij af i.p.v. bij, ik kan dat ook niet aanpassen zonder dat het fout gaat.. ik snap de code niet helemaal. Kan je misschien een beetje uitleg geven over het roodgedrukte stukje:

    Target = Int(Target) + [sumproduct((int(D2:D21)=[COLOR="#FF0000"]offset(D1,counta(D1:D100),0))*1)] * 10 ^ -3[/COLOR]

Gr,
Daniel