Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: VBA - Push column I data down depending on F
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Push column I data down depending on F

    Hi - I have a report that spits out false info. Due date (F) populates 2 dates. In this case there are 4 x 27/06/19 that shouldn't exist. This in turn means the WIP Qty is 4 numbers short (I).
    How can I push (I) down everytime it finds the wrong date? That would also depend on Job # (C) being the same.
    As an FYI, the first line for each Job# will always be correct, its only the "required to make product xx" which stuffs up the report.

    Job # Item # Item Description Due Date Job Qty Picked Qty WIP Qty Status
    67111 Product 1 Product 1 4/07/2019 4 0 2
    67111 Product 2 Product 2 4/07/2019 29 0 2
    67111 Product 3 Product 3 4/07/2019 9 9 1
    67111 Product 4 Product 4 4/07/2019 51 51 1
    67111 Product 4a required to make product 4 27/06/2019 51 125 Red
    67111 Product 4b required to make product 4 27/06/2019 102 25 2
    67111 Product 4c required to make product 4 27/06/2019 51 0 2
    67111 Product 5 Product 5 4/07/2019 125 2
    67111 Product 6 Product 6 4/07/2019 25 2
    67111 Product 6a required to make product 6 27/06/2019 25 2
    67111 Product 7 Product 7 4/07/2019 2 2
    67200 Product 20 Product 20 4/07/2019 7 7 1
    67200 Product 20a required to make product 20 27/06/2019 7 0 2
    67200 Product 27 Product 27 4/07/2019 1 2
    67200 Product 27a required to make product 27 27/06/2019 4 2

    Not even sure its possible!
    Thanks in advance

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,605
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA - Push column I data down depending on F

    Here's a manual way to do it. So should be able to use as a basis for creating a macro.

    • Select the first Job # from column C to column H
    • Sort by column E (Item Description) so that cells in column E containing "required..." are at the bottom (might need a temporary helper column to do this)
    • Select the first Job # from column C to column I and sort by the original sequence
    • Repeat above steps for each of the Job #'s

  3. #3
    Board Regular
    Join Date
    Aug 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Push column I data down depending on F

    Hi Footoo,

    Sensitive data and all that.... i changed the physical names for product and description...
    The "required to make" description is much like the items they go in and nothing to identify there is a difference. The only way i knew it spat out false info as described was looking into the blanks for jobs as that shouldn't happen.

  4. #4
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,605
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA - Push column I data down depending on F

    Quote Originally Posted by marshy3300 View Post
    Hi Footoo,

    Sensitive data and all that.... i changed the physical names for product and description...
    The "required to make" description is much like the items they go in and nothing to identify there is a difference. The only way i knew it spat out false info as described was looking into the blanks for jobs as that shouldn't happen.
    So what about in step 2 sorting by Due Dates (to get the wrong dates at the bottom) instead of by Item Decription?

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,991
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: VBA - Push column I data down depending on F

    Quote Originally Posted by marshy3300 View Post
    Hi - I have a report that spits out false info. Due date (F) populates 2 dates. In this case there are 4 x 27/06/19 that shouldn't exist. This in turn means the WIP Qty is 4 numbers short (I).
    How can I push (I) down everytime it finds the wrong date? That would also depend on Job # (C) being the same.
    As an FYI, the first line for each Job# will always be correct, its only the "required to make product xx" which stuffs up the report.

    Job # Item # Item Description Due Date Job Qty Picked Qty WIP Qty Status
    67111 Product 4 Product 4 4/07/2019 51 51 1
    67111 Product 4a required to make product 4 27/06/2019 51 125 Red
    67111 Product 4b required to make product 4 27/06/2019 102 25 2
    67111 Product 4c required to make product 4 27/06/2019 51 0 2

    Not even sure its possible!
    Thanks in advance
    There is some pattern to identify product 4 and its "required"

    I already have the macro, we just need you to say what the pattern is.
    Code:
    Sub test()
        Dim a As Variant, n As Long, i As Long, r As Range, lr As Long, m As Long, j As Long
        lr = Range("C" & Rows.Count).End(xlUp).Row
        Set r = Range("C2:C" & lr)
        For i = 2 To lr
            n = 1
            m = WorksheetFunction.CountIf(r, Cells(i, "C"))
            a = Range("I" & i & ":I" & i + m - 1)
            For j = i To i + m - 1
                If LCase(Cells(j, "E")) Like LCase("required*") Then
                    Cells(j, "I") = ""
                Else
                    Cells(j, "I") = a(n, 1)
                    n = n + 1
                End If
            Next
            i = i + m - 1
        Next
    End Sub
    Regards Dante Amor

  6. #6
    Board Regular
    Join Date
    Aug 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Push column I data down depending on F

    Hi Foo.. As mentioned i changed the codes... There inst a sequence to how they would be listed per job. The code/descriptions could be listed down as Alpha, Gamma, Beta, 7Foxtrot, Sierra instead of in order. If i sort so the odd dates are down the bottom, I'll lose the alignment of product and quantity once sorted back?

  7. #7
    Board Regular
    Join Date
    Aug 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Push column I data down depending on F

    [QUOTE=DanteAmor;5307018]There is some pattern to identify product 4 and its "required"


    For j = i To i + m - 1
    If LCase(Cells(j, "E")) Like LCase("required*") Then
    Cells(j, "I") = ""
    Else


    Hi Dante, as mentioned i changed the description. There nothing identifiable. I suppose it should be something along the lines of
    Count how many lines per Job #, count how many WIP Qty. If the same, move on to next job #.
    If different find first different due date, push WIP qty down one and move to the next line
    Repeat.

    Unless theres any easier way, which escapes me!

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,991
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: VBA - Push column I data down depending on F

    [QUOTE=marshy3300;5307024]
    Quote Originally Posted by DanteAmor View Post
    There is some pattern to identify product 4 and its "required"

    Hi Dante, as mentioned i changed the description. There nothing identifiable. I suppose it should be something along the lines of
    Count how many lines per Job #, count how many WIP Qty. If the same, move on to next job #.
    If different find first different due date, push WIP qty down one and move to the next line
    Repeat.

    Unless theres any easier way, which escapes me!
    Job # Item # Item Description Due Date Job Qty Picked Qty WIP Qty Status
    67111 Product 4 Product 4 4/07/2019 51 51 1
    67111 Product 4a required to make product 4 27/06/2019 51 125 Red
    67111 Product 4b required to make product 4 27/06/2019 102 25 2
    67111 Product 4c required to make product 4 27/06/2019 51 0 2

    You can investigate how to identify that 4a, 4b and 4c belong to Product 4, the Item, the desc, something, I do not know your information, so you must be the one to provide us with the pattern.
    Regards Dante Amor

  9. #9
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,605
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA - Push column I data down depending on F

    What about adjusting DanteAmor's macro to identify dates instead of item description :
    Code:
    Sub test()
        Dim a As Variant, n As Long, i As Long, r As Range, lr As Long, m As Long, j As Long, d As Date
        lr = Range("C" & Rows.Count).End(xlUp).Row
        Set r = Range("C2:C" & lr)
        For i = 2 To lr
            n = 1
            m = WorksheetFunction.CountIf(r, Cells(i, "C"))
            a = Range("I" & i & ":I" & i + m - 1)
            d = Cells(i, "F")
            For j = i To i + m - 1
                If Cells(j, "F") <> d Then
                    Cells(j, "I") = ""
                Else
                    Cells(j, "I") = a(n, 1)
                    n = n + 1
                End If
            Next
            i = i + m - 1
        Next
    End Sub
    Last edited by footoo; Jul 10th, 2019 at 02:13 AM.

  10. #10
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,605
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA - Push column I data down depending on F

    Quote Originally Posted by marshy3300 View Post
    Hi Foo.. As mentioned i changed the codes... There inst a sequence to how they would be listed per job. The code/descriptions could be listed down as Alpha, Gamma, Beta, 7Foxtrot, Sierra instead of in order. If i sort so the odd dates are down the bottom, I'll lose the alignment of product and quantity once sorted back?
    If i sort so the odd dates are down the bottom, I'll lose the alignment of product and quantity once sorted back?
    Not so. There are simple ways of keeping the desired alignment.

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
  •