Problem with IF and nested IF

s1ip

New Member
Joined
Jul 14, 2006
Messages
15
WPM$2C5B.csv
ABCDEFGHIJK
1ACCTCSHTAAAB
2ACCTFIXBBBB
3ACCTCSHTCCCB
4ACCTESELDDDS
5ACCTCSHTEEEB
6ACCTCSHTFFFB
7ACCTESELGGGS
8ACCTCSHTHHHB
9ACCTCSHTIIIB
10ACCTCSHTJJJB
11ACCTFIXKKKS
12ACCTFIXLLLB
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



Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
I would suggest something a little less complex.

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Surely you should be using Range here?
Code:
If ("K" & CountL) = "B" Then

Perhaps this?

Code:
If Range("K" & CountL) = "B" Then
In fact could you not use this?
Code:
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

New Member
Joined
Jul 14, 2006
Messages
15
@ 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
 

Forum statistics

Threads
1,137,060
Messages
5,679,376
Members
419,824
Latest member
Mercy kiara

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
Top