Solved

IF AND

Posted on 2012-03-15
10
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 42

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 42

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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