CODEWITHSUNDEEP
vbaloopsexcel
Rhodo
Rhodo

Reputation: 1334

VBA Loop for specific letters in alphabet

I would like to loop through the contents of a column in excel using VBA. In each cell there is a word. For words that start with letters 'P-Z' I want to return a '1', otherwise '0'.

e.g.:

DOG
CAT
PONY
ZEBRA

returns:

0
0
1
1

Thank you

Upvotes: 0

Views: 1696

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Public Function Starter(s As String) As Integer
    Starter = 0
    If Left(s, 1) Like "[P-Z]" Then Starter = 1
End Function

For example:

enter image description here

EDIT

Now that we already have the function we can insert a sub to use that function:

Sub MAIN()
    Dim i As Long, N As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To N
        Cells(i, 2).Value = Starter(Cells(i, 1).Value)
    Next i
End Sub

The VBE now looks like:

enter image description here

Upvotes: 1

Paresh J
Paresh J

Reputation: 2419

Another solution is using EXCEL FORMULA.

Using Simple IF ELSE condition in EXCEL, it can be achieved.

Check the below formula:

=IF(AND(CODE(LEFT(UPPER(TRIM(A1)))) >=80, CODE(LEFT(UPPER(TRIM(A1))))<=90),1,0)

Upvotes: 2

Related Questions