Consulting

Results 1 to 8 of 8

Thread: oplaan van aangepaste data terug in de tabel

  1. 11-20-2023, 12:38 PM #1
    CyberTazz
    CyberTazz is offline
    VBAX Newbie
    Joined
    Nov 2023
    Posts
    4
    Location

    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
     
    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
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  2. 11-20-2023, 04:38 PM #2
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,059
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  3. 11-20-2023, 11:00 PM #3
    June7
    June7 is online now
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    337
    Location
    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.
    Last edited by June7; 11-21-2023 at 01:32 AM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
    Reply With Quote Reply With Quote
  4. 11-21-2023, 01:10 AM #4
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,059
    Location
    @June7. CyberTazz posted in the non English section so please don't critique for having posted.

    Anyway this is the translation.

    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
    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
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  5. 11-21-2023, 01:34 AM #5
    June7
    June7 is online now
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    337
    Location
    Just advising how to maximize attention to thread.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
    Reply With Quote Reply With Quote
  6. 11-22-2023, 02:20 PM #6
    CyberTazz
    CyberTazz is offline
    VBAX Newbie
    Joined
    Nov 2023
    Posts
    4
    Location
    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
    Reply With Quote Reply With Quote
  7. 01-07-2024, 12:41 PM #7
    CyberTazz
    CyberTazz is offline
    VBAX Newbie
    Joined
    Nov 2023
    Posts
    4
    Location
    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.
    Reply With Quote Reply With Quote
  8. 01-08-2024, 03:46 AM #8
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,059
    Location
    Glad to know you were able to find a solution, and thank you for marking the thread as Solved.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules