VBA How do I loop an instr function using a string variable?

Question

I just started learning VBA and I am trying to get an if and loop function to work together. I basically want to search for @ in column A and if there is @ then = ok, if not= "not valid". I get it to work for one row but to loop it for an entire column. Please kindly advise. ps. please be indulgent with my ugly first timer code.

Thank you in advance, Christine

Sub help()

    Dim email As String

    email = InStr(email, "@")

    Do While email = InStr(email, "@")
        Cells(email, 1).Value = email
        If email = 0 Then
            Cells(email, 1).Offset(, 1).Value = "Not valid"
        Else
            Cells(email, 1).Offset(, 1).Value = "ok"
        End If 
    Loop

End Sub

Answer 1

You can set a range, then loop through that range:

Sub help()
Dim email As String
Dim rng As Range, cel As Range 'New
Dim lastRow as Long 'New

lastRow = Range("A"& rows.count).End(xlUp).Row
Set rng = Range("A2:A" & lastRow) 'Adjust as necessary

For Each cel In rng
    If InStr(1, cel.Value, "@") > 0 Then
        cel.Offset(0, 1).Value = "Ok"
    Else
        cel.Offset(0, 1).Value = "Not Valid"
    End If
   ' OR as @JohnyL points out, you can do the above in line. 
   ' Just comment out/remove the above `If` statement and uncomment below
   ' cel.Offset(0, 1) = IIf(InStr(1, cel.Value, "@") > 0, "Ok", "Not Valid")
Next cel

End Sub

Here's a super-short macro that may work, depending on how your data is laid out:

Sub t()
Dim rng As Range
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
rng.Offset(0, 1).Formula = "=IF(ISERR(SEARCH(""@"",A2)),""Not Valid"",""Yes"")"
rng.Offset(0, 1).Value = rng.Offset(0, 1).Value
End Sub

Alternatively, you can create a User Defined Function. Enter this code in a Workbook Module:

Function validate_email(cel As Range) As String
If InStr(1, cel.Value, "@") > 0 Then
    validate_email = "Valid"
Else
    validate_email = "Not Valid"
End If
End Function

And in cell, say B20, just do =validate_email(A20) and I'll check for you. This has the advantage of being able to be run on any cell, and not have to edit your macro's range(s).

Also, just to note, you don't need VBA for this, you can simply use the formula =IF(ISERR(SEARCH("@",A2)),"Not Valid","Yes") in Column B and drag down.

And finally, as I mentioned in the comments, this doesn't really check email validity. However, for your question, it works. See this page, or this one, or simply search VBA email validation for more ways to check if the email address is proper.