Uitleg formules in bestaand bestand

[JeroenSturk]

Hallo,

Van een collega heb een Excel bestand gekregen waar ik een dagboek kan bij houden, alleen is deze uit 2019.

Nu wil ik deze wijzigen naar 2020 bij Algemene info(B1) en bij verdere tabbladen.

Deels zou dit automatisch gaan als ik deze in het eerst tabblad in B4 en C4 zou wijzigen, alleen overschrijf ik dan een formule die verwijst naar E4.

Zou iemand mij kunnen uitleggen wat deze formule in E4 doet?

En is het mogelijk als ik de datum invul in startdatum werk bij het tabblad 'algemene info', dat dit dan automatisch wijzigt in de tabbladen?

Alvast bedankt

Groet Jeroen

 

[VenA]

In iedere weektab staat in E4 een formule die de naam van de tab ophaalt.

=MID(@CELL("filename";$A$1);FIND("]";@CELL("filename";$A$1))+1;31)

Deze zal bij jou in het Nederlands zijn. Op basis van de naam van de tab wordt de week en het jaar weergegeven in B4 en D4. Om het voor 2020 werkend te krijgen moet je dus alle tabnamen aanpassen. Verder moet je waarschijnlijk alle formules nalopen om te kijken of de verwijzingen en berekeningen nog wel kloppen.

 

[JeroenSturk]

Oke, bedankt voor de uitleg.

Is er geen formule als ik de startdatum in een bepaalde cel invul, dat tabblad automatisch wijzigt en vervolgens de hele reeks?

Groet Jeroen

 

[AD1957]

Met een stukje VBA in blad "Algemene info" (werkt t/m het jaar 2099):d
Als je het jaartal wijzigt in cel B1 worden alle tabbladnamen aangepast.
Test het wel nog even in het bijgevoegde voorbeeldbestand.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B1") Then
        For i = 1 To 53
         If Left(Sheets(i).Name, 2) = 20 Then
            Sheets(i).Name = Sheets("Algemene info").Range("B1") & Right(Sheets(i).Name, 2)
         End If
        Next i
    End If
End Sub

 

[JeroenSturk]

Dat is top, scheelt een hoop werk

Zou dat ook mogelijk zijn als ik het dagboek pas in week 35 bijvoorbeeld wil laten starten tot einde jaar?

 

[OctaFish]

        For i = 35 To 53

Denk ik

 

[AD1957]

Vraag is een beetje onduidelijk, maar ik denk dat zo je vraag is opgelost.
Wijzig de code in:
wijzig daarna maar eens het weeknummer in blad "Algemene info"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B1") Then
        For i = 1 To 53
         If Left(Sheets(i).Name, 2) = 20 Then
            Sheets(i).Name = Sheets("Algemene info").Range("B1") & Right(Sheets(i).Name, 2)
         End If
        Next i
    End If
    
        
    If Target = Range("B10") Then
        For Each Worksheet In ActiveWorkbook.Worksheets          [COLOR="#008000"]'Alle werkbladen zichtbaar maken[/COLOR]
        Worksheet.Visible = xlSheetVisible
        Next Worksheet
     
        For i = 1 To 53
            If i - 1 < Range("B10").Value Then
               Sheets(i).Visible = False
            End If
        Next i
    End If
    
    Sheets("Algemene info").Visible = True
    Application.Goto Sheets("Algemene info").Range("B10")
    
End Sub

 

[JeroenSturk]

bijna gelukt, alleen van 2020 na week 32 gaat de jaar telling naar 2121.

Of doe ik wat verkeerd?

 

[AD1957]

probeer het eens zo. (zie rode gedeelte)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B1") Then
        For i = 1 To 53
         [COLOR="#FF0000"]If Left(Sheets(i).Name, 1) = 2 Then[/COLOR]
            Sheets(i).Name = Sheets("Algemene info").Range("B1") & Right(Sheets(i).Name, 2)
         End If
        Next i
    End If
    
        
    If Target = Range("B10") Then
        For Each Worksheet In ActiveWorkbook.Worksheets          'Alle werkbladen zichtbaar maken
        Worksheet.Visible = xlSheetVisible
        Next Worksheet
     
        For i = 1 To 53
            If i - 1 < Range("B10").Value Then
               Sheets(i).Visible = False
            End If
        Next i
    End If
    
    Sheets("Algemene info").Visible = True
    Application.Goto Sheets("Algemene info").Range("B10")
    
End Sub

 

[JeroenSturk]

Helaas nog steeds hetzelfde probleem...

 

[HSV]

Dat gaat lekker.

If Target = Range("B1") Then

En doe er dan gelijk maar dubbele quotes omheen; = "2"

 

[VenA]

Mij is de vraag niet geheel duidelijk. Begint het boekjaar in 202035 en loopt door tot 202134. Of gaat het alleen om het zichtbaar maken vanaf een bepaalde week. Met iets meer structuur.

Private Sub Worksheet_Change(ByVal Target As Range)
  Select Case Target.Address(0, 0)
    Case "B1"
      For Each sh In Sheets
        If IsNumeric(sh.Name) Then sh.Name = Replace(sh.Name, Left(sh.Name, 4), Target.Value)
      Next sh
    Case "B10"
      For Each sh In Sheets
        If IsNumeric(sh.Name) Then sh.Visible = Val(Right(sh.Name, 2)) >= Target.Value
      Next sh
  End Select
End Sub

 

[HSV]

Ik had die code iets later geplaatst.
De vragen zullen waarschijnlijk nu uitblijven.

Het niveau wordt er hier niet beter op met sommige schrijfwijzes terwijl er voorbeelden genoeg aanwezig zijn, maar ook de recorder is daar debet aan.
't Is niet anders.

 

[AD1957]

Beste Harry,

Het niveau wordt er hier niet beter op met sommige schrijfwijzes terwijl er voorbeelden genoeg aanwezig zijn, maar ook de recorder is daar debet aan.

Probeer met mijn zeer bescheiden kennis van VBA alleen maar te helpen en ben soms uren bezig om iets uit te zoeken.
Om dan zo iets te lezen.. niet zo leuk.

 

[HSV]

Had een keer F9+F8 gebruikt i.c.m. de muisaanwijzer, dan had je het niet geplaatst.

 

[AD1957]

Mijn code doorlopen met F8 lukt niet.(geen reactie)
Vreemd blijft dat het in mijn voorbeeldbestand wel werkt, zelfs zonder de dubbele "" om 2.
Ik heb het nu tich maal gecontroleerd. (of zie ik iets over het hoofd?)

Tja, en als ik naar de code van VenA kijk is deze natuurlijk vele malen beter. Heb nog veel te leren.

 

[JeroenSturk]

Hallo,

Bedankt voor alle reactie!
Ben sinds kort pas bekend met dit soort codes, dus waarschijnlijk zal ik iets verkeerd doen.
Want ik heb nog steeds hetzelfde probleem, bij de overgang van week 33 naar week 35.

Kan iemand mij dit uitleggen?

Wederom bedankt!

 

[AD1957]

Doe het bestand waarin het niet werkt eens uploaden.

 

[JeroenSturk]

Hierbij het bestand;

 

[AD1957]

Geen idee waardoor dit komt. Even wachten op iemand die meer kennis van VBA heeft.
En anders test het bestand in #16 eens.

ipv weeknummer gewoon de datum in B9 veranderen. ( in B10 staat hier een formule), de code is ook aangepast Case B9 ipv Case B10
Beide codes kun je eens testen. dmv. vinkjes weghalen/vinkjes plaatsen