asked on

Please correct my sub, compile error

at msgbox: compile error left hand side of assignment must return variant or object.

Assume there is a list of names beginning at A1 in the activesheet of the current workbook. The names are in uppercase letters in normal format such as CARRIE F. LEE. This sub is supposed to count the number of people with the name LEE and display it in a message box.

I have a code but there is an error!
What am I doing wrong? Any help is appreciated. Thank you!

Sub lee()
Dim leeName As String
Dim count As Integer

count = 0

With ActiveSheet.Range("A1")
Do Until .Offset(0, 0).Value = ""
leeName = .Offset(0, 0).Value

If Len(leeName) >= 4 Then

If Right(leeName, 3) = "LEE" And Mid(leeName, leeName, (3 - 1)) = " " Then

count = count + 1

End If
End If
Loop
End With
MsgBox = "There are " & count & " people whose last name is Lee."

End Sub

Open in new window

Saqib Husain

MsgBox ( "There are " & count & " people whose last name is Lee.")

buutercup0122

ASKER

Thank you! Now, I have a runtime error 13 mismatch? I know something else is wrong. I can't seem to get it. Any chance you can help again?

Saqib Husain

It cannot be on the same line. Which line is the error on?

buutercup0122

ASKER

I'm sorry, I'm not sure. The line isn't highlighted. Is there a way I can find out where the error is. The error just comes up as: Run-time error '13': Type mismatch.

Peter Kwan

The second argument for mid function is incorrect. It should be a starting index which is a number.

BTW, after even updating the mid function, there are two things it is required to be modified in order to make it work as expected.

1) You are always testing .Offset(0, 0).Value = "" which will fall into infinite loop.
2) I guess you are testing if the right three character is "LEE" and before that it is a character of space. But the second condition in your if statement, a modification is required for making this work.

Saqib Husain

usually when you get the error you have the option to debug. Select that option and the culprit line will be highlighted. If you do not get the debug option then...

Put the cursor on sub lee() and then press F8. Continue pressing F8 and the lines will be highlighted one by one and then let me know which line gives the error.

Saqib Husain

try

Mid(leeName, len(leeName) - 3, 1)

instead of

Mid(leeName, leeName, (3 - 1))

buutercup0122

ASKER

@ssaqibh Yes, it highlighted the mid function and I tried your revised mid function to no avail. Any more tries?

@pkwan Yes, I am testing what you wrote in your number 2. Any thoughts on how to fix it exactly?

I'm still unsure how to make my program run.
Thank you for your time.

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

FWIW, you can simplify a bit:

Sub lee()
   Dim count             As Long
   Dim n                 As Long
   Dim lngLastRow        As Long

   count = 0

   ' get last used row in column A
   lngLastRow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).Row
   For n = 1 To lngLastRow
      With ActiveSheet.Cells(n, "A")
      
         If UCase(Right(.Value, 4)) = " LEE" Then
            count = count + 1
         End If
      End With
   Next n
   MsgBox "There are " & count & " people whose last name is Lee."

End Sub

Open in new window

Saqib Husain

This would count only if lee is all in uppercase. To accommodate all variations of case use

If ucase(Right(leeName, 3)) = "LEE" And Mid(leeName, Len(leeName) - 3, 1) = " " Then

buutercup0122

ASKER

Thank you very much!! I get so frustrated with codes sometimes. Thank you for being patient with me and working through the problem. I was clearly missing the mark here! I am impressed.
Thanks again,
TC