Solved

IF AND

Posted on 2012-03-15
10
209 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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 use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

776 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