Confused with If s and And s
 

I need some assistance with this formula below to make it easier to read and
functional. Everytime I start working on it something, in my head, tells me
it is not right. I started recording a macro and then added all the "Ands"
the formula because the recorder did not like them. What would be a way to
write this?

Range("B5").Select
ActiveCell.FormulaR1C1 = "=IF(Left(RC[4],2)= ""50"", RC[1],_
If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)= ""H""),""Harcourt K-6"",_
If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)< ""H""),""Houghton
K-6"",_
If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)= ""H""),""HRW"",_
If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)< ""H""),""McDougal"",
"" "")))))"

Confused with If s and And s
 

I changed the nesting of the IF's to eliminate the AND's. The 4th and 5th
rows in the formula below need to be placed on the same line and the same
with 6th and 7th lines.

Range("B5").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[4],2)= ""50"", RC[1]," & _
"IF(LEFT(RC[4],2)= ""30"",IF(MID(RC[4],7,1)= ""H"",""Harcourt
K-6"",""Houghton K-6"")," & _
"IF(LEFT(RC[4],2)= ""40"",IF(MID(RC[4],7,1)=""H"",""HRW"",
""McDougal""),"" "")))"

"Pablo" wrote:

I need some assistance with this formula below to make it easier to read and
functional. Everytime I start working on it something, in my head, tells me
it is not right. I started recording a macro and then added all the "Ands"
the formula because the recorder did not like them. What would be a way to
write this?

Range("B5").Select
ActiveCell.FormulaR1C1 = "=IF(Left(RC[4],2)= ""50"", RC[1],_
If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)= ""H""),""Harcourt K-6"",_
If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)< ""H""),""Houghton
K-6"",_
If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)= ""H""),""HRW"",_
If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)< ""H""),""McDougal"",
"" "")))))"

Confused with If s and And s
 

Perfect. Thanks.

"joel" wrote:

I changed the nesting of the IF's to eliminate the AND's. The 4th and 5th
rows in the formula below need to be placed on the same line and the same
with 6th and 7th lines.

Range("B5").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[4],2)= ""50"", RC[1]," & _
"IF(LEFT(RC[4],2)= ""30"",IF(MID(RC[4],7,1)= ""H"",""Harcourt
K-6"",""Houghton K-6"")," & _
"IF(LEFT(RC[4],2)= ""40"",IF(MID(RC[4],7,1)=""H"",""HRW"",
""McDougal""),"" "")))"

"Pablo" wrote:

I need some assistance with this formula below to make it easier to read and
functional. Everytime I start working on it something, in my head, tells me
it is not right. I started recording a macro and then added all the "Ands"
the formula because the recorder did not like them. What would be a way to
write this?

Range("B5").Select
ActiveCell.FormulaR1C1 = "=IF(Left(RC[4],2)= ""50"", RC[1],_
If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)= ""H""),""Harcourt K-6"",_
If(And(Left(RC[4],2)= ""30"", Mid(RC[4],7,1)< ""H""),""Houghton
K-6"",_
If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)= ""H""),""HRW"",_
If(And(Left(RC[4],2)= ""40"", Mid(RC[4],7,1)< ""H""),""McDougal"",
"" "")))))"