oplaan van aangepaste data terug in de tabel

Printable View

11-20-2023, 12:38 PM
CyberTazz

1 Attachment(s)

oplaan van aangepaste data terug in de tabel

Beste forum gebruikers ik heb een probleem met een calculatie file
ik heb een tabblad Artikel_db met een tabel voor mijn artikelen.
Via een tabblad artikelen invoeren kan ik artikelen toevoegen aan het tabel vanhet tabblad artikel_DB.
Daarnaast heb ik een tabblad artikel edit voor artikelen te kunnen editen hierbij haal ik de data uit de tabel van de artikel_DB terug om te editen en via een knop save weer terug te schrijven in de tabel (ook hier zitten foute in maar werkt meestal).
Dan heb ik een tabblad voor recepten in te voeren daar kan ik recepten invoeren met artikelen vanuit de artDB en daar bereken hij van alles mee dit werkt goed.
dit slaat hij via een knop save op in een tabel op het tabblad recept_DB dit werkt ook prima
maar nu moet een recept ook aangepast kunnen worden daar heb ik een tabblad voor aangemaakt recept edit en het inladen van het recept werkt prima het aanpassen ook maar als ik het wil saven dus weer terug schrijven naar de tabel in recept db dan wil hij dit niet doen hij loopt de select velden netjes na maar schrijft niets weg de oude waarde worden weer terug gehaald

ik loop hierop vast voor ik weer verder kan gaan

heeft iemand hier een oplossing of antwoord op
het gaat dus om het stuk code in vb

Code:

Sub Edit_Recept_Save() 'Een gewijzigt recept opslaan en Dim SelRow As Long If Blad8.ComboBox1.Value = Empty Then 'controleer of er een recept geselecteerd is MsgBox "Selecteer een Recept" Exit Sub Else With Blad3 Set RowNr = .Range("A4:A2000").Find(What:=Blad8.Range("G3").Value, LookIn:=xlValues) 'rij nr opzoeken End With SelRow = RowNr.Row 'rij nr setting With Blad3 Blad8.Range("C9", "G9").Select Blad3.Range("B" & SelRow).Value = Blad8.Range("C9") 'Recept naam. Blad3.Range("C" & SelRow).Value = Blad8.Range("G9") 'Artikelnr. Blad8.Range("J9", "C11").Select Blad3.Range("D" & SelRow).Value = Blad8.Range("J9") 'Volume Blad3.Range("E" & SelRow).Value = Blad8.Range("C11") 'Aantal stuks uit recept Blad8.Range("A13", "B13").Select Blad3.Range("F" & SelRow).Value = Blad8.Range("A13").Value 'IngrediÃ«nt 1 Naam Blad3.Range("G" & SelRow).Value = Blad8.Range("B13").Value 'IngrediÃ«nt 1 Hoeveelheid Blad8.Range("A14", "B14").Select Blad3.Range("H" & SelRow).Value = Blad8.Range("A14").Value 'IngrediÃ«nt 2 Naam Blad3.Range("I" & SelRow).Value = Blad8.Range("B14").Value 'IngrediÃ«nt 2 Hoeveelheid Blad8.Range("A15", "B15").Select Blad3.Range("J" & SelRow).Value = Blad8.Range("A15").Value 'IngrediÃ«nt 3 Naam Blad3.Range("K" & SelRow).Value = Blad8.Range("B15").Value 'IngrediÃ«nt 3 Hoeveelheid Blad8.Range("A16", "B16").Select Blad3.Range("L" & SelRow).Value = Blad8.Range("A16").Value 'IngrediÃ«nt 4 Naam Blad3.Range("M" & SelRow).Value = Blad8.Range("B16").Value 'IngrediÃ«nt 4 Hoeveelheid Blad8.Range("A17", "B17").Select Blad3.Range("N" & SelRow).Value = Blad8.Range("A17").Value 'IngrediÃ«nt 5 Naam Blad3.Range("O" & SelRow).Value = Blad8.Range("B17").Value 'IngrediÃ«nt 5 Hoeveelheid Blad8.Range("A18", "B18").Select Blad3.Range("P" & SelRow).Value = Blad8.Range("A18").Value 'IngrediÃ«nt 6 Naam Blad3.Range("Q" & SelRow).Value = Blad8.Range("B18").Value 'IngrediÃ«nt 6 Hoeveelheid Blad8.Range("A19", "B19").Select Blad3.Range("R" & SelRow).Value = Blad8.Range("A19").Value 'IngrediÃ«nt 7 Naam Blad3.Range("S" & SelRow).Value = Blad8.Range("B19").Value 'IngrediÃ«nt 7 Hoeveelheid Blad8.Range("A20", "B20").Select Blad3.Range("T" & SelRow).Value = Blad8.Range("A20").Value 'IngrediÃ«nt 8 Naam Blad3.Range("U" & SelRow).Value = Blad8.Range("B20").Value 'IngrediÃ«nt 8 Hoeveelheid Blad8.Range("A21", "B21").Select Blad3.Range("V" & SelRow).Value = Blad8.Range("A21").Value 'IngrediÃ«nt 9 Naam Blad3.Range("W" & SelRow).Value = Blad8.Range("B21").Value 'IngrediÃ«nt 9 Hoeveelheid Blad8.Range("A22", "B22").Select Blad3.Range("X" & SelRow).Value = Blad8.Range("A22").Value 'IngrediÃ«nt 10 Naam Blad3.Range("Y" & SelRow).Value = Blad8.Range("B22").Value 'IngrediÃ«nt 10 Hoeveelheid Blad8.Range("A23", "B23").Select Blad3.Range("Z" & SelRow).Value = Blad8.Range("A23").Value 'IngrediÃ«nt 11 Naam Blad3.Range("AA" & SelRow).Value = Blad8.Range("B23").Value 'IngrediÃ«nt 11 Hoeveelheid Blad8.Range("A24", "B24").Select Blad3.Range("AB" & SelRow).Value = Blad8.Range("A24").Value 'IngrediÃ«nt 12 Naam Blad3.Range("AC" & SelRow).Value = Blad8.Range("B24").Value 'IngrediÃ«nt 12 Hoeveelheid Blad8.Range("A25", "B25").Select Blad3.Range("AD" & SelRow).Value = Blad8.Range("A25").Value 'IngrediÃ«nt 13 Naam Blad3.Range("AE" & SelRow).Value = Blad8.Range("B25").Value 'IngrediÃ«nt 13 Hoeveelheid Blad8.Range("A26", "B26").Select Blad3.Range("AF" & SelRow).Value = Blad8.Range("A26").Value 'IngrediÃ«nt 14 Naam Blad3.Range("AG" & SelRow).Value = Blad8.Range("B26").Value 'IngrediÃ«nt 14 Hoeveelheid Blad8.Range("A27", "B27").Select Blad3.Range("AH" & SelRow).Value = Blad8.Range("A27").Value 'IngrediÃ«nt 15 Naam Blad3.Range("AI" & SelRow).Value = Blad8.Range("B27").Value 'IngrediÃ«nt 15 Hoeveelheid Blad8.Range("A28", "B28").Select Blad3.Range("AJ" & SelRow).Value = Blad8.Range("A28").Value 'IngrediÃ«nt 16 Naam Blad3.Range("AK" & SelRow).Value = Blad8.Range("B28").Value 'IngrediÃ«nt 16 Hoeveelheid Blad8.Range("A29", "B29").Select Blad3.Range("AL" & SelRow).Value = Blad8.Range("A29").Value 'IngrediÃ«nt 17 Naam Blad3.Range("AM" & SelRow).Value = Blad8.Range("B29").Value 'IngrediÃ«nt 17 Hoeveelheid Blad8.Range("A30", "B30").Select Blad3.Range("AN" & SelRow).Value = Blad8.Range("A30").Value 'IngrediÃ«nt 18 Naam Blad3.Range("AO" & SelRow).Value = Blad8.Range("B30").Value 'IngrediÃ«nt 18 Hoeveelheid End With End If 'Dashboard End Sub
11-20-2023, 04:38 PM
Aussiebear

Welcome to VBAX CyberTazz. have you considered using Access instead of Excel for this project or at the very least create an interface (form) to act between your existing data table and the User?
11-20-2023, 11:00 PM
June7

This is an English forum. Best to post as English in appropriate topic subforum. Use translation tool.

Are you using Excel as a database? Why? There are better tools for that.
11-21-2023, 01:10 AM
Aussiebear

@June7. CyberTazz posted in the non English section so please don't critique for having posted.

Anyway this is the translation.

Quote:

Saving modified data back in the table

Dear forum users I have a problem with a calculation file

I have an Article_db tab with a table for my articles.

I can add articles to the table of the article_DB tab via an article tab.

In addition, I have a tab article edit for articles to be able to edit. I retrieve the data from the table of the article_DB to edit and write back in the table via a save button (there are also errors in this but usually works).

Then I have a tab for recipes to enter there I can enter recipes with articles from the artDB and he calculates everything with that this works well.

He saves this via a save button in a table on the recipe_DB tab this also works fine

But now a recipe must also be able to be adjusted there I have a tab for created recipe edit and loading the recipe works fine adjusting it too but if I want to save it so write back to the table in recipe db then he doesn't want to do this he runs the select fields neatly but writes nothing away the old value are brought back

I get stuck on this before I can continue

Does anyone have a solution or answer to this

So it's about the piece of code in vb

Code:

Sub Edit_Recept_Save() 'Save a edited recipe and Dim SelRow As Long If Sheet8. ComboBox1. Value = Empty Then 'check if a recipe is selected MsgBox "Select a Recipe" Exit Sub Else With Sheet3 Set RowNr = . Range("A4:A2000"). Find(What:=Sheet8. Range("G3"). Value, LookIn:=xlValues) 'row number look up End With SelRow = RowNr.Row 'rij nr setting With Sheet3 Sheet8. Range("C9", "G9"). Select Sheet3. Range("B" & SelRow). Value = Sheet8. Range("C9") 'Recipe name. Sheet3. Range("C" & SelRow). Value = Sheet8. Range("G9") 'Article No. Sheet8. Range("J9", "C11"). Select Sheet3. Range("D" & SelRow). Value = Sheet8. Range("J9") 'Volume Sheet3. Range("E" & SelRow). Value = Sheet8. Range("C11") 'Number of pieces from recipe Sheet8. Range("A13", "B13"). Select Sheet3. Range("F" & SelRow). Value = Sheet8. Range("A13"). Value 'Ingredint 1 Name Sheet3. Range("G" & SelRow). Value = Sheet8. Range("B13"). Value 'Ingredindient 1 Quantity Sheet8. Range("A14", "B14"). Select Sheet3. Range("H" & SelRow). Value = Sheet8. Range("A14"). Value 'Ingredint 2 Name Sheet3. Range("I" & SelRow). Value = Sheet8. Range("B14"). Value 'Ingredint 2 Quantity Sheet8. Range("A15", "B15"). Select Sheet3. Range("J" & SelRow). Value = Sheet8. Range("A15"). Value 'Ingredint 3 Name Sheet3. Range("K" & SelRow). Value = Sheet8. Range("B15"). Value 'Ingredint 3 Quantity Sheet8. Range("A16", "B16"). Select Sheet3. Range("L" & SelRow). Value = Sheet8. Range("A16"). Value 'Ingredint 4 Name Sheet3. Range("M" & SelRow). Value = Sheet8. Range("B16"). Value 'Ingredint 4 Quantity Sheet8. Range("A17", "B17"). Select Sheet3. Range("N" & SelRow). Value = Sheet8. Range("A17"). Value 'Ingredint 5 Name Sheet3. Range("O" & SelRow). Value = Sheet8. Range("B17"). Value 'Ingredint 5 Quantity Sheet8. Range("A18", "B18"). Select Sheet3. Range("P" & SelRow). Value = Sheet8. Range("A18"). Value 'Ingredint 6 Name Sheet3. Range("Q" & SelRow). Value = Sheet8. Range("B18"). Value 'Ingredint 6 Quantity Sheet8. Range("A19", "B19"). Select Sheet3. Range("R" & SelRow). Value = Sheet8. Range("A19"). Value 'Ingredint 7 Name Sheet3. Range("S" & SelRow). Value = Sheet8. Range("B19"). Value 'Ingredint 7 Quantity Sheet8. Range("A20", "B20"). Select Sheet3. Range("T" & SelRow). Value = Sheet8. Range("A20"). Value 'Ingredint 8 Name Sheet3. Range("U" & SelRow). Value = Sheet8. Range("B20"). Value 'Ingredint 8 Quantity Sheet8. Range("A21", "B21"). Select Sheet3. Range("V" & SelRow). Value = Sheet8. Range("A21"). Value 'Ingredint 9 Name Sheet3. Range("W" & SelRow). Value = Sheet8. Range("B21"). Value 'Ingredint 9 Quantity Sheet8. Range("A22", "B22"). Select Sheet3. Range("X" & SelRow). Value = Sheet8. Range("A22"). Value 'Ingredint 10 Name Sheet3. Range("Y" & SelRow). Value = Sheet8. Range("B22"). Value 'Ingredint 10 Quantity Sheet8. Range("A23", "B23"). Select Sheet3. Range("Z" & SelRow). Value = Sheet8. Range("A23"). Value 'Ingredint 11 Name Sheet3. Range("AA" & SelRow). Value = Sheet8. Range("B23"). Value 'Ingredint 11 Quantity Sheet8. Range("A24", "B24"). Select Sheet3. Range("AB" & SelRow). Value = Sheet8. Range("A24"). Value 'Ingredint 12 Name Sheet3. Range("AC" & SelRow). Value = Sheet8. Range("B24"). Value 'Ingredint 12 Quantity Sheet8. Range("A25", "B25"). Select Sheet3. Range("AD" & SelRow). Value = Sheet8. Range("A25"). Value 'Ingredint 13 Name Sheet3. Range("AE" & SelRow). Value = Sheet8. Range("B25"). Value 'Ingredint 13 Quantity Sheet8. Range("A26", "B26"). Select Sheet3. Range("AF" & SelRow). Value = Sheet8. Range("A26"). Value 'Ingredint 14 Name Sheet3. Range("AG" & SelRow). Value = Sheet8. Range("B26"). Value 'Ingredint 14 Quantity Sheet8. Range("A27", "B27"). Select Sheet3. Range("AH" & SelRow). Value = Sheet8. Range("A27"). Value 'Ingredint 15 Name Sheet3. Range("AI" & SelRow). Value = Sheet8. Range("B27"). Value 'Ingredint 15 Quantity Sheet8. Range("A28", "B28"). Select Sheet3. Range("AJ" & SelRow). Value = Sheet8. Range("A28"). Value 'Ingredint 16 Name Sheet3. Range("AK" & SelRow). Value = Sheet8. Range("B28"). Value 'Ingrediant 16 Quantity Sheet8. Range("A29", "B29"). Select Sheet3. Range("AL" & SelRow). Value = Sheet8. Range("A29"). Value 'Ingredint 17 Name Sheet3. Range("AM" & SelRow). Value = Sheet8. Range("B29"). Value 'Ingredint 17 Quantity Sheet8. Range("A30", "B30"). Select Sheet3. Range("AN" & SelRow). Value = Sheet8. Range("A30"). Value 'Ingredint 18 Name Sheet3. Range("AO" & SelRow). Value = Sheet8. Range("B30"). Value 'Ingredint 18 Quantity End With End If 'Dashboard End Sub
11-21-2023, 01:34 AM
June7

Just advising how to maximize attention to thread.
11-22-2023, 02:20 PM
CyberTazz

thanks for the translation,
When asked about a solution in Excel, we do not have access at work.
Since I have some knowledge of Excel, I chose this option
In theory this should be possible, but my knowledge of VBA is self-learned and not yet an expert
that's why I sought help in this way
01-07-2024, 12:41 PM
CyberTazz

I would like to let you know that my problem has already been solved.
The problem was caused by the "ComboBox1_Change" event procedure being fired after changing the value in column B of the "Blad3" sheet.
01-08-2024, 03:46 AM
Aussiebear

Glad to know you were able to find a solution, and thank you for marking the thread as Solved.
05-03-2024, 11:34 PM
jolivanes

Dit doet volgens mij hetzelfde als de 62 lijnen in Post #1. Proberen als je wilt. In ieder geval korter om de bocht.
I don't know if you want to try this on a copy of your original but It should do the same as the 62 lines of code in Post #1.

Code:

Sub Try_So() Dim sh3 As Worksheet, sh8 As Worksheet Dim i As Long, j As Long Set sh3 = Worksheets("Sheet3") Set sh8 = Worksheets("Sheet8") j = 13 With sh3.Cells(SelRow, 2) .Value = sh8.Cells(9, 3).Value .Offset(, 1).Value = sh8.Cells(9, 7).Value .Offset(, 2).Value = sh8.Cells(9, 12).Value .Offset(, 3).Value = sh8.Cells(11, 3).Value End With For i = 6 To 41 Step 2 sh3.Cells(SelRow, i).Value = sh8.Cells(j, 1).Value sh3.Cells(SelRow, i + 1).Value = sh8.Cells(j, 2).Value j = j + 1 Next i End Sub