Need to validate and copy the output into new sheet.

[Balaryan]

Hi Excelperts,

I am bala and i am new to this Forum. Basically i am a mainframe developer and have no clue about Excel Macros. But i have 1 request and need to resolve it ASAP. Would like you people to assist me in this and it will be really grateful.

Request is: We used to get the excel sheet which contains 9 columns with several values associated with it. Now what we are supposed to do is validation using macros. Conditions for the associated columns are mentioned below.

Note: It can be of lakh or million rows. Need to process till the empty row.

column 1: It should hold the value as always 3 (either text or integer)
column 2: It should hold the value as A, B, C and D
column 3: It should hold the value always Greater than zero.
column 4: It should hold the value be TRUE
column 5: It should hold the value be Y or N.
column 6: It should hold the value which is not equal to D or N
column 7: It should hold the value as zero
column 8: It should hold the value as always 5 (either text or integer)
column 9: It should hold the value be Y or N.

Out of this 9 column validation, if any of the condition not satisfies we going to skip that row.

Please let me know, how to incorporate this changes in MACROS..

[foxguy]

Hi bala;

Note: It can be of lakh or million rows.

I don't know what "lakh" is.

Assuming that all the data starts at Row 1 (no headings)
Here's a macro that cycles through all the rows and highlights in red the cells that fail the tests.

Sub Find_Good_Rows()
    Dim lLastRow as Long
    Dim lRow as Long
    Dim sBadCols as String
    Dim i as Integer
    Dim s as String

    lLastRow = Range("A" & Rows.Count).End(xlUp).Row
    For lRow = 1 to lLastRow
        sBadCols = ""     'Reset flag to mark bad cells
        If Val(Cells(lRow, "A")) <> 3 Then
            sBadCols = "A"
        End If
        If Instr("ABCD", Cells(lRow, "B")) = 0 Then
            sBadCols = sBadCols & "B"
        End If
        If Val(Cells(lRow, "C")) < 0 Then
            sBadCols = sBadCols & "C"
        End If
        If CBool(Cells(lRow, "D")) <> True Then
            sBadCols = sBadCols & "D"
        End If
        If Instr("YN", Cells(lRow, "E")) = 0 Then
            sBadCols = sBadCols & "E"
        End If
        If Instr("DN", Cells(lRow, "F")) > 0 Then
            sBadCols = sBadCols & "F"
        End If
        If Cells(lRow, "G") <> 0 Then
            sBadCols = sBadCols & "G"
        End If
        If Val(Cells(lRow, "H")) <> 5 Then
            sBadCols = sBadCols & "H"
        End If
        If Instr("YN", Cells(lRow, "I")) = 0 Then
            sBadCols = sBadCols & "I"
        End If
 
        If sBadCols = "" Then
            'This is where you put code to do what you need to do with good rows
        Else
           'change background color of Cells That failed Test
            For i = 1 to Len(sBadCols)
                s = Mid(sBadCols, i, 1)
                Cells(lRow, s).Interior.ColorIndex = 3
            Next i
        End If
    Next lRow    
End Sub

1) To put this Macro into the workbook: Click Alt+F11 (opens Visual Basic Editor (VBE))
2) In Menus Insert->Module (Window should open with title similar to : "yourWorkbook.xls - Module1 (Code)"
3) Paste code into that module
4) Click Alt+F11 (returns to Excel)
5) Click Alt+F8 (opens macro window)
6) Select "Find_Good_Rows" and "Run"

If you would like to step through the macro 1 line at a time then
6) Select "Find_Good_Rows" and "Step Into"
7) Click F8, and each line will highlight before executing. You can bounce back and forth between Excel and VBE to verify what it is doing is correct.
8) Click F5 to finish running macro without stopping.