Problem with IF and nested IF

[s1ip]

WPM$2C5B.csv
	A	B	C	D	E	F	G	H	I	J	K
1	ACCT	CSHT	AAA								B
2	ACCT	FIX	BBB								B
3	ACCT	CSHT	CCC								B
4	ACCT	ESEL	DDD								S
5	ACCT	CSHT	EEE								B
6	ACCT	CSHT	FFF								B
7	ACCT	ESEL	GGG								S
8	ACCT	CSHT	HHH								B
9	ACCT	CSHT	III								B
10	ACCT	CSHT	JJJ								B
11	ACCT	FIX	KKK								S
12	ACCT	FIX	LLL								B
WPM$2C5B

The code Below is Supposed to Look at Column B for the word "FIX". If it is found check column K in the same row for either "B" or "S". If "B" is found than change "FIX" to "EBUY", if "S" is found than change "FIX" to an "ESEL".

For instance B2 should get changed into "EBUY", B11 should be "ESEL"


So far my code checks the first IF properly if Column B = "FIX" but does not enter the second IF, if the value you is either "B" or "S" it just goes through like it is false. Although when i set watches and debug, every cell it's reading has the correct corresponding value and based on that it SHOULD be going into the second IF statement.

Thanks in advance,
Keith

CountL = 1                                'Sets counter to 1
Ser = LengthCurTicker           
    
       
    Dim oCell As Range
    For Each oCell In Range("B1:B" & Ser)
        If (oCell) = "FIX" Then                 
            Debug.Print ActiveSheet.Range("K" & CountL).Text
            If ("K" & CountL) = "B" Then                         'problem starts here
                ActiveSheet.Range("B" & CountL).Select
                ActiveCell.FormulaR1C1 = "EBUY"
                CountL = CountL + 1
            ElseIf ("K" & CountL) = "S" Then                   'Or here depending
                Debug.Print ActiveSheet.Range("K" & CountL).Text
                ActiveSheet.Range("B" & CountL).Select
                ActiveCell.FormulaR1C1 = "SSHT"
                CountL = CountL + 1
            ElseIf ("K" & CountL) = "SS" Then
               Debug.Print ActiveSheet.Range("K" & CountL).Text
                ActiveSheet.Range("B" & CountL).Select
               ActiveCell.FormulaR1C1 = "SSHT"
                CountL = CountL + 1
            End If
        Else
            Debug.Print ActiveSheet.Range("K" & CountL).Text
            CountL = CountL + 1
        End If
    Next

 

[Cbrine]

I would suggest something a little less complex.

Dim oCell as range
for each oCell in Range("B1:B"&activesheet.rows.count).end(xlup))
  If oCell = "FIX" then
       If oCell.offset(0,9)="B" then
                ocell="EBUY"
       End if
       if ocell.offset(0,9) = "S" then
               oCell ="SSHT"
      End if
      if ocell= "SS" then
                oCell ="SSHT"
      End if
   End if
Next
End sub

HTH
Cal

 

[Norie]

Surely you should be using Range here?

If ("K" & CountL) = "B" Then

Perhaps this?

If Range("K" & CountL) = "B" Then

In fact could you not use this?

Dim oCell As Range
Ser = Range("B" & Rows.Count).End(xlUp).Row

    For Each oCell In Range("B1:B" & Ser)
        If oCell.Value = "FIX" Then
            Select Case oCell.Offset(, 9).Value
                Case "B"
                    oCell = "EBUY"
                Case "S", "SS"
                    oCell = "SSHT"
            End Select
        End If
    Next

 

[s1ip]

@ Cbrine & Norie

Both of those worked equally as well. Thank you very much to the both of you. I went with Norie's only cause it was the lowest post when i finished reading and the second one i tried. But again thank you. I'm a noob to excel and teaching myself.

Thanks again
Keith