1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What's wrong with my loop?

Discussion in 'VBA Macros' started by Bomino, Jun 18, 2017.

  1. Bomino

    Bomino Member

    Messages:
    77
    Hi everyone,
    I need help troubleshooting my code or better yet help with a more efficient solution.
    I wrote a code and it seems not to be doing what it is supposed to do: pulling data from a worksheet (Data) and populate a column in another worksheet (Main) based on value from dropdown. Here is my code:

    Code (vb):
    Sub Retrieval()

        Dim x As Variant, y As Variant
        Dim RptDate As Range


    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

        Lastrow = DATA.Cells(Rows.Count, 1).End(xlUp).Row
         
      y = 18
       
        For x = 6 To Lastrow
              If DATA.Cells(x, 2) = MAIN.Cells(2, 8) And DATA.Cells(x, 3) = MAIN.Cells(y, 6) Then
             
              MAIN.Cells(y, 13) = DATA.Cells(x, 10)
           
              y = y + 1
            End If
         
        Next
       
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
        End With
       
    End Sub
    Am I missing something?
    Any help will be much appreciated.
    Thank you.
    I am also attaching a sample file

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    2,809
    Bomino
    How many same dates there should be?
    Dates would be same format (dd/mm/yyyy or mm/dd/yyyy) not mixed.
    ... You would check Your SOW# compare too ... 'which-to-which'.
  3. Bomino

    Bomino Member

    Messages:
    77
    vletm,
    Thank you for helping. There will be at most 14 same dates (or less depending on the data entry) because there are 14 different SOW#.
    The date format is (mm/dd/yyyy). I still can't make it work.
  4. vletm

    vletm Well-Known Member

    Messages:
    2,809
    Dates:
    Screen Shot 2017-06-18 at 21.07.34.png
    and why = MAIN.cells(y,6)
    ... anyway cannot ever match with those parameters.
  5. Bomino

    Bomino Member

    Messages:
    77
    Here is the desired output
    upload_2017-6-18_14-57-52.png
    Any suggestion as how to make it work?
  6. vletm

    vletm Well-Known Member

    Messages:
    2,809
    Bomino
    I would make it like this ...

    Attached Files:

  7. Bomino

    Bomino Member

    Messages:
    77
    Thank you vletm.
    I would to understand what I am doing wrong. Any help would appreciated.

Share This Page