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