RESETING DROPDOWNS

[JamRivera]

Hi Akuiuni, how do i make the macro search for NO and TIP so if found it will reset to YES? Thank you for all your help.
VBA Code:
Function isValid(c As Range) As Boolean
Dim v

On Error Resume Next
v = c.Validation.Type
On Error GoTo 0

isValid = Not IsEmpty(v)
End Function

Sub JamRivera()

Dim c As Range
Dim sAddress As String

For Each x In Array("Sheet1", "Sheet2")
With Sheets(x).Range("A1:V45")
Set c = .Find(What:="No", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
sAddress = c.Address
Do
Set c = .FindNext(c)
If isValid(c) Then c = "Yes"
Loop While Not c Is Nothing And c.Address <> sAddress
End If
End With
Next

End Sub

 

[Akuini]

how do i make the macro search for NO and TIP so if found it will reset to YES?

Try:

Sub JamRivera2()

Dim c As Range, x, y
Dim sAddress As String
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each x In Array("Sheet1", "Sheet2")
    With Sheets(x).Range("A1:V45")
        For Each y In Array("NO", "TIP")
        Set c = .Find(What:=y, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
            sAddress = c.Address
            Do
               Set c = .FindNext(c)
               If isValid(c) Then c = "Yes"
            Loop While Not c Is Nothing And c.Address <> sAddress
        End If
        Next
    End With


Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

or if you are searching on all sheets then you don't have to specify the sheets, like this:

Sub JamRivera3()

Dim c As Range, y
Dim sAddress As String
Dim ws As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    With ws.Range("A1:V45")
        For Each y In Array("NO", "TIP")
        Set c = .Find(What:=y, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
            sAddress = c.Address
            Do
               Set c = .FindNext(c)
               If isValid(c) Then c = "Yes"
            Loop While Not c Is Nothing And c.Address <> sAddress
        End If
        Next
    End With
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[JamRivera]

Thank You the last code work perfectly
VBA Code:
Sub JamRivera3()

Dim c As Range, y
Dim sAddress As String
Dim ws As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
With ws.Range("A1:V45")
For Each y In Array("NO", "TIP")
Set c = .Find(What:=y, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
sAddress = c.Address
Do
Set c = .FindNext(c)
If isValid(c) Then c = "Yes"
Loop While Not c Is Nothing And c.Address <> sAddress
End If
Next
End With
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[JamRivera]

Hi Akuini again thanks for all your help just need a little bit more help
I ran the macro yesterday and it worked just fine but I tried today and i get the following error
Compile error: sub or function not defined - stops at isValid highlighted in blue below?
BTW I didn't make changes to the macro and the only issue is resetting the drop downs to a YES if drop down is a NO or TIP

 

[Akuini]

You must have the "Function isValid(c As Range) As Boolean" (in post #1), do not remove it.

 

[JamRivera]

Dang Nabbit! totally missed that need sleep - its working again thank you kindly