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

clueless1

New Member
Joined
Apr 16, 2013
Messages
17
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:

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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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:

Code:
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
 
Upvote 0
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!!! :)
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top