PDA

View Full Version : Solved: validate data



Ger
08-24-2011, 01:32 AM
Hi,

again I need some help. I'm looking for a way to validate input in columns with a list.

The input is validated but it is still possible to use numbers double or to don't use them etc.

I think if you look at the example you see where i'm looking for.

Thx,

Ger

Bob Phillips
08-24-2011, 02:03 AM
Sorry, that makes little sense to me. Can you expand on what is happening/not happening as you want.

Ger
08-24-2011, 02:12 AM
In A1 to H27 (in the real sheet a205 to GH285) you see the input. This input is validated (allowed codes). In B30 to B35 are the numbers that must occur in B1 to B27, in C30 to C35 the numbers for C1 to C27 etc.
Where i'm looking for is a output like line 39 to 57 (double numbers, forgotten numbers, not allowed numbers on that specific day.

Ger

Bob Phillips
08-24-2011, 06:15 AM
One way



Public Sub ProcessData()
Const ROW_DATA As Long = 2
Const ROW_ALLOWED As Long = 30
Const ROW_INVALID As Long = 45
Const ROW_MISSING As Long = 50
Const ROW_DOUBLE As Long = 54
Dim NextInvalid As Long
Dim NextMissing As Long
Dim NextDouble As Long
Dim This As Variant
Dim Lastcol As Long
Dim i As Long, j As Long

With ActiveSheet

Lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For j = 2 To Lastcol

If Application.CountA(.Cells(ROW_DATA, j).Resize(26)) > 0 Then

NextInvalid = ROW_INVALID
NextMissing = ROW_MISSING
NextDouble = ROW_DOUBLE
For i = 2 To 27

This = .Cells(i, j).Value
If IsNumeric(This) Then

If IsError(Application.Match(This, .Cells(ROW_ALLOWED, j).Resize(6), 0)) Then

.Cells(NextInvalid, j).Value = This
NextInvalid = NextInvalid + 1
End If
End If
Next i

For i = ROW_ALLOWED To ROW_ALLOWED + 5

If IsError(Application.Match(.Cells(i, j).Value, .Cells(ROW_DATA, j).Resize(26), 0)) Then

.Cells(NextMissing, j).Value = .Cells(i, j).Value
NextMissing = NextMissing + 1
End If

If Application.CountIf(.Cells(ROW_DATA, j).Resize(26), .Cells(i, j).Value) > 1 Then

.Cells(NextDouble, j).Value = .Cells(i, j).Value
NextDouble = NextDouble + 1
End If
Next i
End If
Next j
End With
End Sub

Ger
08-24-2011, 06:36 AM
XLD,

thx again. i'm going to try to adjust this for my worksheet. In the example it works fine.

Ger