• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

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

0
buutercup0122
Asked:
buutercup0122
1 Solution
 
Saqib Husain, SyedEngineerCommented:
MsgBox ( "There are " & count & " people whose last name is Lee.")
0
 
buutercup0122Author Commented:
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?
0
 
Saqib Husain, SyedEngineerCommented:
It cannot be on the same line. Which line is the error on?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
buutercup0122Author Commented:
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.
0
 
Peter KwanCommented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
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.
0
 
Saqib Husain, SyedEngineerCommented:
try

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

instead of

Mid(leeName, leeName, (3 - 1))
0
 
buutercup0122Author Commented:
@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.
0
 
Saqib Husain, SyedEngineerCommented:
Try this code

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

count = 0
rn = 0
With ActiveSheet.Range("A1")
    Do Until .Offset(rn, 0).Value = ""
        leeName = .Offset(rn, 0).Value
       
        If Len(leeName) >= 4 Then
           
            If Right(leeName, 3) = "LEE" And Mid(leeName, Len(leeName) - 3, 1) = " " Then
           
                count = count + 1
           
            End If
        End If
        rn = rn + 1
    Loop
End With
MsgBox ("There are " & count & " people whose last name is Lee.")

End Sub
0
 
Rory ArchibaldCommented:
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

0
 
Saqib Husain, SyedEngineerCommented:
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
0
 
buutercup0122Author Commented:
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now