Solved: Finding non numeric in range using an array

[SeanJ]

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]

[Bob Phillips]

[vba]

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
ReDim Preserve ry(1 To 2, 1 To i)
ry(1, i) = ActiveSheet.Cells(intY, intZ).Value
ry(2, i) = ActiveSheet.Cells(intY, intZ).Address
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]

[mdmackillop]

Hi Sean
When refering to Row numbers use Long rather than Integer as the latter cannot handle values above 36767. Simpler in fact to use Long instead of Integer in all cases.

[SeanJ]

Thx

do you know of a different way of finding all the cells that are not numeric and display their value and cell address?

[mdmackillop]

Is there a problem with the posted solution?

[SeanJ]

No it was fine. I was just hoping another way of solving it. I have looked all over the web for help but I realize that you just can't read an address of one cell of an array of a range.

I will leave open for two day if nothing is posted then I will mark solved.