Array Problem, Help Please.

[Bubu]

the following macro has to compare, cell by cell, in a
certain range, the cell value with a list of values,
but it is not working properly because
it seem there is a match in all three cells, but
the match is only in Range(A2), because "C" = "C"

Where is the problem ???

Thanks for Your suggestion.

Robert.


example

' A
---'----------------
1 ' t
--------------------
2 ' C
--------------------
3 ' 7
--------------------


Sub tester_1()
Dim cell As Range
For Each cell In Range("a1:a3")
Dim i As Integer
Dim x As Variant
x = Array("A", "B", "C", "D", "E")
For i = -1 To 4
On Error Resume Next
MsgBox x(i)
If cell.Value = x(i) Then
cell.Interior.ColorIndex = 4
MsgBox " Match in " & cell.Address & vbNewLine & cell.Value & " = " & x(i)
End If
Next i
Next cell
End Sub

 

[mudraker]

change

For i = -1 To 4

to

For i = 0 To

 

[Trevor Shuttleworth]

Robert

seems to work OK. Try it this way:

Sub tester_1()
Dim cell As Range
For Each cell In Range("a1:a3")
Dim i As Integer
Dim x As Variant
x = Array("A", "B", "C", "D", "E")
For i = 0 To 4 ' note the index is from 0 to 4 and not -1 to 4; -1
gives subscript out of range
Debug.Print cell.Value; " "; x(i)
On Error Resume Next ' this causes the "subscript out of range"
to be ignored
'MsgBox x(i) ' commented out
If cell.Value = x(i) Then
cell.Interior.ColorIndex = 4
'MsgBox " Match in " & cell.Address & vbNewLine & cell.Value & "
= " & x(i) ' commented out
Debug.Print "match"
End If
Next i
Next cell
End Sub

The output to the immediate window is as follows:

t A
t B
t C
t D
t E
C A
C B
C C
match
C D
C E
7 A
7 B
7 C
7 D
7 E

Regards

Trevor

 

[Bubu]

Both working without problems, but just one question,
what does

Debug.Print cell.Value; " "; x(i)

or

Debug.Print "match"

Mean ?


Thanks.

Robert.

 

[Bubu]

How would You write a macro that compare,
if the cell value does not contain :
A-Z or a-z or 0-9.

Thanks.

Robert.

 

[onedaywhen]

If your comparison values follow a pattern (your example, letters A-E), exploit it:

Option Explicit

Sub tester_1()
Dim cell As Range
Dim i As Integer
For Each cell In Range("a1:a3")
i = Asc(cell.Value)
If i >= Asc("A") And i <= Asc("E") Then
cell.Interior.ColorIndex = 4
MsgBox " Match in " & cell.Address & vbNewLine & _
cell.Value & " = " & cell.Value
End If
Next cell
End Sub

 

[Cecilkumara Fernando]

Sub testthisone()
bos = Asc(UCase(Range("A1").Value))
If bos < 91 And bos > 64 Then
Debug.Print Range("A1").Value
ElseIf bos < 58 And bos > 47 Then
Debug.Print Range("A1").Value
Else
Debug.Print "U R Out"
End If
End Sub

 

[Trevor Shuttleworth]

Robert

sorry, it prints to the immediate window. In the VB Editor, press Ctrl-G or
View | Immediate Window. As you step through code you can check the value
in certain variable, etc. So, in this case, as you loop through the code,
it displays the values of the variables and whether there is a match.

The output listed in the earlier response was copied and pasted from the
Immediate Window.

Regards

Trevor

 

[Bubu]

the cell value does not contain :
A-Z or a-z or 0-9.




" i >= Asc("A") And i <= Asc("Z") "
is wrong because i need to catch cell that
....NOT CONTAIN .... "A-Z" or "a-z" or "0-9" value





Thanks for Everybody for Your Kind Help

Robert.

 

[onedaywhen]

Indeed, but you've changed you criteria since I posted. Here's an amended version:

Sub tester_1()
Dim cell As Range
Dim x As Long
For Each cell In Range("a1:a3").SpecialCells(xlCellTypeConstants)
x = Asc(UCase$(cell.Text))
If (x >= Asc("0") And x <= Asc("9")) _
Or (x >= Asc("A") And x <= Asc("Z")) Then
Else
MsgBox cell.Address & " does not contain A-Z nor a-z nor 0-9."
End If
Next cell
End Sub