Kolommen toevoegen met header en formule

[Bertjens48]

Hallo.

Ik ben momenteel bezig met het opstellen van een voorraad bestand. Onderdeel van dit bestand zijn de tellijsten (huidige voorraad) en bijbehorende financiële cijfers. Als het een maandag telling betreft wil ik dat deze gegevens worden toegevoegd aan een tabel.

Als er 'Ja' wordt geselecteerd bij de tellijst dienen drie kolommen toegevoegd te worden aan de tabel 'financieel'. De headers zijn voorraad, waarde en verbruik waarbij het weeknr. dynamisch zal zijn. Met onderstaande code werkt alles zoals ik het wil, het lukt me echter niet om formules toe te voegen aan de kolommen.

Formules die ik wil toevoegen:
Voorraad: Waardes overnemen uit tellijst (index-match)
Waarde: Voorraad * waarde product (index-match uit data blad)
Verbruik: afwijking tussen vorige telling en laatste telling.

Daarna zou ik bovenstaande willen plakken als waarde.

Hoe kan ik dit het beste doen?

    'Maandag tellingen worden ook in Financieel verwerkt.
    If Sheets("Tellijst").Range("F1") = "Ja" Then
        Dim lst As ListObject
        Dim currentSht As Worksheet
        
        Sheets("Financieel").Select
        Set currentSht = ActiveWorkbook.Sheets("Financieel")
        Set lst = ActiveSheet.ListObjects("Financieel")

        'below is the code that I would like to have looped
        lst.ListColumns.Add.Name = "Voorraad w" & [Tellijst!c2]
        lst.ListColumns.Add.Name = "Waarde w" & [Tellijst!c2]
        lst.ListColumns.Add.Name = "Verbruik w" & [Tellijst!c2]

Alvast bedankt

 

[VenA]

Al best vaak bekeken en nog geen reactie betekent vaak dat de vraag niet begrepen wordt. Je plaatst een halve code en geen voorbeeldbestand dan wordt het allemaal een beetje gokken wat het worden moet. Als ik gegevens wil toevoegen aan een tabel dan zorg ik er eerst voor dat de opzet van de tabel goed is. Data voeg je dan toe aan de rijen in een tabel. Gebruik bij voorkeur datums ipv weeknummers.

 

[Bertjens48]

Dan gaan we het nogmaals proberen. In de bijlage een versimpelde versie van mijn bestand.

Zoals aangegeven wordt er tweemaal per week geteld. Deze tellijst wordt ingevuld in het tabblad 'Tellijst'. Als de lijst volledig is ingevuld wil ik volgende stappen via macro laten uitvoeren:

Stap 1: via index/match de getelde aantallen verwerken in voorraad. Dit is gelukt en werkt naar behoren, echter in het aangepast bestand niet meer.

Stap 2: Als het een maandag telling is moeten de volgende stappen uitgevoerd worden. Als 'Ja' Wordt geselecteerd bij de Tellijst moet deze stap plaats vinden.
- Drie nieuwe kolommen aanmaken in tabblad 'Financieel', namelijk: Voorraad (getelde aantallen), Waarde(getelde aantallen * inkoopprijs) en verbruik (externe bron). Deze stap is ook gelukt.
- Formules in deze kolommen plaatsen: Dit zullen grotendeels index/match formules zijn.
- Het geheel als waardes 'inplakken'

Stap 3 Als alles verwerkt is het tabblad opslaan als 'Tellijst (datum)'

Mijn volledige code

Private Sub CommandButton2_Click()
    'Aantallen in Overzicht plakken
    Sheets("Voorraad").Select
    Sheets("Voorraad").Range("Voorraad[Voorraad]").FormulaR1C1 = _
        "=INDEX(Tellijst,MATCH([Item Nr.],Tellijst[Art Nr],0),9)"
    With Sheets("Voorraad").Range("Voorraad[Voorraad]")
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Sheets("Tellijst").Select
    
    'Maandag tellingen worden ook in Financieel verwerkt voor overzicht.
    If Sheets("Tellijst").Range("F1") = "Ja" Then
        Dim lst As ListObject
        Dim currentSht As Worksheet
        
        Sheets("Financieel").Select
        Set currentSht = ActiveWorkbook.Sheets("Financieel")
        Set lst = ActiveSheet.ListObjects("Financieel")

        'below is the code that I would like to have looped
        lst.ListColumns.Add.Name = "Voorraad w" & [Tellijst!c2]
        lst.ListColumns.Add.Name = "Waarde w" & [Tellijst!c2]
        lst.ListColumns.Add.Name = "Verbruik w" & [Tellijst!c2]

    With Sheets("Voorraad").Range("Voorraad[Voorraad]")
        .Copy
        .PasteSpecial xlPasteValues
    End With
    
    End If
    
End Sub

 

[VenA]

Plaats een bestand zonder dat overal #REF staat. Kolom I in de tab 'Tellijst' schijnt van belang te zijn maar is nu leeg. Moet dan ook de waarden in de voorraad leeggemaakt worden? Ipv formules kan je het ook rechtstreeks vanuit VBA doen. Bv

Sub VenA()
  ar = Sheets("Tellijst").ListObjects(1).DataBodyRange
  For j = 1 To UBound(ar)
    If ar(j, 1) <> "" Then Sheets("Voorraad").Cells(Application.Match(ar(j, 1), Sheets("Voorraad").Columns(2), 0), 9).Value = ar(j, 9)
  Next j
End Sub

 

[Bertjens48]

Hoi VenA

In de bijlage het nieuwe document. De code die ik had geschreven werkt weer naar behoren.

De code die je zojuist hebt geplaatst werkt inderdaad ook naar behoren. Deze code is wat langzamer omdat deze de formule per cel uit voert, maar dat is eenvoudig op te lossen.

Stap twee wil ik dus nog verder uit werken.
Drie kolommen aanmaken met dynamische headers is gelukt. Nu moeten nog formules toegevoegd worden aan deze kolommen.

Als de tellingen zijn verwerkt in het tabblad 'Voorraad & 'Financieel' (mits het maandag is) dan wil ik het tabblad opslaan als 'tellijst (datum)' waarna de hele lijst leeggemaakt kan worden.

Alvast bedankt, dit geeft weer stof tot puzzelen.


Bekijk bijlage Voorraad Grondstoffen - Helpmij.xlsm