How to apply validation to each column using VBA

Question

i have the data in a sheet1 with 100+ columns. In sheet2 i have validation formula(Regular Expressions) for each column. i wants copy data from sheet1 to sheet3 by checking validation formula of each column before pasteing the data in sheet3. If it satisfies formula, copy that in sheet3 and fill the cell with green color or if not satisfies copy data to sheet3 and fill that cell with red color.

Sub test()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wbk As Workbook
    Dim sRegex As Variant
    Dim lColumn As Long
    Dim sText As String
    Set sRegex = CreateObject("VBScript.RegExp")
    Set wb = ActiveWorkbook
    wb.Sheets("Sheet3").Activate
    lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    lColumn = wb.Sheets("Sheet3").UsedRange.Column + wb.Sheets("Sheet3").UsedRange.Columns.Count - 1
    For i = 1 To lColumn
    temp = Cells(1, i).Value
    For j = 2 To lastRow
    sText = Cells(j, i).Value
        Set wb = ActiveWorkbook
         sheetName = ActiveSheet.Name
    wb.Sheets("Sheet4").Activate
    sheetName = ActiveSheet.Name

    lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For k = 2 To lRow
    temp2 = wb.Sheets("Sheet4").Cells(k, 1).Value
    If wb.Sheets("Sheet4").Cells(k, 1).Value = temp Then
   temp1 = wb.Sheets("Sheet4").Cells(k, 2).Value
    sRegex.Pattern = wb.Sheets("Sheet4").Cells(k, 2).Value
    sRegex.Global = True
     Debug.Print TestRegex(sText, sRegex)
     Else
     End If
    Next k  
    Next j
    Next i
End Sub

Function TestRegex(sInput As String, sRegex As Variant) As Boolean
    TestRegex = sRegex.test(sInput)
   ' ActiveCell.Interior.ColorIndex = 3
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    wb.Sheets("Sheet3").Activate
    Cells(Selection.Row, Columns.Count).End(xlToLeft).Offset(, 1).Select
    'wb.Sheets("Sheet3").Cells(2, 9).Value = TestRegex
    ActiveCell.Value = TestRegex

End Function

Robert J. Robert J. · Accepted Answer · 2016-06-22T07:04:36

I believe that you know how to copy the data, but you don't know how to validate object with Regex.

If I understood you correctly, you are copying row by row and you want to validate every column (from the row) based on the REGEX expression.

I am not quite sure how fast that would be, but what you need to do is to first create an object of Regex like following (I use Late Binding, but you are free to add it to the References and use Early Binding).

Here is something to get you started:

Function TestRegex(sInput As String, sRegex As Variant) As Boolean
    TestRegex = sRegex.test(sInput)
End Function


Sub test()
    Dim sRegex As Variant
    Set sRegex = CreateObject("VBScript.RegExp")
        sRegex.Pattern = "^[A-Z]" 'regex of your choice
        sRegex.Global = True

    Dim sText As String
        sText = "abc123"

    Debug.Print TestRegex(sText, sRegex)
End Sub

How to apply validation to each column using VBA

1 Answers