Solved

IF AND

Posted on 2012-03-15
10
216 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

628 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