CODEWITHSUNDEEP
vbaexcel
Calin Lencar
Calin Lencar

Reputation: 159

Check to see if a cell consists only of vowels or consonants Vba

I am trying to write a macro to see if the cells in a column are consisting only of consonants or only of vowels, and if they do color the entire row with yellow. I have these two functions to check the count of consonants:

Function ConsonantCount(cons As String) As Integer
Dim Str
Dim KCount As Integer
Dim i As Integer
Dim Chr As String

Str = ActiveSheet.Range("A1").Value
KCount = 0
For i = 1 To Len(Str)
    Chr = UCase(Mid(Str, i, 1))
    If Not Chr Like "[AEIOU]" Then
      KCount = KCount + 1
End If
Next i
cons = KCount
End Function

Respective vowels:

Function VowelCount(vowl As String) As Integer
Dim Str
Dim KCount As Integer
Dim i As Integer
Dim Chr As String

Str = ActiveSheet.Range("A1").Value
KCount = 0
For i = 1 To Len(Str)
  Chr = UCase(Mid(Str, i, 1))
  If Chr Like "[AEIOU]" Then
    KCount = KCount + 1
End If
Next i
vowl = KCount
End Function

Then I'm using the functions to see if there's 0 consonants/vowels for two different columns M and N :

Dim iix As Long, FFX As Long
With Sheets("JP")
FFX = .Range("M" & .Rows.count).End(xlUp).Row
For iix = 1 To FFX
    If ConsonantCount(.Range("M" & iix)) = 0 Then
        .Rows(iix).Interior.Color = vbYellow
    End If
    If ConsonantCount(.Range("N" & iix)) = 0 Then
        .Rows(iix).Interior.Color = vbYellow
    End If
    If VowelCount(.Range("M" & iix)) = 0 Then
        .Rows(iix).Interior.Color = vbYellow
    End If
    If VowelCount(.Range("N" & iix)) = 0 Then
        .Rows(iix).Interior.Color = vbYellow
    End If
Next iix
End With

I really need some guidance, fairly new to VBA, Thank You in advance!

Upvotes: 0

Views: 1570

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

There's no need to check letter by letter. To see if a cell contains all consonants or vowels, you can do something like (function returns TRUE or FALSE)

Option Explicit
Option Compare Text 'case insensitive compares

Function AllConsonants(R As Range) As Boolean
    Dim sPat As String

sPat = WorksheetFunction.Rept("[bcdfghjklmnpqrstvwxyz]", Len(R.Text))
AllConsonants = R.Text Like sPat And Len(R.Text) > 0

End Function

Function AllVowels(R As Range) As Boolean
    Dim sPat As String

sPat = WorksheetFunction.Rept("[aeiou]", Len(R.Text))
AllVowels = R.Text Like sPat And Len(R.Text) > 0

End Function

Upvotes: 2

Brian M Stafford
Brian M Stafford

Reputation: 8868

To see if a cell contains only vowels or only consonants, you would check the length of the cell text against the vowel or consonant count. For example, to see if a cell is only consonants you would do this:

Len(.Range("M" & iix)) = ConsonantCount(.Range("M" & iix))

You would also need to check for empty cells so they don't get highlighted. Keeping these ideas in mind, I reworked your code a little so it works how I think you want it to work.

Function ConsonantCount(cons As String) As Integer
   'Dim Str
   Dim KCount As Integer
   Dim i As Integer
   Dim Chr As String

   'Str = ActiveSheet.Range("A1").Value
   KCount = 0

   For i = 1 To Len(cons)
       Chr = UCase(Mid(cons, i, 1))
       If Not Chr Like "[AEIOU]" Then
         KCount = KCount + 1
       End If
   Next i

   ConsonantCount = KCount
End Function

Function VowelCount(vowl As String) As Integer
   'Dim Str
   Dim KCount As Integer
   Dim i As Integer
   Dim Chr As String

   'Str = ActiveSheet.Range("A1").Value
   KCount = 0

   For i = 1 To Len(vowl)
     Chr = UCase(Mid(vowl, i, 1))
     If Chr Like "[AEIOU]" Then
       KCount = KCount + 1
     End If
   Next i

   VowelCount = KCount
End Function

Sub Test()
   Dim iix As Long
   Dim FFX As Long

   With Sheets("JP")
      FFX = .Range("M" & .Rows.Count).End(xlUp).Row

      For iix = 1 To FFX
          If Len(.Range("M" & iix)) > 0 Then
            If Len(.Range("M" & iix)) = ConsonantCount(.Range("M" & iix)) Or Len(.Range("M" & iix)) = VowelCount(.Range("M" & iix)) Then
                .Rows(iix).Interior.Color = vbYellow
            End If
          End If

          If Len(.Range("N" & iix)) > 0 Then
            If Len(.Range("N" & iix)) = ConsonantCount(.Range("N" & iix)) Or Len(.Range("N" & iix)) = VowelCount(.Range("N" & iix)) Then
                .Rows(iix).Interior.Color = vbYellow
            End If
          End If
      Next iix
   End With
End Sub

Upvotes: 1

Dman
Dman

Reputation: 118

Is the issue you are seeing related to it highlighting lines it shouldn't? This is because your functions should end with

ConsonantCount = Kcount

and

VowelCount = Kcount

(Instead of 'cons = Kcount' and 'vowl = Kcount').

In VBA you use the name of the function to return a value.

Upvotes: 3

Related Questions