• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Bereik cellen

Status
Niet open voor verdere reacties.
M

martijnbos

Gebruiker
Lid geworden
17 dec 2010
Berichten
101
Hoi,

Ik heb de volgende vraag. Op blad1 wordt data neergezet uit een andere worksheet.
Bij deze date moet bv iets worden bijgezocht met vertical zoeken en moet er een sortering plaatsvinden. Probleen is echter dat ik vantevoren niet weet hoeveel regels er op het blad komen te staan. Nu staat er bv bij verticaal zoeken van A4:A790. Dit komt omdat mijn voorbeeld 790 regels bevat.
Heeft iemand een suggestie voor een flexibeler manier?
BIjgevoegd de code

Code: 
Sub Macro3()
With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("C:D").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Selection.RowHeight = 20
    Range("a4").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],Picklocaties!C[1]:C[2],2,0)"
    Selection.AutoFill Destination:=Range("A4:A790")
    Range("A4:A790").Select
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Picklocatie"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Gepicked door:"
    Range("F1").Select
    Range("A4:K790").Select
    ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Add Key:=Range("C4:C790") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Add Key:=Range("E4:E790") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Add Key:=Range("A4:A790") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Blad1").Sort
        .SetRange Range("A3:K790")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Een stukje uit je code met een aanvulling in het rood.

Code: 
Sheets("blad1").Range("a4").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],Picklocaties!C[1]:C[2],2,0)"
    Selection.AutoFill Destination:=Range("A4:A" [COLOR="red"]& Sheets("Picklocaties").Cells(Rows.Count, 2).End(xlUp).Row)[/COLOR]
 
Harry,

Bedankt voor je antwoord. Neem aan dat als de activiteit op blad1 plaatsvind ik gewoon "picklocaties"kan vervangen door blad1?
 
Hallo Martijn,

Je zoekt met je VlookUp in het blad Picklocaties in de kolommen B en C.
Ik neem aan dat de gezochte data daar ergens moet staan.
Hoeveel rijen kolom B van tabblad Picklocaties is weet je niet.
Vandaar de variabele lengte afhankelijk van kolom B van Picklocaties.

Mocht je de tabbladnaam Piclocaties veranderen in Blad1, dan zul je dat in je code moeten aangeven.
 
Harry,

Het probleem zit hem niet in blad " Picklocaties". Hierop staan 60 vestigingen met 60 picklocaties. Dit veranderd niet.
Het probleem zit hem op blad1. Op blad1. Daar kan dus 1 regel komen te staan, maar ook 600 regels.
Kortom, hij moet dus verticaal zoeken op blad1, waarbij de range waarvoor hij dit moet doen flexibel moet zijn. Dus de ene keer van A4:A790, maar de volgende keer A4:A10000

Nog een aanvulling. Dit probleem geldt voor meerdere stukjes codes. Bijvoorbeeld ook voor het sorteren.

With ActiveWorkbook.Worksheets("Blad1").Sort
.SetRange Range("A3:K790")
 
Laatst bewerkt:
Martijn,

Doe er anders je bestandje bij als je wil (zonder gevoelige info).
 
Harry,

Hierbij de bestanden. Wat er dus gebeurt is dat de data van bestand verdeelsleutel wordt geknipt en geplakt in bestand test verdeelsleutel. De macro gaat vervolgens het e.e.a. doen aan verticaal zoeken en sortering etc

Mijn probleem is dus dat de aangeleverde data in bestand verdeelsleutel variabel is. Dus in dit geval vier regels, maar de andere keer kunnen dat 1000 regels zijn. Nu staat in mijn macro o.a.bij verticaal zoeken van A4:A790 en ook bv bij de sortering. Deze kan je natuurlijk op A50000 zetten in de VBA code

Gevolg hiervan is veel regels met waarde #N/B in kolom A doordat deze regels geen waarde bevatten. Dit heeft vervolgens weer impact op de functie subtotaal in Excel 2003. Van de laatste regel staat het subtotaal onder de laatste regel van #N/B.

Kortom: ik kom er ff niet meer uit. Heb ook geprobeert om een code in te voegen in VBA die lege regels eruit gooide. Maar ook dat werkte niet goed.
 

Bijlagen

waarom kan je niet met een gedefinieerde naam werken in het 1e bestand dat verwijst naar het andere bestand, bv. in je kopie-bestand deze naam, die zich dynamisch aanpast aan de grootte in het andere bestand
Code: 
AKolom_Verdeelsleutel	=VERSCHUIVING([Verdeelsleutel.xls]Blad4!$A$4;;;AANTALARG([Verdeelsleutel.xls]Blad4!$A$4:$A$65000);)

en verder die macro aanpassen aan het aantal rijen.
Het is gemakkelijk als je de termen offset en resize ook onder de knie krijgt.
PS. de redenering van onderstaande kan misschien niet overeenstemmen met wat jij voor ogen hebt, want ik snap uit de uitleg precies niet wat waar moet komen en naar wat verwezen moet worden.
Code: 
Sub Macro3()
  Dim LaatsteRij As Long, BData As Range
  With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With
  With Sheets("blad1") 'in blad1
    .Columns("C:D").Delete Shift:=xlToLeft 'paar kolommen deleten
    .Cells.RowHeight = 20 'rijhoogtes aanpassen
    LaatsteRij = WorksheetFunction.Max(4, .Range("B" & Rows.Count).End(xlUp).Row)  'rij van de laatste niet-lege cel in B-kolom, met ondergrens 4
    Set BData = .Range("B4:B" & LastRow)                   'B-kolom in dat blad over het ganse bereik van gegevens
    BData.Offset(, -1).FormulaR1C1 = "=VLOOKUP(RC[1],Picklocaties!C[1]:C[2],2,0)"  'zet in de A-kolom de formules
    .Range("A3").Value = "Picklocatie"
    .Range("F1").Value = "Gepicked door:"
    BData.Offset(, -1).Resize(, 11).Sort key1:=.Range("C4"), key2:=.Range("E4"), key3:=.Range("A4"), Header:=xlNo 'Vanuit B-kolom 1 naar links en dan 11 kolommen breed sorteren
  End With
End Sub
 
Laatst bewerkt:
Beste allemaal,

Bedankt voor je antwoord maar het wordt voor mij iets te ingewikkeld.
Heb je code voor een deel gebruikt, maar hij liep vast op de subtotalen.
Ik ga denk ik kijken of ik regels met #n/B eruit kan gooien voordat ik de subtotalen maak.
In het geel staat het deel dat qua bereik variabel is.

Mocht iemand nog een idee hebben dan hoor ik het graag.



Code: 
Sub Macro3()
With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("C:C,D:D,F:F").Select
    Selection.Delete shift:=xlToLeft
    Cells.Select
    Selection.RowHeight = 20
    Range("a4").Select
    [COLOR="yellow"]ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],Picklocaties!C[1]:C[2],2,0)"
    Selection.AutoFill Destination:=Range("A4:A790")
    Range("A4:A790").Select[/COLOR]
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Picklocatie"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Gepicked door:"
    Range("F2").Select
    [COLOR="yellow"]Range("A4:K790").Select
    Range("A3:K790").Select[/COLOR]
    Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Key2:=Range("D4") _
        , Order2:=xlAscending, Key3:=Range("A4"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
   [COLOR="yellow"] Range("3:790").Select[/COLOR]    Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6, 9), _
        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
    Columns("A:A").ColumnWidth = 5.57
    Columns("B:B").ColumnWidth = 13.57
    Columns("D:D").ColumnWidth = 21.71
    Columns("F:F").ColumnWidth = 32.86
    Columns("K:K").ColumnWidth = 5.14
End Sub
 
Laatst bewerkt:
eigenlijk wil je deze formule vermoedelijk krijgen, dan zet die geen fout neer, maar een lege cel en dan krijg je geen fout in je subtotaal.
Code: 
        "=IF(ISERROR(VLOOKUP(RC[1],Picklocaties!R1C[1]:R65536C[2],2,0)),"""",VLOOKUP(RC[1],Picklocaties!R1C[1]:R65536C[2],2,0))"
Verder blijf ik een beetje doelloos ronddobberen, als je er niet uit raakt, plaats dan nog een keer dat bestandje met de macro erin.
 
Hoi,

Hierbij de code zover. Helaas ook niet de oplossing. Want het groeperen duurt waanzinning lang.

Hierbij de uitleg. Hoop dat het iets duidelijker wordt.

1. Er worden x aantal regels gekopieerd vanaf een ander werkblad. De X is dus de variabele. De ene keer kunnen er 100 regels worden geknipt en de volgende keer 1000
2. De X aantal regels worden dus geplakt op een ander werkblad waar deze code in zit
3. De macro moet o.a. de volgende zaken gaan doen

a. Verticaal zoeken. Op zich werkt dit, maar nu staat in mijn code hard cel A4:A5000. Op de plek van de "A5000" wil ik graag een variable die zich aanpast aan de hoeveelheid regels die zijn geplakt vanuit andere sheet
b. Sorteren. Idem dito. Hier wil ik graag sorteren maar dan ook zonder een hard eindbereik(k5000)
c. En als laatste een groepering. En dit uiteraard ook zonder hard eindbereik


Code: 
Sub Macro3()
With Selection ' cellen splitsen
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("C:C,D:D,F:F").Select ' cellen verwijderen
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Selection.RowHeight = 20 ' aanpassen rijhoogte
    Range("a4").Select ' verticaal zoeken voor kolom A
     ActiveCell.FormulaR1C1 = _
        "=IF(RC="""","""",VLOOKUP(RC[1],Picklocaties!C[1]:C[2],2,0))"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[1]="""","""",VLOOKUP(RC[1],Picklocaties!C[1]:C[2],2,0))"
    Selection.AutoFill Destination:=Range("A4:A5000"), Type:=xlFillDefault
    Range("A4:A5000").Select
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Picklocatie"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Gepicked door:"
    Range("F2").Select
    Range("A4:K5000").Select
    Range("A3:K5000").Select
    Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Key2:=Range("D4") _
        , Order2:=xlAscending, Key3:=Range("A4"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
    Range("3:5000").Select
    Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(6, 9), _
        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
    Columns("A:A").ColumnWidth = 5.57
    Columns("B:B").ColumnWidth = 13.57
    Columns("D:D").ColumnWidth = 21.71
    Columns("E:E").ColumnWidth = 32.86
    Columns("J:J").ColumnWidth = 5.14
End Sub
 
zonder echt rvoorbeeldje kan ik niet veel meer zeggen dan even te zoeken in de richting van application.screenupdating, calculation etc
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan