Planning: loop through row of dates, copy non-blanks in column with corresponding titles

Question

The idea is to make a planning tool, based on a "database":

one row with dates, in the columns the needed transports,...
want to search on today and copy the non-blanks in that column to another sheet "dashboard"
want to copy the corresponding titles in the first columns of the "database" to the dashboard

Can't get it to work, searching around, and just don't get it, sorry. Novice in this... 2 questions:

how to solve error 91
how to dynamically select the right date (based on a loop through range) in a row with autofilter to get the data (non-blanks) in that column copied to another sheet?

Here's the code and the highlight where it gets stuck. If you want the file, let me know.

    Sub Transportplan()
'
' Transportplan Macro
'
' Sneltoets: Ctrl+Shift+T
    
    'ZET ALLES KLAAR VOOR NIEUWE PLANNING
    'Ga naar planningsoverzicht en delete vorige planning
    Sheets("NIEUW").Select
    Columns("B:G").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
        
    'Ga naar data tab
    Sheets("DATA").Select
    'Alle filters uitdoen
    ActiveSheet.ShowAllData
    'Activate search criteria in column
    ActiveSheet.Range("$A$4:$JN$196").AutoFilter Field:=5, Criteria1:=Array( _
        "Transport", "Transport INGEPAKT: Fase + (PALLETnrs)", _
        "Transport NIET ingepakt: Fase" & Chr(10) & "!!! RISICO NIET GELEVERD !!!", "Transport Retour" _
        ), Operator:=xlFilterValues
    
    '--------------------------------------------------------
    'START LOOP COPY PASTE SEQUENCE VOOR NIEUWE PLANNING
    
    '1. Choose the date in the tab "Datums voor macro"
    Sheets("Datums voor macro").Select
    'Loop through dates
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("B4:B31")
    For Each cell In rng
         
        '------------------
        'Search the date in the DATA tab
        Sheets("DATA").Select
        Cells.Find(What:="cell", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate


'HOW CAN I GET FIELD 21 dynamically changed if the date changes (in row 4)
   
        'If nothing that day, paste just the date
        ActiveSheet.Range("$A$4:$JN$1000").AutoFilter Field:=21, Criteria1:="<>"
        If (comboBox1.SelectedIndex = -1) Then
        'Go to planning and paste that day
        Sheets("NIEUW").Select
        Range("G1").Select
        ActiveCell.End(xlDown).Offset(1, 0).Select
        ActiveRange = cell.Value
        
            Else

'HOW CAN I GET FIELD 21 dynamically changed if the date changes (in row 4). I activated the macro through record and pressing Ctrl+F and pasting the date...

            ActiveSheet.Range("$A$4:$JN$196").AutoFilter Field:=21, Criteria1:="<>"
            Range(Selection, Selection.End(xlDown)).Select
            Application.CutCopyMode = False
            Selection.Copy
            'Go to planning and paste data
            Sheets("NIEUW").Select
            Range("G1").Select
            ActiveCell.End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste
            
            'Copy headers from DATA tab
            Sheets("DATA").Select
            Range("E4").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToLeft)).Select
            Range(Selection, Selection.End(xlToLeft)).Select
            Application.CutCopyMode = False
            Selection.Copy
            'PASTE HEADERS in planning
            Sheets("NIEUW").Select
            'Search next empty cel to paste under previous data
            Range("B1").Select
            ActiveCell.End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste
            
            End If
        
        'END LOOP 1
        '-----------------------------------
        
        'RESTART LOOP
    Next cell

End Sub

Maybe start by reviewing this and applying the suggestions to clean up your code: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Tim Williams, Nov 19 '20 at 21:50
Cleaned it up, but still gets stuck at the FIND.Activate option. What am I doing wrong? See answer — Peter Delva, Nov 24 '20 at 11:16

Tim Williams · Answer 1 · 2020-11-25T17:22:44.963

0

THis will give a runtime error if no match is found:

datadag.Find(What:="cell", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

Use this pattern instead:

Dim f As Range

Set f = datadag.Find(What:="cell", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

If Not f Is Nothing Then
    'do something with f
Else
    'handle "not found" case
End if

edited Nov 25 '20 at 17:22

answered Nov 24 '20 at 16:21

Tim Williams

154,628
8
97
125

Tim, thanks a lot for the swift reply! I'll give it a try. – Peter Delva Nov 25 '20 at 08:24
Tim, isn't your code doing something/nothing when the search is not nothing/nothing? I'm actually searching the column and then want to activate the filter in that column to copy the non-blank items in that column. If only blanks, the date... – Peter Delva Nov 25 '20 at 10:51
I was responding specifically to "Cleaned it up, but still gets stuck at the FIND.Activate" - to show how to handle cases where the Find() fails to make a match. – Tim Williams Nov 25 '20 at 17:23
Thanks a lot Tim, but it still doesn't work and I'm really desperate. Could you/or anyone you know, help out? Thanks! – Peter Delva Dec 01 '20 at 20:42
Tim, it works! Fantastic! Anyway to copy the format of the text in the cell, or should I write a specific Class for this? – Peter Delva Dec 08 '20 at 13:23
The format of the text in the cell that is copied (from data tab to plan tab) – Peter Delva Dec 29 '20 at 19:06
If you want to copy both the value and the format you can just copy/paste the cell in question. – Tim Williams Dec 30 '20 at 01:15

Planning: loop through row of dates, copy non-blanks in column with corresponding titles

1 Answers1