Hjälp med Excel makro - matplanering

[a-son]

Ett litet tips

Tittar på din Sort-rutin, där använder du

 ActiveWorkbook.Worksheets("Ingredienser")

som referens till ett blad, kanske du fått det från en inspelning av kod?

 

Nåja, bättre är att referera till bladet med VBA-namnet

shIngr.ListObjects("Ingredients")

och egentligen skriva

Sub SortIngred()
'
' SortTabell Makro

    With shIngred.ListObjects("Ingredients")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.HeaderRowRange.Cells(1, 1), SortOn:=xlSortOnValues, Order:= _
            xlAscending, DataOption:=xlSortNormal
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

exempelvis.

 

Tittat lite på det här nu,

 

Har jag fattat rätt,

om man använder:

With shIngred.ListObjects("Ingredients")

så hänvisar man till sheet SHingred och tabell "Ingredients"

 

och då gäller "with" på rader som börjar på punkt tex.

.Sort.SortFields.Clear

är samma sak som

shIngred.ListObjects("Ingredients").Sort.SortFields.Clear

 

och 

Key:=.HeaderRowRange.Cells(1, 1)

är första rad,kolumn i tabellen "Ingredients"

 

och när man refererar så, kan man flytta tabellen på samma blad utan att macro påverkas.

[Monshi]

Precis.

With-satser ger att man referera till det objekt som anges genom att börja referensen men en punkt.

 

Likaså, att använda namnen i VBA ger att koden alltid hamnar rätt.

Man kan även, om man nu tror att det skulle kunna ske, även använda kolumnens namn, i tabellen, för att kunna nå rätt kolumn varje gång.

Dock använder man då just kolumnens namn och det kan ju ändras enkelt av användaren...

[a-son]

har googlat lite men inget bra svar,

om jag vill peka på en kolumn i en tabell i en formel, hur ska det var då?

har gjort så här nu, (Recept!C:C)

men borde väl gå att använda sej av [Dishes] och kolumn nr eller något sånt.

[Monshi]

Här har du några av egenskaperna i en tabell

 With Me.ListObjects(1)
        Debug.Print .ListColumns("Värde").Range.Address
        Debug.Print .ListColumns("Värde").Index
        Debug.Print .HeaderRowRange.Address
 End With

[a-son]

Nu börjar vi komma närmare slutet på månaden,

 

Hur ska man enklast göra övergången fån full lista till tom?

för det kommer ju bli lite krock då, när man måste ha veckans planering kvar, men samtidigt börja på nästa.

 

Sen kanske det vore skoj att spara historiken, om man vill kolla tex hur ofta man äter vissa rätter eller när man åt det senast,

 

det är ju som sagt bara extra funktioner, men det första är nog lite viktigare att lösa,

Om man skapar ett till blad, eller en till tabell längre ner på Matplan,

Skulle man då enkelt kunna kopiera över innehållet från tabell 2 till tabell 1 om dom ser exakt likadan ut?

[Monshi]

Du kan göra som har lust. jag skulle säga fyll bara på i dina lista. Du kan sortera om den så att du har sista datum högst upp istället för tvärtom, sorteringen spelar ingen roll.

Eller applicera ett filter på den, filtrerar bort månader du inte vill se, år du inte vill se.

Eller spara undan, kopiera, dina data i en ny tabell på annat blad där du efterhand fyller på.

 

Statistik, basera en pivotabell på din matplanering/historik, datum och maträtt som radettiketter, gruppera datum efter månad, kvartal eller liknande, sätt även maträtt om värdefält och sätt det till antal...

Ett exempel.

[a-son]

Var inte så du ide du hade med att utöka tabellen eftersom.

 

På matplan har jag lagt till macro för att lägga in ny vecka med datum,

Går det att förenkla, eller göra på smidigare sätt?

Sub AddRows()
'
    Application.ScreenUpdating = False
    
    With shPlan.ListObjects("Plan")
        .ListRows.Add (1)
        .ListRows.Add (1)
        .ListRows.Add (1)
        .ListRows.Add (1)
        .ListRows.Add (1)
        .ListRows.Add (1)
        .ListRows.Add (1)
    End With
    Range("b13:b14").Select
    Selection.AutoFill Destination:=Range("B6:B14"), Type:=xlFillDefault
        Application.ScreenUpdating = True
End Sub

Och filter mellan 2 datum ser ut så här:

Sub Matplan_datum()
    With shPlan.ListObjects("Plan")
    .Range.AutoFilter field:=1, Criteria1:=">=" & [MatplanStart], Operator:=xlAnd, Criteria2:="<=" & [MatplanStopp]
    End With
End Sub

Skulle man kunna få filtret att gå hela tiden, så man inte behöver trycka på knappen?

Matplanering_6.zip

[Monshi]

Ny vecka.

Ogillar Select-satser,  man bör undvika dem. Och direkt adressering är aldrig fel. Samt samma kod sju gånger, nääää.

Sub AddRows()
    Application.ScreenUpdating = False
    Dim i As Integer
    With shPlan.ListObjects("Plan")
        For i = 1 To 7
            .ListRows.Add (1)
        Next i
        .DataBodyRange.Resize(2, 1).Offset(7).AutoFill _
            Destination:=.DataBodyRange.Resize(9, 1), Type:=xlFillDefault
    End With
    
        Application.ScreenUpdating = True
End Sub

Den tabellen, städa lite i villkorsformateringen. Den blir lite förvirrad när du arrangerar om i tabellen. Radera alla extra och fixa till referensen på den som finns kvar.

Dumt att man inte kan använda tabellnamn i formateringsregelen men det går iofs att gå runt via namn om man vill.

 

Filter? Alltid aktivt? Du kan få det att verka som det genom att fyra av det efter du lagt till nya veckor exempelvis eller när bladet aktiveras.

[a-son]

Tackar igen, visste att jag kunde göra det på bättre sätt än det jag fick till..

 

Har läst lite om formateringen,

när man infogar nya rader så här, så ärver raderna formateringen från första raden, dvs rubrikerna och det blir inte snyggt

 

hittade det här, och då är det lika igen, hur använder man sej av dess om man vill  göra som tidigare     With shPlan.ListObjects("Plan")

        With ActiveSheet.Range(ActiveSheet.Cells(row, 1), ActiveSheet.Cells(row, 20))
            .Interior.Color = vbRed
            .Font.Bold = False
            .Style = "Bad"
            .Name = "Calibri"
            .Size = 10
        End With

[Monshi]

Det att tabellen ärver från första raden, bara att formatera om tabellen en gång så minns den. Den är helt enkelt inte helt säker på sin formatering, därav detta.

 

Vad menar du med gör med som tidigare?

Kanske

With shPlan.ListObjects("Plan").DataBodyRange

?

[a-son]

menar du att alla rader ska ha samma format, även första?

 

Jag vill ha det formatet på första raden, 

och resten som det är, (om det inte är något konstigt där)

 

tänkte mej att jag ska sätta rätt format på infogade rader med hjälp av dessa

om det är möjligt,

            .Interior.Color = vbRed
            .Font.Bold = False
            .Style = "Bad"
            .Name = "Calibri"
            .Size = 10

[Monshi]

Okej, du vill ha en viss färg på första raderna? Det kan sätta via VBA om du vill eller villkorsstyrd formatering. Det senare exemplvis om du vill markera de närmsta sju dagarna eller liknande eller bara det sju första raderna.

 

Men om vi ska ta det via VBA:

With Me.ListObjects(1)
        With .DataBodyRange.Resize(WorksheetFunction.Min(.DataBodyRange.Rows.Count, 7))
           .Interior.Color = vbRed
            With .Font
                .Bold = False
                .Name = "Calibri"
                .Size = 10
            End With
        End With
    End With

kan fungera...

[a-son]

Fick till formateringen tack vare dina tips sist,

Har lagt till en kolumn på matplaneringen för lite information som sedan visas på veckomenyn

tex om något ska förberedas dagen före eller så.

 

Har ett konstigt fenomen,

på Inköp bladet och man har kryssat i alla 3 rutorna och där efter beräknar,

så blir inköps beräkningen inte rätt, gör man om beräkningen så blir det rätt.

det har kommit efter jag gjorde ett huvudmacro som anropar dom 3 olika macrona,

vad kan det bero på?

 

Sedan på fliken matplan så har jag ett filter för start stopp datum,

skulle vilja ha en droplista lika som för inköp, fast här vill jag ha hela datum kolumnen som start datum, och stopp datum ska vara från start datum till sista datum i kolumnen.

är det möjligt på något enkelt sätt?

Matplanering.zip

[Monshi]

På veckosidan, ändra formeln till en koncis formel, exempelvis

=OMFEL(OM([@Datum]=B7;LETARAD([@Datum];Plan;2;FALSKT);OM(LETARAD([@Datum];Plan;6;FALSKT)="";"";"Information"));"")

så har du samma formel i hela tabellen. (och varför dropdown i den tabellen?)

 

 

Ditt fenomen, helt kopplat till att du kör alla valen? Inte ena specifikt som triggar det?

 

Filter på matplan, duger det inte att hantera autofiltret? Vill du ha egna rutor som sedan i sin ur styr autofiltret? Eller kanske en knapp som utökar till att visa en vecka till.

Annars enkelt att styra i autofiltret.

Fast vill du ha en dropown, bara att en lista med datum och formatera cellerna så att de visar det du vill.

[a-son]

Vet när dropdown kom till, är borta nu, 

den formeln funkar bra.

Har inte kommit på avd som går galet ibland när jag skapar inhandlingslistan,

funderar om det kan vara data beroende, har excel 2013 på en äldre dator, och 2010 på en nyare

får testa mej fram lite.

 

tittade på vilkordstyrdformatering på planeringen, och där var det lite konstigheter,

hade kommit in flera formateringar där, rätade till det så det blev rätt igen,

men såg på recept fliken att jag inte använde samma formel för vilkordstyrdformatering 

utan tittade på en dold kolumn för att styra formateringen

så jag ändrade den här som är för planeringen

=OM(D6="";10;OMFEL(PASSA(D6;IngredDishes;0);0))=0 

till

=OMFEL(PASSA(DC6;IngredDishes;0);0)=0

Och efter det så går det inte att öppna filen, nu funkar det med min dolda kolumn

men vill ju optimera det lite.

Vad kan det vara som spökar?

 

Matplanering_12 är filen som funkar med min gamla formel

och Matplanering är ovanstående inlagd som kraschar

MealPlan.zip

[Monshi]

Kan bara instämma, matplanering.xlsb kraschar.

Om du har data i den som du vill få tag på kan dessa gå att rädda via  LibreOffice calc exempelvis.

 

Jag gissar att det är något med villkorsstyrda formateringen. Har råkat ut för liknande någon gång vill jag minnas. Någon konflikt som ej går att lösa kanske.

 

Dessa områdesindelningar av formateringsreglerna, brukar du radera celler/rader inom tabellen? Kan inte se hur de uppkommer annars.

Om du flyttar ut regeln till kolumn bredvid tabellen blir iofs själva formateringsregeln enklare men du kommer fortfarande få dessa områden om du raderar celler.

Fast notera att det är alla tre maträtterna som söks, alla tre behöver varsin kolumn för kontroll.

Kanske testa dela upp formateringsregeln som start? Dvs skapa en regel för huvudrätt, en för tillbehör 1 och en för tillbehör 2.

[a-son]

Ska titta närmare på det där om formateringsreglerna,

det är mycket nu som man vill pyssla med så det här går på sparlåga.

 

men skulle iallafall vilja få till det här nu, jag infogar ny vecka med macro,

men den nya veckan blir inte dataverifiering - lista med.

Sub NyaRader()
    Application.ScreenUpdating = False
    Dim i As Integer
    With shPlan.ListObjects("Plan")
        For i = 1 To 7
            .ListRows.Add (1)
        Next i
        .DataBodyRange.Resize(2, 1).Offset(7).AutoFill _
            Destination:=.DataBodyRange.Resize(9, 1), Type:=xlFillDefault
            With .DataBodyRange.Resize(WorksheetFunction.Min(.DataBodyRange.Rows.Count, 7))
            With .Font
                .Color = RGB(91, 86, 77)
                .Bold = False
                .Name = "Calibri"
                .Size = 10
            End With
        End With
    End With
       Application.ScreenUpdating = True
End Sub

håller på att labba med det här:

Sub Test1()

    With shPlan.ListObjects("Plan")
        With .DataBodyRange.Resize(7, 5).Offset(0)
            With .Validation
            .Delete
            .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:="=DishSelection"
            End With
        End With
    End With
       Application.ScreenUpdating = True
End Sub

och det funkar bra, men vill egentligen bara ska ske i kollumn 3-5 i dom 7 första raderna,

hur fixar man det?

[Monshi]

Om du vill att valideringsregeln ska stanna kvar, då får du infoga dina rader mellan existerande rader i tabellen, inom dess definitionsområde så att säga.

 

Men men, inget svårt att applicera regeln via VBA. Bara sju första raderna?

Ett sätt:

With shPlan.Range("Plan[[Huvudrätt]:[Information]]")
        With .Validation
            .Delete
        End With
        With .Resize(7).Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=DishSelection"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = False
        End With
        
    End With

[a-son]

Tackar, precis, lägger in det när jag infogar ny vecka,

funkar som tänkt.

blev så här:

Sub NyaRader()
    Application.ScreenUpdating = False
    Dim i As Integer
    With shPlan.ListObjects("Plan")
        For i = 1 To 7
            .ListRows.Add (1)
        Next i
        .DataBodyRange.Resize(2, 1).Offset(7).AutoFill _
            Destination:=.DataBodyRange.Resize(9, 1), Type:=xlFillDefault
            With .DataBodyRange.Resize(.DataBodyRange.Rows.Count, 7)
            With .Font
                .Color = RGB(91, 86, 77)
                .Bold = False
                .Name = "Calibri"
                .Size = 10
            End With
        End With
    End With
    
    With shPlan.Range("Plan[[Huvudrätt]:[Information]]")
        With .Validation
        End With
        With .Resize(7).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=DishSelection"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = False
        End With
        End With
       Application.ScreenUpdating = True
End Sub

[Monshi]

Fast... nu raderar du valideringen i de sju första raderna och lägger sedan in den där igen.

 

Om du ändå ska spara valideringen i hela tabellen, radera för hela kolumnen (i tabellen) och lägg in för hela kolumnen igen. Jag tänkte att du vill ta bort valideringen på gamla poster så man inte enkelt skulle kunna gå in och ändra rätt.

[a-son]

när jag inte hade validering med, så var det ingen droplist.

så där av den här lösningen,

och har jag inte delete så går det inte att köra macrot,

är troligtvis för det är någon validering där redan.

 

Edit:

funkar nu utan att jag har lagt till validering i macrot.

måste ha varit nogit skrot i cellerna sedan tidigare