VB to replace formula, please?

[tjak]

I have to split a downloaded report to 6 people based on a matrix. I have a 6-level, nested and/if formula, but it doesn't work in a module. Even if it did, I'm sure there are better ways to do this, but I don't know how. I get a lot of "expected end of statement" errors.
Any suggestions will be welcomed, but I ask that you comment them heavily as I'm trying to learn.

Thanx, again, to all.

Range("D2:d100").FormulaR1C1 = "=IF(AND(LEFT(rc[1],3)=""MED"", (rc[3])>2500),""1"",IF(AND(LEFT(RC[1],3) _
<>""MED"",(rc[3])>2500),""2"",IF(AND(LEFT(RC[1],3)=""MED"",(rc[-3])<""M""),""3"" _
,IF(AND(LEFT(RC[1],3)=""MED"",(rc[-3])>""L""),""4"",IF(AND(LEFT(RC[1],3)<>""MED"",(rc[-3])<""L"", _
""5"",""6"")))))"

 

[acw]

Hi

Worked through the formula (but not the logic) and found a mismatch with the brackets. Try

Range("D2:d100").FormulaR1C1 = "=IF(AND(LEFT(RC[1],3)=""MED"",RC[3]>2500),""1"",IF(AND(LEFT(RC[1],3)<>""MED"", RC[3]>2500),""2"",IF(AND(LEFT(RC[1],3)=""MED"", RC[-3]<""M""),""3"",IF(AND(LEFT(RC[1],3)=""MED"", RC[-3]>""L""),""4"",IF(AND(LEFT(RC[1],3)<>""MED"", RC[-3]<""L""),""5"",""6"")))))"

Tony