Setting range looking for text across several columns

[lizsunnysideup]

I am using the following for a macro:
Dim y As Long

For y = 1 To Cells(Rows.Count, “AK”).End(xlUp).Row

If Range(“AK:AM” & y).Value = “DRUG REQUIRES PRIOR AUTHORIZATION” And Range(“A” & y).Value = “” Then

Range(“A” & y).Value = “PA REQD”

End If

Next y

However, I keep getting an error message of method 'range' of object_global failed. I'm not sure how to fix, but I'm trying to flag claims with the text DRUG REQUIRES PRIOR AUTHORIZATION which appears in several different columns.

 

[DanteAmor]

You need to check each column:

Sub test_1()
  Dim i As Long
  Dim txt As String
  
  txt = "DRUG REQUIRES PRIOR AUTHORIZATION"
  
  For i = 1 To Cells(Rows.Count, "AK").End(xlUp).Row
    If (Range("AK" & i).Value = txt Or Range("AL" & i).Value = txt Or Range("AM" & i).Value = txt) _
       And Range("A" & i).Value = "" Then
      Range("A" & i).Value = "PA REQD"
    End If
  Next
End Sub

 

[lizsunnysideup]

That did it! Thank you so much!!