How to determine a person's gender in VBA Excel
An array of full names is set. You need to determine the gender of each person by the last vowel letter. Here is the code, it works, but only output the male gender
Private Sub CommandButton1_Click()
Dim s, i, a As Integer
Dim h, g As String
gls = ("а""я""о""е""ё""у""ю""ы""и""э")
For a = 1 To 10
h = Cells(a, 1)
i = (InStr(h, " "))
g = i - 1
If gls = g Then
Cells(a, 3) = "жен"
Else
Cells(a, 3) = "муж"
End If
Next a
End Sub
How to fix it?
2 answers
g - string variable, here you wanted to write a letter, but write a number:
g = i - 1
And it was necessary to take the symbol located at the position i - 1
Even if in g we somehow got a letter, then further comparison does not give anything:
If gls = g Then
Rather, it will always return False, since a character cannot be equal to a text of several characters.
Passing errors or "writing curve".
In VBA, the type must be specified for each variable, otherwise, the type is Variant
Variables should be given names that are easier to navigate in the code. Single-letter-usually for counters.
"a" "I" " o "" e " "e" "y" " y "" y ""and"e" - in the text, double quotes mean the presence of quotation marks, i.e. you write the string " a " I " o " e " e " y " y "y"and" e "
Cells(i, 1) - by default, the cell value is assumed, but such an entry also means a range. To avoid possible errors, it is more correct to explicitly specify what you want receive.
Private Sub CommandButton1_Click()
Dim sLetter As String, sName As String
Dim i As Integer
Const cSymbols As String = "аяоеёуюыиэ"
For i = 1 To 10
sName = Cells(i, 1).Value
sLetter = Mid$(sName, InStr(sName, " ") - 1, 1)
If InStr(cSymbols, sLetter) Then
Cells(i, 3).Value = "жен"
Else
Cells(i, 3).Value = "муж"
End If
Next i
End Sub
The entire set of vowels is not needed for this task. It is enough to know that male patronymics end with "h" (not always, but in the vast majority of cases)
Sub Button1_Click()
Dim oCell As Range
Dim sFullName As String
Set oCell = [A2]
Do While True
sFullName = Trim(oCell.Text)
If sFullName = vbNullString Then Exit Sub
If Right(sFullName, 1) = "." Then ' На случай если вместо полного ФИО только инициалы
oCell.Offset(0, 2).Value = "???"
ElseIf LCase(Right(sFullName, 1)) = "ч" Then
oCell.Offset(0, 2).Value = "муж"
Else
oCell.Offset(0, 2).Value = "жен"
End If
Set oCell = oCell.Offset(1, 0)
Loop
End Sub