Solved

IF AND

Posted on 2012-03-15
10
203 Views
Last Modified: 2012-03-16
Hi, i have put together some code where i am checking for text and the color of the cell

On the bolded line i am getting the error message

"Object doesn not support this property or method"

Can anyone see where i am making an error?

Thanks
Seamus

With ActiveSheet

R = .Range("C" & Rows.Count).End(xlUp).Row

Range("B2").Select
Selection = "=IF(COUNT(MATCH(F2,'Sophis Paris'!A:A,0),MATCH(F2,'Sophis US'!A:A,0),MATCH(F2,'Sophis HK'!A:A,0))>0,""match"",""no match"")"


 .Range("A2:B2").Copy .Range("A2:B" & R)
 
 End With

For Each cell In Range("A2:A10000")

Range("A2").Select

If cell.Offset(0, 1).Selection = "Match" And cell.Offset(0, 2).Interior.ColorIndex = 3 Then

cell.Offset(0, 2).Interior.ColorIndex = 4



End If

Next cell


End Sub
0
Comment
Question by:Seamus2626
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37725706
You don't need to have the ".Selection" afterwards, in fact it's a waste and syntactically incorrect.  Try this instead...

    With ActiveSheet
        R = .Range("C" & .Rows.Count).End(xlUp).Row
        .Range("B2").Formula = "=IF(COUNT(MATCH(F2,'Sophis Paris'!A:A,0),MATCH(F2,'Sophis US'!A:A,0),MATCH(F2,'Sophis HK'!A:A,0))>0,""match"",""no match"")"
        .Range("A2:B2").Copy .Range("A2:B" & R)
    End With
    For Each Cell In .Range("A2:A10000")
        If Cell.Offset(0, 1).Value = "Match" And Cell.Offset(0, 2).Interior.ColorIndex = 3 Then
            Cell.Offset(0, 2).Interior.ColorIndex = 4
        End If
    Next Cell

Open in new window


HTH

Regards,
Zack Barresse
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37725709
Try this:
For Each cell In Range("A2:A10000")
    'Range("A2").Select
    If cell.Offset(0, 1) = "Match" And cell.Offset(0, 2).Interior.ColorIndex = 3 Then
        cell.Offset(0, 2).Interior.ColorIndex = 4
    End If
Next cell

Open in new window

0
 

Author Comment

by:Seamus2626
ID: 37725768
Hey guys, even though your code seems correct. When i run it, nothing happens.

Attached is the file where i have added where the code should turn the index to green but doesnt

Thanks
Seamus
test.xls
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 167 total points
ID: 37725805
Seamus - maybe this? In the example I see none of the cells in B contain "match".
With ActiveSheet
    R = .Range("C" & .Rows.Count).End(xlUp).Row
    .Range("B2").Formula = "=IF(COUNT(MATCH(F2,'Sophis Paris'!A:A,0),MATCH(F2,'Sophis US'!A:A,0),MATCH(F2,'Sophis HK'!A:A,0))>0,""match"",""no match"")"
    .Range("A2:B2").Copy .Range("A2:B" & R)
    For Each Cell In .Range("A2:A10000")
        If LCase(Cell.Offset(0, 1).Value) = "match" And Cell.Offset(0, 2).Interior.ColorIndex = 3 Then
            Cell.Offset(0, 2).Interior.ColorIndex = 4
        End If
    Next Cell
End With

End Sub

Open in new window

0
 
LVL 14

Assisted Solution

by:Zack Barresse
Zack Barresse earned 167 total points
ID: 37725855
As it seems you are trying to format conditionally based on a cell value*, why don't you just use conditional formatting?

* I also see it's based on the color of column C values.  I'm assuming this is another conditional format?  You would need to know that condition as well, since you have two checks.

In any case, you don't need code for this, but only Conditional Formatting.  If you would like help with that, please post what the relevant conditions are.

Regards,
Zack Barresse
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 166 total points
ID: 37727576
>>syntactically incorrect

Selection as a range object has the default property of .Value.  

Selection = "=formula" is syntactically correct.

@seamus, that code you posted was close, but was checking for different case of "Match".  The following converts the case of the find and match string to MATCH and it works properly now.

Sub test()

    With ActiveSheet
        For Each cell In .Range("A1:A10000")
            If UCase(cell.Offset(0, 1)) = "MATCH" And cell.Offset(0, 2).Interior.ColorIndex = 3 Then
                cell.Offset(0, 2).Interior.ColorIndex = 4
            End If
        Next cell

    End With
End Sub

Open in new window


PS - column B drives the code, why not code as this (more efficiently):
Sub test()

    With ActiveSheet
        For Each cell In .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
            If UCase(cell.Value) = "MATCH" And cell.Offset(0, 1).Interior.ColorIndex = 3 Then
                cell.Offset(0, 1).Interior.ColorIndex = 4
            End If
        Next cell

    End With
End Sub

Open in new window

See attached.

Dave
test.xls
0
 

Author Closing Comment

by:Seamus2626
ID: 37728820
Thanks all, all sorted

Cheers
Seamus
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37730327
Dave,

I see where you're going, but I gotta say, I still disagree.  When I say "syntactically incorrect", I don't always necessarily mean "validly structured" or "non-compilable" or "will error out".  What I mean is that it is a non-professional way of coding.  We are allowed to make many exceptions when it comes to VBA, they really dumbed it down, didn't they?  I.e. the Value property is the default for the Range object.  ;)  Perhaps I should have rephrased.  Not best practice?  How does that work?

Selection = "=formula" is syntactically correct.
Wrong, Dave.  Does it work?  Sure, it'll compile, it'll get processed, it'll get executed, and it won't even throw an error of any kind.  Does that somehow imply it is "right" and we should use that as best practice?  What about in 2 years when somebody else has to debug or maintain your code, would it help them to have well written, well commented code, where you use properties fully and explicitly?  I'm going to assume you've been there (I think most of us have), and you know very well the answer (psst, it's YES).

Good catch on the last row methodology, I think I just got lazy.  :)

@Seamus: thanks for letting me have a soap box in your thread.  :)

Regards,
Zack Barresse
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37730388
Then saying it is not a good practice would be More appropriate than syntactically incorrect

I do the same each time I see select activate and unqualified range use


Dave
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37730458
Touche.  I'll choose my words better.  :)

Have a great day, sir.

Zack
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now