Create Report(?) to report on rows with empty cells
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Create Report(?) to report on rows with empty cells

  1. #11
    Board Regular
    Join Date
    Oct 2011
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create Report(?) to report on rows with empty cells

    Quote Originally Posted by surkdidat View Post
    (DATA TAB)
    Internal Ref External Ref DETAILS DATE REC'D REQUESTOR AREA PRIORITY DEVELOPER STATUS STATUS2 STATUS DATE ANALYST Type Price Comments
    1 1234 Request 1 01/06/2019 Adam 1 A Steve Stage 1 A 03/06/2019 Sophie 2 500 On Target
    2 1235 Request 2 02/06/2019 Peter 1 B Phil Stage 1 B 03/06/2019 Ellie On Target
    3 1236 Request 3 03/06/2019 Andy 1 A Tom Stage 3 C 05/06/2019 Kaitlyn 60 50
    4 1237 Request 4 03/06/2019 Katie 1 C Stuart Stage 4 B 08/06/2019 Hannah

    (Before Macro is Run This Table is completely empty of any data)
    (REPORT TAB)



    MACRO IS NOW RUN.



    (REPORT TAB)
    External REFERENCE
    1235 Type Price
    1236 Comments
    1237 Type Price Comments




    1234 - has been fully filled out so the Lookup (renamed Report) should not return anything

    1235 - In Data Tab all fields except "Type" and "Price" have been filled out. On Report Tab (previously named lookup) it needs to highlight what the missing data is by naming the column header. So return result is TYPE and PRICE. All other fields in the table have been filled in, so should NOT appear on the Report Tab.

    1236 - Only empty field in Data Tab is comments, so the results should be the column header COMMENTS

    1237 - 3 empty cells for reference 1237, Type, Price, Comments. This is the text I need to appear in REPORT tab



    Hope that makes more sense!




    Quote Originally Posted by DanteAmor View Post
    This down here, was what you asked for:


    Do you need something different?
    Then you could explain again what you have, where you have it, and what you expect from the result.

    Please Note
    -----------------------
    One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
    Quote Originally Posted by surkdidat View Post
    (DATA TAB)
    Internal Ref External Ref DETAILS DATE REC'D REQUESTOR AREA PRIORITY DEVELOPER STATUS STATUS2 STATUS DATE ANALYST Type Price Comments
    1 1234 Request 1 01/06/2019 Adam 1 A Steve Stage 1 A 03/06/2019 Sophie 2 500 On Target
    2 1235 Request 2 02/06/2019 Peter 1 B Phil Stage 1 B 03/06/2019 Ellie On Target
    3 1236 Request 3 03/06/2019 Andy 1 A Tom Stage 3 C 05/06/2019 Kaitlyn 60 50
    4 1237 Request 4 03/06/2019 Katie 1 C Stuart Stage 4 B 08/06/2019 Hannah

    (Before Macro is Run This Table is completely empty of any data)
    (REPORT TAB)



    MACRO IS NOW RUN.



    (REPORT TAB)
    External REFERENCE
    1235 Type Price
    1236 Comments
    1237 Type Price Comments




    1234 - has been fully filled out so the Lookup (renamed Report) should not return anything

    1235 - In Data Tab all fields except "Type" and "Price" have been filled out. On Report Tab (previously named lookup) it needs to highlight what the missing data is by naming the column header. So return result is TYPE and PRICE. All other fields in the table have been filled in, so should NOT appear on the Report Tab.

    1236 - Only empty field in Data Tab is comments, so the results should be the column header COMMENTS

    1237 - 3 empty cells for reference 1237, Type, Price, Comments. This is the text I need to appear in REPORT tab



    Hope that makes more sense!




    Quote Originally Posted by DanteAmor View Post
    This down here, was what you asked for:


    Do you need something different?
    Then you could explain again what you have, where you have it, and what you expect from the result.

    Please Note
    -----------------------
    One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

  2. #12
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Create Report(?) to report on rows with empty cells

    Quote Originally Posted by surkdidat View Post
    1234 - has been fully filled out so the Lookup (renamed Report) should not return anything

    1235 - In Data Tab all fields except "Type" and "Price" have been filled out. On Report Tab (previously named lookup) it needs to highlight what the missing data is by naming the column header. So return result is TYPE and PRICE. All other fields in the table have been filled in, so should NOT appear on the Report Tab.

    1236 - Only empty field in Data Tab is comments, so the results should be the column header COMMENTS

    1237 - 3 empty cells for reference 1237, Type, Price, Comments. This is the text I need to appear in REPORT tab
    Try this

    Code:
    Sub Create_Report()
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
        Dim k As Long, lr As Long
        
        Application.ScreenUpdating = False
        Set sh1 = Sheets("Data")
        Set sh2 = Sheets("LOOK UP")
        sh2.Cells.ClearContents
        For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
            lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
            k = 2
            For j = Columns("C").Column To sh1.Cells(1, Columns.Count).End(xlToLeft).Column
                If sh1.Cells(c.Row, j).Value = "" Then
                    sh2.Cells(lr, "A").Value = c.Value
                    sh2.Cells(lr, k).Value = sh1.Cells(1, j).Value
                    k = k + 1
                End If
            Next
        Next
        Application.ScreenUpdating = True
        MsgBox "End"
    End Sub
    Regards Dante Amor

  3. #13
    Board Regular
    Join Date
    Oct 2011
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create Report(?) to report on rows with empty cells

    Excellent - that does the job, just I need it to only do Columns A through to K, and Columns R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN

    Is it (as an afterthought) possible to ALWAYS include the contents on Column L where the below applies please? (sorry!)


    Quote Originally Posted by DanteAmor View Post
    Try this

    Code:
    Sub Create_Report()
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
        Dim k As Long, lr As Long
        
        Application.ScreenUpdating = False
        Set sh1 = Sheets("Data")
        Set sh2 = Sheets("LOOK UP")
        sh2.Cells.ClearContents
        For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
            lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
            k = 2
            For j = Columns("C").Column To sh1.Cells(1, Columns.Count).End(xlToLeft).Column
                If sh1.Cells(c.Row, j).Value = "" Then
                    sh2.Cells(lr, "A").Value = c.Value
                    sh2.Cells(lr, k).Value = sh1.Cells(1, j).Value
                    k = k + 1
                End If
            Next
        Next
        Application.ScreenUpdating = True
        MsgBox "End"
    End Sub

  4. #14
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Create Report(?) to report on rows with empty cells

    Quote Originally Posted by surkdidat View Post
    Excellent - that does the job, just I need it to only do Columns A through to K, and Columns R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN
    please? (sorry!)

    That's why my insistence that you include in your explanation what you need.
    And that was not in your explanation, that's why the macro does not do it.
    You could explain again, with complete examples.


    And finally, this part I do not understand.
    Is it (as an afterthought) possible to ALWAYS include the contents on Column L where the below applies
    If it is a requirement, you must explain it with examples.
    Regards Dante Amor

  5. #15
    Board Regular
    Join Date
    Oct 2011
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create Report(?) to report on rows with empty cells

    Hi DanteAmor

    Firstly, thank you for all your help so far. The code is running "perfectly", ie is is bringing up the column headers for anything that is empty.

    So for the columns listed (Columns A through to L, and then any blank cells from Column R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN) - these need to be included and all other columns ignored


    So the code to do exactly the same but only for Columns A, C, D, E, F, G, H, I, J, K, L, R, S, T, U, V, W, X, Y, AA, AB, AE, AF, AK, AL, AM, AN

    If the column M, N, O, P, Q Z, AC, AD, AG, AH, AI, AJ are blank/contain text I need the VBA to ignore these.

    With Column L to have the contents displayed alongside the reference number (this is the name of who is dealing with it) so makes it easier to deal with

    So on the below I have Column L as the Analyst, this on, any information the Macro generates, I would like to include next to the reference number.

    On the below data this goes from A-L, and then restarts at Column R. Any information (for this report based in
    M, N, O, P, Q Z, AC, AD, AG, AH, AI, AJ are completely irrelevant, regardless of any content or not, and should be ignored by this please.

    Apologies again, I posted it in my initial post, and then when trying to explain again, I forgot to include it in my requirements!!


    (DATA TAB)
    A B C D E F G H I J K L R S T
    Internal Ref External Ref DETAILS DATE REC'D REQUESTOR AREA PRIORITY DEVELOPER STATUS STATUS2 STATUS DATE ANALYST Type Price Comments
    1 1234 Request 1 01/06/2019 Adam 1 A Steve Stage 1 A 03/06/2019 Sophie 2 500 On Target
    2 1235 Request 2 02/06/2019 Peter 1 B Phil Stage 1 B 03/06/2019 Ellie On Target
    3 1236 Request 3 03/06/2019 Andy 1 A Tom Stage 3 C 05/06/2019 Kaitlyn 60 50
    4 1237 Request 4 03/06/2019 Katie 1 C Stuart Stage 4 B 08/06/2019 Hannah









    Quote Originally Posted by DanteAmor View Post
    That's why my insistence that you include in your explanation what you need.
    And that was not in your explanation, that's why the macro does not do it.
    You could explain again, with complete examples.


    And finally, this part I do not understand.


    If it is a requirement, you must explain it with examples.

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

    Default Re: Create Report(?) to report on rows with empty cells

    Quote Originally Posted by surkdidat View Post
    Hi DanteAmor

    Firstly, thank you for all your help so far. The code is running "perfectly", ie is is bringing up the column headers for anything that is empty.

    So for the columns listed (Columns A through to L, and then any blank cells from Column R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN) - these need to be included and all other columns ignored


    So the code to do exactly the same but only for Columns A, C, D, E, F, G, H, I, J, K, L, R, S, T, U, V, W, X, Y, AA, AB, AE, AF, AK, AL, AM, AN

    If the column M, N, O, P, Q Z, AC, AD, AG, AH, AI, AJ are blank/contain text I need the VBA to ignore these.

    With Column L to have the contents displayed alongside the reference number (this is the name of who is dealing with it) so makes it easier to deal with

    So on the below I have Column L as the Analyst, this on, any information the Macro generates, I would like to include next to the reference number.

    On the below data this goes from A-L, and then restarts at Column R. Any information (for this report based in
    M, N, O, P, Q Z, AC, AD, AG, AH, AI, AJ are completely irrelevant, regardless of any content or not, and should be ignored by this please.

    Apologies again, I posted it in my initial post, and then when trying to explain again, I forgot to include it in my requirements!!


    (DATA TAB)
    A B C D E F G H I J K L R S T
    Internal Ref External Ref DETAILS DATE REC'D REQUESTOR AREA PRIORITY DEVELOPER STATUS STATUS2 STATUS DATE ANALYST Type Price Comments
    1 1234 Request 1 01/06/2019 Adam 1 A Steve Stage 1 A 03/06/2019 Sophie 2 500 On Target
    2 1235 Request 2 02/06/2019 Peter 1 B Phil Stage 1 B 03/06/2019 Ellie On Target
    3 1236 Request 3 03/06/2019 Andy 1 A Tom Stage 3 C 05/06/2019 Kaitlyn 60 50
    4 1237 Request 4 03/06/2019 Katie 1 C Stuart Stage 4 B 08/06/2019 Hannah

    Try this

    Code:
    Sub Create_Report()
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
        Dim k As Long, lr As Long, cols As Variant, cl As Variant
        
        Application.ScreenUpdating = False
        Set sh1 = Sheets("Data")
        Set sh2 = Sheets("LOOK UP")
        sh2.Cells.ClearContents
        
        cols = Array("A", "C", "D", "E", "f", "G", "H", "i", "J", "k", "L", "R", "S", "T", _
                     "U", "V", "W", "X", "Y", "AA", "AB", "AE", "AF", "AK", "AL", "AM", "AN")
        
        For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
            lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
            k = 2
            For Each cl In cols
                If sh1.Cells(c.Row, cl).Value = "" Then
                    sh2.Cells(lr, "A").Value = c.Value
                    sh2.Cells(lr, k).Value = sh1.Cells(1, cl).Value
                    k = k + 1
                End If
            Next
        Next
        Application.ScreenUpdating = True
        MsgBox "End"
    End Sub
    I still do not understand this part.
    Please put examples.
    So on the below I have Column L as the Analyst, this on, any information the Macro generates, I would like to include next to the reference number.
    Last edited by DanteAmor; Jun 20th, 2019 at 11:12 AM.
    Regards Dante Amor

  7. #17
    Board Regular
    Join Date
    Oct 2011
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create Report(?) to report on rows with empty cells

    REPORT TAB)

    External REFERENCE
    External Reference Analyst (Blank 1) (Blank 2) (Blank 3)
    1235 Ellie Type Price
    1236 Kaitlyn Comments
    1237 Hannah Type Price Comments


    The Analyst Data is in column L in the DATA Table. So for anything that would appear in the table normally, the Analyst needs to appear alongside the reference number (this is a mandatory field so will always be in the data tab)




    Quote Originally Posted by DanteAmor View Post
    Try this

    Code:
    Sub Create_Report()
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
        Dim k As Long, lr As Long, cols As Variant, cl As Variant
        
        Application.ScreenUpdating = False
        Set sh1 = Sheets("Data")
        Set sh2 = Sheets("LOOK UP")
        sh2.Cells.ClearContents
        
        cols = Array("A", "C", "D", "E", "f", "G", "H", "i", "J", "k", "L", "R", "S", "T", _
                     "U", "V", "W", "X", "Y", "AA", "AB", "AE", "AF", "AK", "AL", "AM", "AN")
        
        For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
            lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
            k = 2
            For Each cl In cols
                If sh1.Cells(c.Row, cl).Value = "" Then
                    sh2.Cells(lr, "A").Value = c.Value
                    sh2.Cells(lr, k).Value = sh1.Cells(1, cl).Value
                    k = k + 1
                End If
            Next
        Next
        Application.ScreenUpdating = True
        MsgBox "End"
    End Sub
    I still do not understand this part.
    Please put examples.

  8. #18
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Create Report(?) to report on rows with empty cells

    Quote Originally Posted by surkdidat View Post
    REPORT TAB)

    External REFERENCE
    External Reference Analyst (Blank 1) (Blank 2) (Blank 3)
    1235 Ellie Type Price
    1236 Kaitlyn Comments
    1237 Hannah Type Price Comments


    The Analyst Data is in column L in the DATA Table. So for anything that would appear in the table normally, the Analyst needs to appear alongside the reference number (this is a mandatory field so will always be in the data tab)

    Use this please

    Code:
    Sub Create_Report()
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
        Dim k As Long, lr As Long, cols As Variant, cl As Variant
        
        Application.ScreenUpdating = False
        Set sh1 = Sheets("Data")
        Set sh2 = Sheets("LOOK UP")
        sh2.Cells.ClearContents
        
        cols = Array("A", "C", "D", "E", "f", "G", "H", "i", "J", "k", "L", "R", "S", "T", _
                     "U", "V", "W", "X", "Y", "AA", "AB", "AE", "AF", "AK", "AL", "AM", "AN")
        
        For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
            lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
            k = 3
            For Each cl In cols
                If sh1.Cells(c.Row, cl).Value = "" Then
                    sh2.Cells(lr, "A").Value = c.Value
                    sh2.Cells(lr, "B").Value = sh1.Cells(c.Row, "L")
                    sh2.Cells(lr, k).Value = sh1.Cells(1, cl).Value
                    k = k + 1
                End If
            Next
        Next
        Application.ScreenUpdating = True
        MsgBox "End"
    End Sub
    Regards Dante Amor

  9. #19
    Board Regular
    Join Date
    Oct 2011
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create Report(?) to report on rows with empty cells

    Thank you so much for all this and being very patient with me,- sorry for all the confusion. Ironically, it has helped me understand the code more though having seen certain bits run, and then the extra bits added!

    Quote Originally Posted by DanteAmor View Post
    Use this please

    Code:
    Sub Create_Report()
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
        Dim k As Long, lr As Long, cols As Variant, cl As Variant
        
        Application.ScreenUpdating = False
        Set sh1 = Sheets("Data")
        Set sh2 = Sheets("LOOK UP")
        sh2.Cells.ClearContents
        
        cols = Array("A", "C", "D", "E", "f", "G", "H", "i", "J", "k", "L", "R", "S", "T", _
                     "U", "V", "W", "X", "Y", "AA", "AB", "AE", "AF", "AK", "AL", "AM", "AN")
        
        For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
            lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
            k = 3
            For Each cl In cols
                If sh1.Cells(c.Row, cl).Value = "" Then
                    sh2.Cells(lr, "A").Value = c.Value
                    sh2.Cells(lr, "B").Value = sh1.Cells(c.Row, "L")
                    sh2.Cells(lr, k).Value = sh1.Cells(1, cl).Value
                    k = k + 1
                End If
            Next
        Next
        Application.ScreenUpdating = True
        MsgBox "End"
    End Sub

  10. #20
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,261
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Create Report(?) to report on rows with empty cells

    Quote Originally Posted by surkdidat View Post
    Thank you so much for all this and being very patient with me,- sorry for all the confusion. Ironically, it has helped me understand the code more though having seen certain bits run, and then the extra bits added!
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

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
  •