Results 1 to 7 of 7

Thread: Macro Assistance

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Macro Assistance

    Hello!
    Ok; I've never created a macro before and would really appreciate some help.
    I am trying to create a spreadsheet that will populate data based on a few criteria so that I do not have to constantly type everything out each and every time. On one tab, I want to have a start date and an end date. I also will have a list of companies in one column and products in the column next to it. I would like to have a macro create a row for each Company-Product for every date between the start date and end date (including those dates). I would like the data to have the Date in Column B, Company in Column C, and Product listed in Column D.

    Please see the sample tables below. Any assistance is greatly appreciated and do not hesitate to reach out for any clarification!

    Criteria
    Start Date: 12/01/18
    End Date: 12/03/18

    Company Product
    Lay’s Potato Chips
    Wise Potato Chips
    Utz Thin Pretzels
    Hershey’s Kisses
    Hershey’s Kisses with Almonds


    Result:
    Date Company Product
    12/01/18 Lay’s Potato Chips
    12/02/18 Lay’s Potato Chips
    12/03/18 Lay’s Potato Chips
    12/01/18 Wise Potato Chips
    12/02/18 Wise Potato Chips
    12/03/18 Wise Potato Chips
    12/01/18 Utz Thin Pretzels
    12/02/18 Utz Thin Pretzels
    12/03/18 Utz Thin Pretzels
    12/01/18 Hershey’s Kisses
    12/02/18 Hershey’s Kisses
    12/03/18 Hershey’s Kisses
    12/01/18 Hershey’s Kisses with Almonds
    12/02/18 Hershey’s Kisses with Almonds
    12/03/18 Hershey’s Kisses with Almonds

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,718
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Macro Assistance

    What are the exact ranges your criteria and company/product are in?
    Is nothing going in column A on the destination tab?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Sep 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Assistance

    Column A would be a concatenate of the Company, Product, Date, ie: Hershey’s_Kisses_12/02/18
    For the criteria, whatever columns are easiest for you to help me with, I can work with. Though the criteria can be thousands of rows.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,718
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Macro Assistance

    OK. I am assuming that the start date is in cell B2 and the end date is in cell B3.
    And your company/product data is in columns A and B and starts on row 6.

    Here is code that will do what you want. I documented it so you can follow along to see what it is doing, and can easily change values (like sheet names and range values) to get it to work for you.
    Code:
    Sub MyCopyMacro()
    
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim dtStart As Date
        Dim dtEnd As Date
        Dim fr As Long
        Dim lr As Long
        Dim r As Long
        Dim i As Long
        Dim dte As Date
        Dim cmp As String
        Dim prd As String
        
        Application.ScreenUpdating = False
        
    '   Set sheet name
        Set ws1 = Sheets("Sheet1")
        Set ws2 = Sheets("Sheet2")
        
    '   Capture start/end dates
        dtStart = ws1.Range("B2")
        dtEnd = ws1.Range("B3")
        
    '   Set start row of companies and find ending row
        fr = 6
        lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
        
    '   Put headings on destination sheet
        ws2.Range("A1") = "Date"
        ws2.Range("B1") = "Company"
        ws2.Range("C1") = "Product"
        
    '   Set initial starting row value on destination sheet
        r = 2
        
    '   Make sure end date is after start date
        If dtStart > dtEnd Then
            MsgBox "The end date must be after the start date!"
            Exit Sub
        End If
        
    '   Loop through all data
        For i = fr To lr
    '       Capture values from current row
            cmp = ws1.Cells(i, "A")
            prd = ws1.Cells(i, "B")
    '       Loop through all dates
            For dte = dtStart To dtEnd
    '           Assign values to destination sheet
                ws2.Cells(r, "A") = cmp & "_" & prd & "_" & Format(dte, "mm/dd/yy")
                ws2.Cells(r, "B") = dte
                ws2.Cells(r, "C") = cmp
                ws2.Cells(r, "D") = prd
    '           Increment row number
                r = r + 1
            Next dte
        Next i
        
        Application.ScreenUpdating = True
    
        MsgBox "Process complete!"
    
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Sep 2018
    Location
    Ontario, Canada
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Assistance

    Hello,

    I have created the following:

    Code:
    Option Explicit
    
    
    
    
    
    
    Public Sub FillDates()
    
    
        Dim StartDate As Date
        Dim EndDate As Date
        Dim NextDay As Date
        
        
        Dim DataStart As Range
        Dim NewData As Range
        
        StartDate = Range("B1").Value
        EndDate = Range("B2").Value
        
        NextDay = StartDate
        
    
    
       
       Set DataStart = Range("A6")
       Set NewData = Range("A16")
    
    
        
        
        Do
              
            Do
    
    
                
                NewData.Value = NextDay
                Set NewData = NewData.Offset(0, 1)
                NewData = DataStart.Value
                
                Set NewData = NewData.Offset(0, 1)
                NewData = DataStart.Offset(0, 1).Value
                
                
                Set NewData = NewData.Offset(1, -2)
                
                Set DataStart = DataStart.Offset(1, 0)
            
                
            Loop Until IsEmpty(DataStart) = True
              
                Set DataStart = Range("A6")
               NextDay = NextDay + 1
        
        Loop Until NextDay > EndDate
        
    
    
    
    
    
    
    End Sub

    This would all be done on sheet1. You can modify as necessary.

    Start Date is B1
    End Date is B2

    Data table starts in A6

    Completed Data Starts in A16

  6. #6
    New Member
    Join Date
    Sep 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Assistance

    Thank you both sooo much! You are such a life saver!

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,718
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Macro Assistance

    You are welcome.
    And welcome to the Board!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •