Nederlandse formules met VBA gebruiken?

[dvejam]

Goedemiddag,

Ik heb een VBA code waar ik kolommen invoeg in een bestaande Excel. Met verticaal zoeken werkt dit prima echter nu wil ik ook formules toevoegen maar daarbij wordt het = teken niet geaccepteerd door VBA. Ik kan met onderstaande code dus wel de formules in de toegevoegde kolommen krijgen maar dan moet ik met F2 nog handmatig = voor de formule zetten.

Het gaat om de formules DATUM, MAAND, WEEKNUMMER en INTEGER in de code hieronder.

Ik heb ook een voorbeeld bestand bijgesloten. Op het tabblad DATA KB zie je formules terug.

Sub Dennis1()
Application.ScreenUpdating = False
With Sheets("Data KB")
    .Cells.Clear
        With Sheets("Import")
            .Cells(1).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Criteria").Range("A1:A2")
            .UsedRange.Cells.Copy Sheets("Data KB").[A1]
            .Cells.AutoFilter
        End With
       .Columns.AutoFit
       .Cells(1).CurrentRegion.RemoveDuplicates Array(9, 14), xlYes
       .Columns(3).Insert
       .Cells(1, 3) = "Oorsprong"
       .Columns(12).Insert
       .Cells(1, 12) = "Opnamedatum"
       .Columns(13).Insert
       .Cells(1, 13) = "Maand"
       .Columns(14).Insert
       .Cells(1, 14) = "Week"
       .Columns(15).Insert
       .Cells(1, 15) = "Periode"
    .Range("C2").Formula = "=VLOOKUP(B2,Oorsprong!$A:$B,2,0)"
    .Range("C2:C" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Range("L2").Formula = "DATUM(DEEL(K2;1;4);DEEL(K2;5;2);DEEL(K2;7;2))"
    .Range("L2:L" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Range("M2").Formula = "=MAAND(L2)"
    .Range("M2:M" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Range("N2").Formula = "=WEEKNUMMER(L2)"
    .Range("N2:N" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Range("O2").Formula = "=INTEGER((M2+3)/4)"
    .Range("O2:O" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Columns.AutoFit
    .ListObjects.Add(xlSrcRange, .Cells(1).CurrentRegion.Resize(20000), _
                                 , xlYes).Name = "Tabel1"
End With
End Sub

 

[Cobbe]

Doe eens de test:
Enkel Weeknummer doet het niet omdat in L2 geen datum staat.

Sub Dennis1()
Application.ScreenUpdating = False
With Sheets("Data KB")
    .Cells.Clear
        With Sheets("Import")
            .Cells(1).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Criteria").Range("A1:A2")
            .UsedRange.Cells.Copy Sheets("Data KB").[A1]
            .Cells.AutoFilter
        End With
       .Columns.AutoFit
       .Cells(1).CurrentRegion.RemoveDuplicates Array(9, 14), xlYes
       .Columns(3).Insert
       .Cells(1, 3) = "Oorsprong"
       .Columns(12).Insert
       .Cells(1, 12) = "Opnamedatum"
       .Columns(13).Insert
       .Cells(1, 13) = "Maand"
       .Columns(14).Insert
       .Cells(1, 14) = "Week"
       .Columns(15).Insert
       .Cells(1, 15) = "Periode"
    .Range("C2").FormulaLocal = "=Vert.zoeken(B2;Oorsprong!$A:$B;2;0)"
    .Range("C2:C" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Range("L2").FormulaLocal = "=DATUM(DEEL(K2;1;4);DEEL(K2;5;2);DEEL(K2;7;2))"
    .Range("L2:L" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Range("M2").FormulaLocal = "=MAAND(L2)"
    .Range("M2:M" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    '.Range("N2").FormulaLoal = "=WEEKNUMMER(L2;1)"
    .Range("N2:N" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Range("O2").FormulaLocal = "=INTEGER((M2+3)/4)"
    .Range("O2:O" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Columns.AutoFit
    .ListObjects.Add(xlSrcRange, .Cells(1).CurrentRegion.Resize(20000), _
                                 , xlYes).Name = "Tabel1"
End With
End Sub

 

[dvejam]

Hoi Cobbe,

Getest en werkt. Datum in L2 ga ik baar kijken.

Dank voor snelle reactie.

Dennis

 

[VenA]

VBA is Engelstalig. De Nederlandse versie(s) hebben verschillende vertalingen van de 'locale formules' gebruik deze dan ook nooit.!

Maak een tabel ook nooit groter dan het aantal rijen dat je nodig hebt.

Sub VenA()
With Sheets("Data KB")
  .Cells.Clear
  With Sheets("Import")
    .Cells(1).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Criteria").Range("A1:A2")
    .UsedRange.Cells.Copy Sheets("Data KB").[A1]
    .Cells.AutoFilter
  End With
  .Cells(1).CurrentRegion.RemoveDuplicates Array(9, 14), xlYes
  .Range("C:C, K:N").Columns.Insert
  .Cells(1, 3) = "Oorsprong"
  .Cells(1, 12).Resize(, 4) = Split("Opnamedatum Maand Week Periode")
    .Range("C2").Formula = "=VLOOKUP(B2,Oorsprong!$A:$B,2,0)"
    .Range("C2:C" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Range("L2").Formula = "=DATE(LEFT(K2,4),MID(K2,5,2),RIGHT(K2,2))"
    .Range("M2").Formula = "=month(L2)"
    .Range("N2").Formula = "=WEEKNum(L2)"
    .Range("O2").Formula = "=INT((M2+3)/4)"
    .Range("L2:O" & .Cells(Rows.Count, 5).End(xlUp).Row).FillDown
    .Columns.AutoFit
    .ListObjects.Add(xlSrcRange, .Cells(1).CurrentRegion, , xlYes).Name = "Tabel1"
End With
End Sub

 

[jkpieterse]

Tip: Als je de Engelse vertaling van een formule nodig hebt:

- Selecteer de cel
- Alt-F11, control+g
- type in:
?ActiveCell.Formula
en druk enter.

 

[HSV]

Zinvolle truc @JKP. :thumb:

 

[dvejam]

Bedankt voor de reacties allemaal.