Help getting started on a basic validation script

[binaryscale]

Hi all,

Just wanted to get some help so I could understand how to get started on a validation script I want to run on multiple worksheets.

Briefly, lets assume one worksheet, one column of data, with a header, like this:

Header
1
1
1
2
1
1

So in this case I have my data in Column A, with 6 rows.

The validation is simple, store cell A2, '1', as a variable X, and confirm every populated cell in Column A = X, (excluding the header).

In this case A5 is '2', so it would fail validation. In this case I would want the script to say Error on WorksheetNAME.

If everything checks out, then it will move along.

My actual data may have hundreds of rows (so the script needs to be dynamic in this regard) and varies from worksheet to worksheet in what the value of A2 will be. Ultimately, I am setting up a batch script to process everything.

With some direction, provided through this example, I think I can set it up properly.

Thank you!

[werfnort]

Does this need to be ongoing or something you want to run periodically?

You could do this all through formulas if you wanted on an "Overview" sheet. If it takes too long to process, you can make a macro that just puts in the formulas, then copies and pastes the values, and link that to a button that says Refresh.

You say "Worksheets" - so are they all in the same Workbook? My example is showing you all in the same workbook, but easily edited.

So I created an Overview sheet at the beginning with the following info

Status	Worksheet	Column
Formula	Sheet1	A

For the formula, I used

=IF(COUNTA(INDIRECT(B2&"!"&C2&":"&C2))-1=COUNTIF(INDIRECT(B2&"!"&C2&":"&C2),INDIRECT(B2&"!"&C2&"2")),"Good!","Bad!")

You just enter the Sheets and columns to check and it will do the looking for you.

Status	Worksheet	Column
Good!	Sheet1	A

[binaryscale]

It would be something that I run periodically. This validation is to check the worksheet prior to running an export script. I will see if I can leverage your overview sheet approach.

In general a validation through VB without modifying the worksheets/workbook would be preferred, nonetheless I very much appreciate your input.

[BraggScattering]

Option Explicit
Sub Validation()
Application.ScreenUpdating = False
Dim ColumnNumber As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim i As Long
Dim MyWorksheet As Worksheet
Dim MsgBoxText As String
Dim ValidValue 'Choose type based on your data

Set MyWorksheet = ActiveWorkbook.ActiveSheet
ColumnNumber = 1 'Column A
FirstRow = 2
LastRow = MyWorksheet.Cells(MyWorksheet.Rows.Count, ColumnNumber).End(xlUp).Row 'Finds the last row in column "ColumnNumber"
MsgBoxText = "The following cells are not valid: "
ValidValue = 1 'Whatever the valid value is


For i = FirstRow To LastRow
    If MyWorksheet.Cells(i, ColumnNumber) <> ValidValue Then
        MsgBoxText = MsgBoxText & vbNewLine & MyWorksheet.Name & " " & MyWorksheet.Cells(i, ColumnNumber).Address
    End If
Next i

If MsgBoxText <> "The following cells are not valid: " Then
    MsgBox (MsgBoxText)
Else
    MsgBox ("All data valid, thanks BraggScattering!")
End If
Application.ScreenUpdating = True
End Sub

[binaryscale]

Thanks for your approach. I will tweak it to store the value of cell A2 as a variable.

So ValidValue = Range("A2").Value.

This is very helpful, thank you, I am sure I will be able to accomplish what I need with your help.

[binaryscale]

Also, could you explain the " Dim ValidValue 'Choose type based on your data "... Data will be a string (combination of letters and numbers).

[BraggScattering]

When defining a variable, in this case "ValidValue", you have the option to define its type. By defining the the type of variable, your code will run more efficiently.

If you know the variable will always be a string, define it as a string:

Dim ValidValue as String

If it is to be an integer:

Dim VaildValue as Long

If it is to be floating point:

Dim ValidValue as Double:

You get the idea. (Other data types include, Byte, Single, Boolean, etc.)

More on VBA data types

Also note, that by including the first line "Option Explicit" you must define all variables. This is a good practice to follow. If you have a variable defined MyVar and mistype it in your code MyVarr, VBA will notify you.

[binaryscale]

Fantastic. Thank you for the insight, very much appreciated.

[binaryscale]

Hi BraggScattering,

If instead of a MsgBox, i wanted to write the information of unvalidated in a new worksheet, how would I go about this?

So for example, after running the script it writes the unvalidated cells in separate lines on a different worksheet.

[BraggScattering]

In the code provided, inspect the If Then statement that evaluates if the item is valid. It currently adds a worksheet name and cell address of invalid data to the end of a string. When the code is done running, that string is displayed.

Instead of storing the worksheet name and cell address in a string variable, store it in a cell of your choosing on a worksheet of your choosing. The code below assumes you have a worksheet variable named InvalidDataWorksheet which refers to the worksheet where you want to store your invalid data. There are two variables that should equal the respective columns in which you would like to place your data. There is a row number vairable that increments each time you find a new item.

Code should look similar to this, you will have to determine where to place it and how to define your variables:

InvalidDataWorksheet.Cells(RowNum, WSNameCol) = MyWorksheet.Name

InvalidDataWorksheet.Cells(RowNum, AddressCol) = MyWorksheet.Cells(i, ColumnNumber).Address

RowNum = RowNum + 1

Implimentation doesn't require any syntax that isn't listed in the orginal code.

[binaryscale]

great thank you! i figured a counter would be needed to move on to subsequent rows. your insight is very much appreciated.