For Each cell In rng - not recognising cell value - ?

[clueless1]

Hi there,

I cannot seem to get the Excel vba to read the text/string within a cell, to give a specified result, it just ends up going to the 'Case Else' statement?

Code:

Sub StandardLifeInsuranceInvestmentBondNewStyleXX()
Application.ScreenUpdating = False
Dim rng As Range
Dim cell As Range
Dim PolNumber As String
Dim StdLife As String
Dim StdLfInsInvBnd As String
 
StdLife = "STANDARD LIFE"
StdLfInsInvBnd = "INSURANCE / INVESTMENT BOND"

Set rng = Range("e2:e22")
For Each cell In rng
                PolNumber = UCase(cell.Value)
            
                Select Case UCase(cell)
                
                Case Mid(PolNumber, 2, 1) <> "U" And Len(PolNumber) < 10 And Right(PolNumber, 1) = "0" And _
                UCase(cell.Offset(0, -1).Value) = StdLife And _
                UCase(cell.Offset(0, -2).Value) = StdLfInsInvBnd
                cell.Offset(0, -3).Value = "Standard Life Insurance / Investment Bond has less than 10 letters AND is missing a 'U' as the second letter - Please Amend"
                        
            
                Case Mid(PolNumber, 2, 1) <> "U" And Len(PolNumber) < 10 And Right(PolNumber, 1) <> "0" And _
                UCase(cell.Offset(0, -1).Value) = StdLife And _
                UCase(cell.Offset(0, -2).Value) = StdLfInsInvBnd
                cell.Offset(0, -3).Value = "Standard Life Insurance / Investment Bond has less than 10 letters AND is missing a 'U' as the second letter AND does not end with a zero/'0'- Please Amend"
                
                Case Else
                cell.Offset(0, -3).Value = "Unexpected Error"
                
                End Select
                
Next cell
Application.ScreenUpdating = True
End Sub

Link to my file:

A file has been sent to you via the YouSendIt File Delivery Service.
Download the file - Standard-Life-FIVE-snippet.xlsm
Your file will expire after 14 days.


Please help, I cant stop thinking about it and have tried everything I can think of - Thank you!

 

[Scott Huish]

You are not using Select Case correctly, and is not really what you want here. Select Case is generally used to execute different code based on multiple single values. I think this is what you want:

For Each cell In rng

                PolNumber = UCase(cell.Value)
            
                If Mid(PolNumber, 2, 1) <> "U" And Len(PolNumber) < 10 And _
                    UCase(cell.Offset(0, -1).Value) = StdLife And _
                    UCase(cell.Offset(0, -2).Value) = StdLfInsInvBnd Then
                    
                    If Right(PolNumber, 1) = "0" Then
                        cell.Offset(0, -3).Value = "Standard Life Insurance / Investment Bond has less than 10 letters AND is missing a 'U' as the second letter - Please Amend"
                    Else
                        cell.Offset(0, -3).Value = "Standard Life Insurance / Investment Bond has less than 10 letters AND is missing a 'U' as the second letter AND does not end with a zero/'0'- Please Amend"
                    End If
                    
                Else
                    cell.Offset(0, -3).Value = "Unexpected Error"
                End If
                
Next cell

 

[clueless1]

Ah, ok - (not using the Select Case statement for correct pupose) thank you. I managed to do it with IF statements, but thought I was cheating doing it this way. Many thanks!!!