Confused with If s and And s

P

Pablo

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"",
"" "")))))"
 
J

joel

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""),"" "")))"
 
P

Pablo

Perfect. Thanks.

joel said:
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 said:
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"",
"" "")))))"
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 2
macro to copy rows that contain a cell with a certain number 3
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
Creating Pivot Table with Macro using Multiple Sheets 0
Help with my VBA and formula 8
If statement giving syntax error when run macro 3
Vlookup in vba - how to use absolute rows not relative 1
Auto-calculate after a cell is filled in - Macro is looping!!! 2

Top