Multiple TRUE conditions

[cr2289]

I'm trying to create a macro and formula that will work with multiple true conditions. In column I there are numbers, about 40. In column J, I'd like to convert those numbers to text. Some numbers will be AA (true) and others BB (false). I tried over and over to get the formula to work and kept running into missing closing parenthesis errors. The formula below was finally accepted, but alas, it won't copy down to the rest of the cells in the column. What am I doing wrong? Here's the formula I used cell J7:

=IF(OR(I7=9, OR(I7=13,OR(I7=25,OR(I7=26,OR(I7=38,OR(I7=39,OR(I7=40,OR(I7=50,OR(I7=51,OR(I7=56)))))))))),"AA","BB").

The formula work in cell J7 but it won't copy down to the cells below. The value in J8 is also 38. I only included the true numbers. The numbers not included in the true conditions are false (BB). I don't think I need to list them individually, or do I?

Also, how do I create macro language that will include this formula in cell J7 down to the last active row?

Thanks so much for your help.

 

[Joe4]

=IF(OR(I7=9, OR(I7=13,OR(I7=25,OR(I7=26,OR(I7=38,OR(I7=39,OR(I7=40,OR(I7=50,OR(I7=51,OR(I7=56)))))))))),"AA","BB").

I think you may be able to simplify this formula greatly. If I am understanding it correctly, there is no need to nest the IF statements (you just aren't using OR correctly).
Try this:

=IF(OR(I7=9,I7=13,I7=25,I7=26,I7=38,I7=39,I7=40,I7=50,I7=51,I7=56),"AA","BB")

 

[My Aswer Is This]

Try this:
I included the values you mentioned modify by adding more if needed.
Looks down Column I till last value.
Starts in Range("I7")

Sub Select_Case_Me()
'Modified  7/26/2022  8:31:51 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "I").End(xlUp).Row

For i = 7 To Lastrow
    Select Case Cells(i, "I").Value
        Case 9, 13, 25, 26, 38, 39, 40, 50, 51, 56
            Cells(i, "J").Value = "AA"
            Case Else: Cells(i, "J").Value = "BB"
            End Select

Next
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The formula work in cell J7 but it won't copy down to the cells below.

Your formula copies down fine for me, however Joe has shortened it somewhat and you can shorten it even further as follows.

=IF(OR(I7={9,13,25,26,38,39,40,50,51,56}),"AA","BB")

how do I create macro language that will include this formula in cell J7 down to the last active row?

I would do it like this.

Sub Insert_Formula()
  Range("J7:J" & Range("I" & Rows.Count).End(xlUp).Row).Formula = "=IF(OR(I7={9,13,25,26,38,39,40,50,51,56}),""AA"",""BB"")"
End Sub

 

[My Aswer Is This]

I would think I would like the values entered into the cells and not the formulas.
But:

 

[cr2289]

Try this:
I included the values you mentioned modify by adding more if needed.
Looks down Column I till last value.
Starts in Range("I7")

Sub Select_Case_Me()
'Modified  7/26/2022  8:31:51 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "I").End(xlUp).Row

For i = 7 To Lastrow
    Select Case Cells(i, "I").Value
        Case 9, 13, 25, 26, 38, 39, 40, 50, 51, 56
            Cells(i, "J").Value = "AA"
            Case Else: Cells(i, "J").Value = "BB"
            End Select

Next
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

Thank you!!!

 

[My Aswer Is This]

Thank you!!!

Glad I was able to help you

 

[Peter_SSs]

Thank you!!!

It sounds then like you wanted the values placed in column J, not formulas as you asked for.

how do I create macro language that will include this formula in cell J7 down to the last active row?

You are quite welcome to continue with the row-by-row looping to insert the values but if you want, you can process the whole column together. One way would be

Sub Insert_Values()
  Const myNums As String = "9,13,25,26,38,39,40,50,51,56"
  
  With Range("J7:J" & Range("I" & Rows.Count).End(xlUp).Row)
    .Formula = "=if(or(I7={" & myNums & "}),""AA"",""BB"")"
    .Value = .Value
  End With
End Sub