VBA search for value twice - embedded if statement

[katekoz]

Hello - I'm trying to have a macro search one list for a value and, if it doesn't find it there, to then search a different list. I'm having problems with the fact that I'm using "For Row" twice, and I get the error "For control variable already in use." Any suggestions on how to do this double-search?

SearchColumn = 4

lr = ThisWorkbook.Sheets("Overall User Data").UsedRange.Rows.Count + ThisWorkbook.Sheets("New Data Add").UsedRange.Row
sr = ThisWorkbook.Sheets("Overall User Data").UsedRange.Row
lr2 = ThisWorkbook.Sheets("New Data Add").UsedRange.Rows.Count + ThisWorkbook.Sheets("New Data Add").UsedRange.Row
sr2 = ThisWorkbook.Sheets("New Data Add").UsedRange.Row

For Row = lr To sr Step -1
If 0 = InStr(1, UserValue, ThisWorkbook.Sheets("Overall User Data").Cells(Row, SearchColumn)) Then

For Row = lr2 To sr2 Step -1
If 0 = InStr(1, UserValue, ThisWorkbook.Sheets("New Data Add").Cells(Row, SearchColumn)) Then

ans = MsgBox("This user is not found in the dataset and their values will be blank throughout the reports. Would you still like to add this user to the metrics?", vbYesNo)
End If
End If
Next Row

 

[Fluff]

How about

Sub katekoz()
   Dim Fnd As Range
   
   With ThisWorkbook.Sheets("Overall User Data").Columns(4)
      Set Fnd = .Find(UserValue, , , xlPart, , , False, , False)
   End With
   If Fnd Is Nothing Then
      With ThisWorkbook.Sheets("New Data Add").Columns(4)
         Set Fnd = .Find(UserValue, , , xlPart, , , False, , False)
      End With
   End If
   If Fnd Is Nothing Then
      ans = MsgBox("This user is not found in the dataset and their values will be blank throughout the reports. Would you still like to add this user to the metrics?", vbYesNo)
      'do something
   End If
End Sub

 

[katekoz]

That works, thanks! I didn't know about that "find" functionality, so I'll definitely look into that to use in the future. Thanks so much!!

 

[Fluff]

You're welcome & thanks for the feedback