need help with if or and mid statement

**erickmcburger** · 09-19-2014, 05:40 AM

i have some strings in coloumn B2...it is all constant or all cells till the 17th character of the string after which there are codes like FI,WM,MM,CO..etc
i want to check with these codes and give a common code eg
if it is FI or CO then show FICO if it is MM or PUR or show MM etc i have used this code

  Dim MODULE As String, i As Integer
    
    i = 2
    For i = 2 To 500
   
If Mid(B2, 18, 2) = "FI" Or Mid(B2, 18, 2) = "CO" Or Mid(B2, 18, 2) = "IM" Then
        MODULE = "FICO"
 ElseIf Mid(B2, 18, 2) = "MD" Or Mid(B2, 18, 3) = "MM:" Or Mid(B2, 18, 3) = "PUR" Then
          MODULE = "MM"
 ElseIf Mid(B2, 18, 2) = "QM" Or Mid(B2, 18, 2) = "PP" Or Mid(B2, 18, 2) = "PM" Or Mid(B2, 18, 2) = "PL" Or Mid(B2, 18, 2) = "BM" Then
           MODULE = "PP"
 ElseIf Mid(B2, 18, 2) = "SD" Then
         MODULE = "SD"
 ElseIf Mid(B2, 18, 2) = "SR" Then
           MODULE = "SRM"
 ElseIf Mid(B2, 18, 2) = "" Then
           MODULE = ""
 ElseIf Mid(B2, 18, 3) = "MM," Or Mid(B2, 18, 2) = "WM" Then
          MODULE = "WM"
 Else
          MODULE = "S&A"
           End If
           Cells(i, 1).Value = MODULE
           
Next i

but am getting only S&A

it mayb a syntax error plz help

**huuthang_bd** · 09-19-2014, 05:55 AM

Excel can not understand "B2" in your code. Try replace all B2 in your code by Cells(i,2).Value

**erickmcburger** · 09-19-2014, 08:43 AM

thanks a lot it worked

**pareshj** · 09-19-2014, 05:58 AM

Hi,

Please check the forum rules :
http://www.excelforum.com/forum-rule...rum-rules.html

Use Code tags around the code.

Regards,
Paresh J

**Marc L** · 09-19-2014, 06:02 AM

Easier with Select Case statement instead of several If …

**JOHN H. DAVIS** · 09-19-2014, 08:51 AM

Try:

Sub erickmcburger()
Dim i As Long
For i = 2 To 500
    Select Case Left(Right(Range("B" & i), Len(Range("B" & i)) - 17), 2)
        Case Is = "FI", "CO"
            Range("A" & i).Value = "FICO"
        Case Is = "MM", "PUR"
            Range("A" & i).Value = "MM"
    End Select
Next i

End Sub

**erickmcburger** · 09-23-2014, 08:16 AM

to find a word "NOT" in the 5th cell and to post if found in the 14th cell "Not" else post the values of the 13th cell. this has to check and post for 500 cells
i am getting error in the code for search string

need help with if or and mid statement

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

need help with if or and mid statement

Re: need help with if or and mid statement

Re: need help with if or and mid statement

Re: need help with if or and mid statement

Re: need help with if or and mid statement

Re: need help with if or and mid statement

Re: need help with if or and mid statement

Thread Information

Users Browsing this Thread

Similar Threads

compile error expected line number statement end statement

VBA Compile Error : line number or label or statement or end of statement

[SOLVED] Convert If statement with cell row reference calculations to vlookup statement

VBA- Compile Error, Expected: line number or label or statement or end of statement

[SOLVED] Utilize a Select Case Statement in Target Intersect Statement

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions