Gegevens opslaan in de juiste rij

Judithdoek · 12 apr 2021

Hallo allemaal,

In bijgevoegd Excel bestand staan 2 werkbladen. in 1 werkblad worden gegevens ingevoerd en in het andere werkblad moeten vervolgens de gegevens worden opgeslagen. Het werkt als volgt: 1. Er worden gegevens ingevoerd (het nummer staat er al, want deze wordt automatisch aangemaakt) 2. Wanneer alle gegevens zijn ingevoerd klik je op de command button doorvoeren. 3. In de knop staat een code die er nu voor zorgt dat het nummer op de juiste plek wordt opgeslagen en dat er automatisch een nieuw uniek nummer in beeld komt te staan (zodat er geen dubbele waarden ontstaan). De naam op het invoerbestand in cel D4 moet soms wel worden ingevoerd, maar soms ook niet. Het probleem is dat het met de code die ik nu gebruik niet in de juiste rij wordt opgeslagen in het overzicht.

Code:

Option Explicit

Private Sub CommandButton1_Click()
    'UNIEK NUMMER
    With Sheets("Overzicht afspraken test2")
        .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Value = Sheets("Invoerbestand test2").Range("B4").Value
    End With
    If Range("B4").Value = "" Then Range("B4").Value = 0
    Range("B4").Value = Year(Date) & "-" & Format(Int(Right(Range("B4").Value, 4)) + 1, "#0000")
    With Sheets("Overzicht afspraken test2")
        .Cells(.Rows.Count, "J").End(xlUp).Offset(1).Value = Sheets("Invoerbestand test2").Range("D4").Value
    End With
End Sub

Heeft iemand de oplossing voor mij?

Alvast bedankt.

bron · 12 apr 2021

mod. Verplaatst naar Excel forum

JEC. · 12 apr 2021

Wil je de rest van de invulvakken niet overzetten?

Code:

Sub j()
 With Sheets("Invoerbestand test2")
   If .Cells(4, 2).Value = "" Then .Cells(4, 2).Value = 0
   Sheets("Overzicht afspraken test2").Cells(.Rows.Count, 4).End(xlUp).Offset(1).Value = .Cells(4, 2).Value
   Sheets("Overzicht afspraken test2").Cells(.Rows.Count, 4).End(xlUp).Offset(, 6).Value = .Cells(4, 4).Value
  .Cells(4, 2).Value = Year(Date) & "-" & Format(Int(Right(.Cells(4, 2).Value, 4)) + 1, "#0000")
 End With
End Sub

Judithdoek · 12 apr 2021

Uiteindelijk wel, maar dat is een kwestie van herhaling dacht ik

JEC. · 12 apr 2021

Het nummer, de naam en de eerste 5 velden voorgedaan. (de velden heb ik een named range gegeven: Veld1, Veld2 etc)
Macro hangt aan de knop.

Ook heb ik een voorbeeldje toegevoegd waarbij de data netjes in een tabel wordt weggezet.
Dat doe je dmv de blauwe knop. Data kom in tabblad "Overzicht2"

Judithdoek · 13 apr 2021

Heel erg bedankt, volgens mij werkt het nu. Kan je misschien ook uitleggen wat je precies gedaan hebt in de code? Dan kan ik in het vervolg het zelf uitzoeken.

Code:

Private Sub CommandButton1_Click()
 ReDim ar(1 To 1, 1 To 7)
  With Sheets("Invoerbestand test2")
   If .Cells(4, 2).Value = "" Then .Cells(4, 2).Value = 0
      ar(1, 1) = [Nummer]
      ar(1, 2) = [Naam]
    For i = 1 To 5
      ar(1, i + 2) = .Range("Veld" & i)
    Next
   .Cells(4, 2).Value = Year(Date) & "-" & Format(Int(Right(.Cells(4, 2).Value, 4)) + 1, "#0000")
  End With
  With Sheets("Overzicht afspraken test2")
    .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize(, UBound(ar, 2)) = ar
  End With
End Sub

JEC. · 13 apr 2021

Dit is wat er gebeurt

Code:

Private Sub CommandButton1_Click()
 ReDim ar(1 To 1, 1 To 7)                                     [COLOR="#008000"]  'array maken[/COLOR]
  With Sheets("Invoerbestand test2")
   If .Cells(4, 2).Value = "" Then .Cells(4, 2).Value = 0      [COLOR="#008000"] 'De check die jij wil om te kijken of er iets in cel B4 staat[/COLOR]
      ar(1, 1) = [Nummer]                         [COLOR="#008000"]  'Waarde van kolom 1 in de array is het nummer[/COLOR]
      ar(1, 2) = [Naam]                           [COLOR="#008000"]  'Waarde van kolom 2 in de array is de naam[/COLOR]
    For i = 1 To 5                                 [COLOR="#008000"] 'Loop door de velden (in dit geval 5 velden als voorbeeld genomen)[/COLOR]
      ar(1, i + 2) = .Range("Veld" & i)            [COLOR="#008000"] 'De rest van de array vullen met de waarden uit de velden[/COLOR]
    Next
   .Cells(4, 2).Value = Year(Date) & "-" & Format(Int(Right(.Cells(4, 2).Value, 4)) + 1, "#0000")    [COLOR="#008000"]'Nummer ophogen met juiste format[/COLOR]
  End With
  With Sheets("Overzicht afspraken test2")
    .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize(, UBound(ar, 2)) = ar                [COLOR="#008000"] 'Array wegschrijven in bereik[/COLOR]
  End With
End Sub

Judithdoek · 14 apr 2021

Ik had nog even een vraag (staat in het rood)

JVeer zei:

Dit is wat er gebeurt

Code:

Private Sub CommandButton1_Click()
 ReDim ar(1 To 1, 1 To 7)                                     [COLOR="#008000"]  'array maken[/COLOR]
  With Sheets("Invoerbestand test2")
   If .Cells(4, 2).Value = "" Then .Cells(4, 2).Value = 0      [COLOR="#008000"] 'De check die jij wil om te kijken of er iets in cel B4 staat[/COLOR]
      ar(1, 1) = [Nummer]                         [COLOR="#008000"]  'Waarde van kolom 1 in de array is het nummer[/COLOR]
      ar(1, 2) = [Naam]                           [COLOR="#008000"]  'Waarde van kolom 2 in de array is de naam[/COLOR]
    For i = 1 To 5                                 [COLOR="#008000"] 'Loop door de velden (in dit geval 5 velden als voorbeeld genomen)[/COLOR][COLOR="#ff0000"]'Als ik alle kolommen benoemd heb, hoef ik dan geen velden te benoemen? En hoe ziet de code er dan voorts uit?[/COLOR]
      ar(1, i + 2) = .Range("Veld" & i)            [COLOR="#008000"] 'De rest van de array vullen met de waarden uit de velden[/COLOR]
    Next
   .Cells(4, 2).Value = Year(Date) & "-" & Format(Int(Right(.Cells(4, 2).Value, 4)) + 1, "#0000")    [COLOR="#008000"]'Nummer ophogen met juiste format[/COLOR]
  End With
  With Sheets("Overzicht afspraken test2")
    .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize(, UBound(ar, 2)) = ar                [COLOR="#008000"] 'Array wegschrijven in bereik[/COLOR]
  End With
End Sub

JEC. · 14 apr 2021

Je moet alle velden benoemen, net zoals de eerste 5 velden. (Veld6, Veld7 etc..). Als je alle velden hebt benoemd, moet je ook de array en Loop uitbreiden naar het juiste aantal.

Code:

Private Sub CommandButton1_Click()
 ReDim ar(1 To 1, 1 To [COLOR="#FF0000"]21[/COLOR])                                      
  With Sheets("Invoerbestand test2")
   If .Cells(4, 2).Value = "" Then .Cells(4, 2).Value = 0       
      ar(1, 1) = [Nummer]                           
      ar(1, 2) = [Naam]                           
    For i = 1 To [COLOR="#FF0000"]19[/COLOR]                                
      ar(1, i + 2) = .Range("Veld" & i)            
    Next
   .Cells(4, 2).Value = Year(Date) & "-" & Format(Int(Right(.Cells(4, 2).Value, 4)) + 1, "#0000")   
  End With
  With Sheets("Overzicht afspraken test2")
    .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize(, UBound(ar, 2)) = ar               
  End With
End Sub

PS: probeer te reageren met de grote knop onderin "Reageer op bericht". Grote berichten quoten maakt het onoverzichtelijk

Judithdoek · 14 apr 2021

Bedankt voor de tip over het quoten.

Ik heb in de bijlage een nieuw Excel bestand, waarbij ik de volgende code heb toegepast.

Code:

Option Explicit
Sub Gegevens_opslaan()
ReDim ar(1 To 1, 1 To 12)
  With Sheets("Invoerbestand test2")
   If .Cells(4, 2).Value = "" Then .Cells(4, 2).Value = 0
      ar(1, 1) = [Nummer]
      ar(1, 2) = [Persoon]
      ar(1, 3) = [Persoon2]
      ar(1, 4) = [Klant]
      ar(1, 5) = [Afspraak]
      ar(1, 6) = [Klantnummer]
      ar(1, 7) = [Persoon3]
      ar(1, 8) = [Plaats]
      ar(1, 9) = [Kans]
      ar(1, 10) = [Groep]
      ar(1, 11) = [Percentage]
      ar(1, 12) = [Opmerking]
    For i = 1 To 5                                                                                                                    'Hier gaat iets mis, als ik het zo laat staan. Naar mijn idee heb ik 12 kolommen benoemd, dus is mijn gedachte dat ik het weg kan laten.
      ar(1, i + 2) = .Range("Veld" & i)                                                                                      'als ik het weg laat, dan moet ik Next ook weglaten, als ik Next ook weglaat dan gebeurt er niks, omdat het dan als "" wordt gezien.
    Next
   .Cells(4, 2).Value = Year(Date) & "-" & Format(Int(Right(.Cells(4, 2).Value, 4)) + 1, "#0000")
  End With
  With Sheets("Overzicht afspraken test2")
    .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize(, UBound(ar, 2)) = ar
  End With
End Sub

Private Sub CommandButton1_Click()
Call Gegevens_opslaan


End Sub

Hoe los ik bovenstaande op?
Dank voor je reactie alvast.

VenA · 14 apr 2021

Volgens mij heb je een verkeerd bestand bijgevoegd.

Judithdoek · 14 apr 2021

Nee hoor dit is de goede, alleen het is wel een andere. Omdat ik nog een vraag heb, maar dat gaat over iets anders, vandaar dat er een aantal dingen aan toegevoegd zijn.

VenA · 14 apr 2021

De tabnamen kloppen niet. Er staan geen gedefinieerde namen in. Als je Option Explicit gebruikt dan moet je alle variabelen declareren.

Judithdoek · 14 apr 2021

Och wat stom! Ik heb de code gekopieerd vanuit het oude bestand, dom van mij.

Nu heb ik alleen wel een ander probleem. Ik heb de bladnamen gewijzigd en het gedeelte For Next weggehaald. Nu krijg ik als resultaat in het overzicht #NAAM?

Ik heb het gewijzigde bestand bijgevoegd. En dit is nu de code

Code:

Option Explicit
Sub Gegevens_opslaan()
ReDim ar(1 To 1, 1 To 12)
  With Sheets("Invoer")
   If .Cells(4, 2).Value = "" Then .Cells(4, 2).Value = 0
      ar(1, 1) = [Nummer]
      ar(1, 2) = [Persoon]
      ar(1, 3) = [Persoon2]
      ar(1, 4) = [Klant]
      ar(1, 5) = [Afspraak]
      ar(1, 6) = [Klantnummer]
      ar(1, 7) = [Persoon3]
      ar(1, 8) = [Plaats]
      ar(1, 9) = [Kans]
      ar(1, 10) = [Groep]
      ar(1, 11) = [Percentage]
      ar(1, 12) = [Opmerking]
   .Cells(4, 2).Value = Year(Date) & "-" & Format(Int(Right(.Cells(4, 2).Value, 4)) + 1, "#0000")
  End With
  With Sheets("Overzicht1")
    .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize(, UBound(ar, 2)) = ar
  End With
End Sub

Private Sub CommandButton1_Click()
Call Gegevens_opslaan


End Sub

VenA · 14 apr 2021

Er staan geen gedefinieerde namen in.

Dan maar zo

Code:

Private Sub CommandButton1_Click()  Dim ar
  If Cells(4, 2).Value = "" Then Cells(4, 2).Value = 0
  ar = Array([B4].Value, [D4].Value, [F4].Value, [H4].Value, [J4].Value, [B7].Value, [D7].Value, [F7].Value, [H7].Value, [B10].Value, [D10].Value, [F10].Value)
  Sheets("Overzicht1").Cells(Rows.Count, 4).End(xlUp).Offset(1).Resize(, 12) = ar
  Cells(4, 2).Value = Year(Date) & "-" & Format(Int(Right(Cells(4, 2).Value, 4)) + 1, "#0000")
End Sub

Judithdoek · 14 apr 2021

Welke namen moet ik definiëren? De tabnamen? Moet dat ook wanneer je dezelfde naam gebruikt als het bladnaam?
Excuus als ik je niet helemaal begrijp. Ik probeer het te snappen, zodat ik ooit niet iedere keer hoef te vragen hoe een code werkt.

Thanks voor de code, deze werkt goed. Even een ander vraagje wat is het voordeel van For Next?

VenA · 14 apr 2021

Als je het bestand in #5 goed bekeken had dan waren alle vervolgvragen overbodig

https://www.gratiscursus.be/Excel_Tips/Excel_Tip_101.html

Judithdoek · 14 apr 2021

@VenA Dat is een aanname dat ik het in reactie #5 niet goed heb bekeken. De vervolgvragen waren voor mij nuttig, ik ben namelijk vrij nieuw in VBA en weet ook niet perse alles van Excel. Ik heb het bestand daarom zo ver mijn kennis toereikend is bekeken, meer kan ik niet doen.
Bedankt voor de link, ik ga kijken of ik daarmee verder kan.

Gegevens opslaan in de juiste rij

Gebruiker

Bijlagen

Moderator

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Bijlagen

Gebruiker

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Bijlagen

Inventaris

Gebruiker

Inventaris

Gebruiker

Bijlagen

Inventaris

Gebruiker

Inventaris

Gebruiker

Wij waarderen jouw privacy