Array Problem, Help Please.
 

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

Array Problem, Help Please.
 

change

For i = -1 To 4

to

For i = 0 To

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Array Problem, Help Please.
 

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" wrote in message
om...
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

Array Problem, Help Please.
 

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

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

or

Debug.Print "match"

Mean ?


Thanks.

Robert.

Array Problem, Help Please.
 

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.

Array Problem, Help Please.
 

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

(Bubu) wrote in message . com...
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

Array Problem, Help Please.
 

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

"Bubu" wrote in message
om...
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.

Array Problem, Help Please.
 

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" wrote in message
om...
Both working without problems, but just one question,
what does

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

or

Debug.Print "match"

Mean ?


Thanks.

Robert.

Array Problem, Help Please.
 

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.

Array Problem, Help Please.
 

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

(Bubu) wrote in message . com...
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.