1 Attachment(s)
Solved: Finding non numeric in range using an array
Ok I have this code here that works. I will alway have data starting in "D2" and the last column will be "G". The unknown is number of rows. I am trying to find the non numeric and displaying the value and the address in a message box so the user will have to fix the known errors before the rest of my other code runs.
I was attempting to create an array with a range and then verify for numeric and return all cells and their value that do not comply. If someone can lend a hand I would be grateful
[VBA]Option Explicit
Sub test()
Dim ry() As Variant
Dim IntX As Integer
Dim intY As Integer
Dim intZ As Integer
Dim i As Integer
Dim j As Integer
Dim flg As Boolean
Dim msgString As String
flg = True
i = 0
IntX = ActiveSheet.Range("c65000").End(xlUp).Row
ReDim ry(1 To 2, 1 To 1)
'///Check for numbers only
For intY = 2 To IntX 'row
For intZ = 4 To 7 'column
If IsNumeric(ActiveSheet.Cells(intY, intZ).Value) <> True Then
flg = False
i = i + 1
ry(1, i) = ActiveSheet.Cells(intY, intZ).Value
ry(2, i) = ActiveSheet.Cells(intY, intZ).Address
ReDim Preserve ry(1 To 2, 1 To i + 1)
End If
Next intZ
Next intY
'///////
'/////create msgbox
If flg = False Then
For j = i To 1 Step -1
msgString = ry(1, j) & " in " & ry(2, j) & vbCr & msgString
Next j
If j = 0 Then
MsgBox "found error in cell(s) " & vbCr & msgString
End If
End If
'///////
End Sub[/VBA]
