# Thread: Problem with Instr coding

1. ## Problem with Instr coding

I am trying to do an Instr function but want to use two different criteria in my If-Then statement. Below is a shortened version of my current code. For example on the second line, if both "S/P" and "#04" are found in the string, I want to code the item "04 V". The fourth line is the same way. Is Instr the wrong thing to use here? Several of my other lines are just searching for one substring and I also have other lines going off of the left/right/mid of the string. Any help would be great!

Code:
```Sub AutoCoding()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set Desc = Range("E6")
Do Until IsEmpty(Desc.Offset(0, -4))
With Desc
If InStr(1, Desc, "02999999") Then
Desc.Offset(0, 2) = "02 I"
ElseIf InStr(1, Desc, "S/P") And InStr(1, Desc, "#04") Then
Desc.Offset(0, 2) = "04 V"
ElseIf InStr(1, UCase(Desc), UCase("Admin Charge")) Then
Desc.Offset(0, 2) = "J"
ElseIf InStr(1, Desc, "STOP PAY") And InStr(1, Desc, "#1") Then
Desc.Offset(0, 2) = "01 V"
ElseIf Left(Desc, 3) = "ICB" Then
Desc.Offset(0, 2) = "J"
End If
Set Desc = Desc.Offset(1, 0)
End With
ActiveWindow.SmallScroll Down:=1
Loop
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub```

2. KMF,

You don't give us any data to work with so it's hard to tell what the code is doing. Specifically, is the string you are searching in E6 or A6 (e.g.) Desc.Offset(0,-4).

A worksheet with some test data would be most useful. HTH

3. KMF,

Here's a modification of your code moving from ElseIf constructs to a Case construct which is easier to read IMHO!
You'll also notice that I'm testing the Instr functions to be greater than 0, Instr returns the starting position of the found string which is always greater than 0 (it returns 0 if the string is not found). I've also killed the scroll command as this accomplishes nothing with Application.ScreenUpdating set to False.

Another thing I personally don't like the use of the default object property, specifically DESC for Desc.Value it tends to hide what you are really doing. Thus the change, you already had the With Desc clause but were not taking advantage of it.
Code:
```Option Explicit

Sub AutoCodingCase()

Dim Desc As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set Desc = Range("E6")

Do Until IsEmpty(Desc.Offset(0, -4).value)

With Desc

Select Case True
Case InStr(1, .Value, "02999999") > 0
.Offset(0, 2) = "02 I"
Case (InStr(1, UCase(.Value), "S/P") > 0) And _
(InStr(1, .Value, "#04") > 0)
.Offset(0, 2) = "04 V"
Case (InStr(1, UCase(.Value), UCase("Admin Charge")) > 0)
.Offset(0, 2) = "J"
Case (InStr(1, UCase(.Value), "STOP PAY") > 0) And _
(InStr(1, .Value, "#1") > 0)
.Offset(0, 2) = "01 V"
Case Left(UCase(.Value), 3) = "ICB"
.Offset(0, 2) = "J"
End Select

End With  'Desc

Set Desc = Desc.Offset(1, 0)

Loop

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub   'AutoCodeingCase()```
kmf.JPG
BTW: I've tested this with data I made up but a test file is always useful. HTH

4. This is perfect! Thank you for all of your help! I'll be sure to post test data next time.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•