Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3D Array Problem | Excel Worksheet Functions | |||
Problem with array ref (I think!) | Excel Worksheet Functions | |||
Array problem, I think.. | Excel Worksheet Functions | |||
Array problem - TIA | Excel Worksheet Functions | |||
Array Problem | Excel Discussion (Misc queries) |