Macro help?

[John Davis]

Hello all:

I have the following, which does absolutely nothing, although the If/And statements are True.

Sub test()
Dim i As Long
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

    For i = lr To 2 Step -1
    
        If Range("C" & i).Value = "Yes" And Range("A" & i).Value = Range("D1").Value And Range("A" & i).Value = Range("A" & i).Offset(1, 0).Value Then
  
            Range("A" & i).select
                        
            MsgBox ("Value found at Row " & ActiveCell.Row & "! Stop")
            
        End If
        
    Next

End Sub

Perhaps someone can see something I missed or not understanding.

 

[Sandeep Warrier]

Hi

Part of your If statement says

Range("A" & i).Value = Range("D1").Value And Range("A" & i).Value = Range("A" & i).Offset(1, 0).Value

Unless Range("D1") and Range("A" & i).Offset(1,0).Value are the same, this statement will always return a false.

Also, it is a good idea to use ()

For example

If (somthing = somthing) And (somthing else = something else) Then
.
.
.
.

 

[John Davis]

Hi

Part of your If statement says

Range("A" & i).Value = Range("D1").Value And Range("A" & i).Value = Range("A" & i).Offset(1, 0).Value

Unless Range("D1") and Range("A" & i).Offset(1,0).Value are the same, this statement will always return a false.

Also, it is a good idea to use ()

For example

If (somthing = somthing) And (somthing else = something else) Then
.
.
.
.

Thanks for your reply Sandeep. It helped me to find my mistake?

 

[Trebor76]

Hi there,

The syntax looks fine, though while most of it is dynamic with the row number, the value in the second AND argument is fixed to D1, perhaps it should be "D" & i

I'd also put Exit Sub immediately beneath the msgbox so the code actually does quit when a match is found.

HTH

Robert

 

[John Davis]

Hi there,

The syntax looks fine, though while most of it is dynamic with the row number, the value in the second AND argument is fixed to D1, perhaps it should be "D" & i

I'd also put Exit Sub immediately beneath the msgbox so the code actually does quit when a match is found.

HTH

Robert

Thanks for you input Robert. The value in the second And argument should be fixed, but it should have been D2 ("There's a header row"). I'm going blind, these days. Exit Sub sounds like a great idea.