combinatie van formules - rekenkundig en logisch

[Patroeschka]

Bekijk bijlage Stock 2016 test.pdf
Beste, ik heb volgende vraag over bepaalde formules.
in bijlage kan u zien hoe de opmaak is,
in kolom G staat een simpele formule die kolom E in vermindering brengt met kolom C (=C2-E2)
in kolom H staat een formule voor als de stock onder de minimumstock komt er Bestel komt te staan (=ALS(G2<M2,"Bestel"," "))

nu is mijn vraag (misschien is het iets simpel maar ik kom er niet uit):
Als er een bestelling is binnengekomen zet ik in Kolom K "OK", nu zou ik graag willen dat als ik "OK" zet, kolom J + G samengeteld worden (en de uitkomst in kolom G komt te staan), en dat kolom I, J & K terug lege velden worden (I = datum, J & K tekst)

dit is om zoveel mogelijk geautomatiseerd te werk te gaan en om fouten te vermijden.

alvast dank op voorhand

 

[Joske2000]

Hi Patroeschka,

Welkom op het forum

Wij hebben graag een excel voorbeeldje, dan moeten we niet alles gaan herbouwen.

Bedankt,

Joske

 

[Patroeschka]

Bekijk bijlage Stock 2016 test.xlsx

Beste, bij deze het excel bestand

alvast dank voor de moeite

 

[MarcelBeug]

Het zal een VBA oplossing worden waarbij het is aan te raden om hiervoor een knop te maken en niet direct te reageren op het invullen van "OK" in kolom K.
Om fouten te vermijden.

Edit: of de code zodanig maken dat je nog terug kunt.

 

[Patroeschka]

Ik ben een leek in formules van excell, en daarom zijn alle voorstellen welkom.

 

[Joske2000]

Deze code doet wat je verwacht

Sub OpboekStock()
j = Range("A" & Rows.Count).End(xlUp).Row
i = 1
Do Until i > j
    If Sheets("Blad1").Cells(i, 11).Value = "ok" Then
        Sheets("Blad1").Cells(i, 7).Value = Sheets("Blad1").Cells(i, 10) + Sheets("Blad1").Cells(i, 7).Value
        Sheets("Blad1").Range(Cells(i, 9), Cells(i, 11)).ClearContents
    End If
i = i + 1
Loop
End Sub

Groet,

Joske

 

[MarcelBeug]

Hierbij een versie die direct reageert op "OK" en nog wel om een bevestiging vraagt.
Praktisch gezien moet je OK invullen, op Enter drukken en nogmaals Enter om te bevestigen of Esc om te annuleren (of de knoppen van de popup kiezen).
Bij ontkenning of een fout bij de berekening, wordt "OK" weer weggehaald.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newStock As Long
    Dim verwerk As Variant
    If Target.Column <> 11 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If UCase(Target.Value) <> "OK" Then Exit Sub
    On Error GoTo errorhandler
    newStock = Target.Offset(0, -1) + Target.Offset(0, -4)
    On Error GoTo 0
    verwerk = MsgBox("Voorraad van artikel " & _
                Target.Offset(0, -10) & " " & _
                Target.Offset(0, -9) & " wordt " & _
                Target.Offset(0, -4) & " + " & _
                Target.Offset(0, -1) & " = " & _
                newStock, vbOKCancel, "Bevestig bijwerking voorraad")
    If verwerk = vbCancel Then
        Target.ClearContents
        Exit Sub
    Else
        Application.EnableEvents = False
        Target.Offset(0, -4) = newStock
        Range(Target.Offset(0, -2), Target).ClearContents
        Application.EnableEvents = True
    End If
        
    Exit Sub
    
errorhandler:
    MsgBox "Fout bij berekenen nieuwe voorraad."
    Target.ClearContents
    
End Sub

 

[Patroeschka]

Bedankt iedereen, het werkt perfect

 

[Patroeschka]

Beste MarcelBeug, ik heb echter dan nog één vraagje, na het uitvoeren van deze formule is de kolom G zonder formule, kan deze alsnog in de codering een formule krijgen dat kolom E nog steeds in vermindering kan worden gebracht met G.

dank bij voorbaat

 

[MarcelBeug]

Beste MarcelBeug, ik heb echter dan nog één vraagje, na het uitvoeren van deze formule is de kolom G zonder formule, kan deze alsnog in de codering een formule krijgen dat kolom E nog steeds in vermindering kan worden gebracht met G.

Dan klopt er iets niet. Ik vermoed dan, dat niet G en J moeten worden samengeteld, maar C en J, en dat de uitkomst in kolom C moet komen?

 

[Patroeschka]

Beste MarcelHeug, in de lijst begin ik (Bvb in Januari) met een beginstock (kolom C) en vandaar uit begint de originele formule van kolom G te werken (=C2-E2(wat er uit de stock gaat)), dus kolom C blijft onveranderd (het ganse jaar door = een beginwaarde), nu als er een bestelling wordt gedaan in kolom J wordt deze samengeteld met kolom G wat een nieuw getal van actuele stock geeft, maar er worden nog steeds verder artikels uit stock genomen (kolom E) en deze zou verder moeten in vermindering gebracht worden met kolom G (dit is de reële stock op het moment).
Dus in het kort:
kolom C = beginpunt dat onveranderd blijft
kolom E = wat er uit de stock genomen wordt
kolom G = de reële stock
kolom J= de bestellingen die na OK bijgeteld worden in kolom G (reële stock)

hopelijk is dit duidelijk

groetjes en dank u

 

[MarcelBeug]

hopelijk is dit duidelijk

Nee, maar dat ligt echt niet aan mij.
Voorbeeldje met getallen:
C = 18
E = 5
G = C-E (=13)
J = 10
Wat wordt dit na OK als de formule in G moet blijven staan?

 

[Patroeschka]

Wel hier komt het moeilijke gedeelte denk ik, momenteel wordt de simpele formule in G gewist (=E3-C3) en zou deze vervangen worden door een andere simpele formule in kolom G (=E3-G3)bvb, ik weet niet of het mogelijk is om in een script een formuleverandering door te voeren, daarom de vraag.
Dus G begint met de formule (=C3-E3) en zou na de OK moeten worden (=G3-E3).
dus met uw voorbeeld:
C = 18
E = 5
G = C-E (=13)
J = 10

na de OK

G = 23
G= G-E

de functie voor J blijft behouden voor de volgende bestelling

Is dat mogelijk?

 

[MarcelBeug]

Ja, het is wel mogelijk om met VBA een formuleverandering door te voeren, maar je kunt in G3 niet opnemen =G3-E3, want dat levert een kringverwijzing op.

 

[MarcelBeug]

Mijn suggestie zou zijn om een extra kolom op te nemen met alle ontvangsten tot nu toe.
De logica wordt dan: beginstock - alle verbruik + alle ontvangsten = huidige voorrraad.

 

[Patroeschka]

Beste MarcelHeug, ik heb uw eerdere suggestie overgenomen en de telling laten aanpassen na de OK, dan kan alles blijven zoals het was en is zowel de telling als actuele stock up to date, enkel moet ik nog één code toevoegen om dan de "uit stock" ook te wissen zodat deze niet direct in mindering wordt gebracht met het totaal.

nogmaals dank

 

[snb]

Een 'iets' andere benadering:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 11 Then
       Target = IIf(Target = "" And Target.Offset(, -4) >= Target.Offset(, -1), "a", "")
       If Target = "a" Then Target.Offset(, -4) = Target.Offset(, -4) - Target.Offset(, -1)
       Cancel = True
    End If
End Sub