Help with VBA code breaking into second row

[shahid5788]

My code consist of a large formula. And I would like to keep on adding to the formula in the VBA. But it looks like I have reached my maximum in that formula that it spills into the second line on my VBA. When that happens my formula breaks. I was wondering if someone had a solution to this problem. Hopefully I made clear to what I am looking for. Can you please HELP?



This is my Code. I would like to keep on adding such as CAP 55, 556, 57, etc.....


ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[-6]=""CAP 1"",RC[-6]=""CAP 2"",RC[-6]=""CAP 3"",RC[-6]=""CAP 4"",RC[-6]=""CAP 5"",RC[-6]=""CAP 6"",RC[-6]=""CAP 7"",RC[-6]=""CAP 8"",RC[-6]=""CAP 9"",RC[-6]=""CAP 10"",RC[-6]=""CAP 11"",RC[-6]=""CAP 12"",RC[-6]=""CAP 13"",RC[-6]=""CAP 14"",RC[-6]=""CAP 15"",RC[-6]=""CAP 16"",RC[-6]=""CAP 17"",RC[-6]=""CAP 18"",RC[-6]=""CAP 19"",RC[-6]=""CAP 20"",RC[-6]=""CAP 21"",RC[-6]=""CAP 22"",RC[-6]=""CAP 23"",RC[-6]=""CAP 24"",RC[-6]=""CAP 25"",RC[-6]=""CAP 26"",RC[-6]=""CAP 27"",RC[-6]=""CAP 28"",RC[-6]=""CAP 29"",RC[-6]=""CAP 30"",RC[-6]=""CAP 31"",RC[-6]=""CAP 32"",RC[-6]=""CAP 33"",RC[-6]=""CAP 34"",RC[-6]=""CAP 35"",RC[-6]=""CAP 36"",RC[-6]=""CAP 37"",RC[-6]=""CAP 38"",RC[-6]=""CAP 39"",RC[-6]=""CAP 40"",RC[-6]=""CAP 41"",RC[-6]=""CAP 42"",RC[-6]=""CAP 43"",RC[-6]=""CAP 44"",RC[-6]=""CAP 45"",RC[-6]=""CAP 46"",RC[-6]=""CAP 47"",RC[-6]=""CAP 48"",RC[-6]=""CAP 49"",RC[-6]=""CAP 50"",RC[-6]=""CAP 51"",RC[-6]=""CAP 52"",RC[-6]=""CAP 53"",RC[-6]=""CAP 54"",,RC[-6]=""CAP 55""),RC[-1]*RC[-4],""0"")"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I5400")
Range("I2:I5400").Select

 

[Roderick_E]

eventually, even the formula will break.

 

[jproffer]

How about using:

IF(AND(LEFT(RC[-6],3)=""CAP"",RIGHT(RC[-6],2)>0,RIGHT(RC[-6],2)<56)),RC[-1]*RC[-4],0)

I did this on the fly, but I think I got all the "( )" in there. Check it and see. Adjust "56" to whatever you want.

You may have to trim the RIGHT function because I'm picking up 2 digits, and that may or may not throw off the greater than/less than evaluation of a single digit.

Just an idea

 

[Fluff]

If you just want to check if the cell starts with "CAP", then try

ActiveCell.FormulaR1C1 = "=if(left(RC[-6],3)=""CAP"",RC[-1]*RC[-4],""0"")"

 

[shahid5788]

I am not sure if I explained it correctly.

So my current formula "=IF(OR(RC[-6]=""CAP 1"",RC[-6]=""CAP 2"",RC[-6]=""CAP 3"",RC[-6]=""CAP 4"",RC[-6]=""CAP 5"",RC[-6]=""CAP 6"",RC[-6]=""CAP 7"",RC[-6]=""CAP 8"",RC[-6]=""CAP 9"",RC[-6]=""CAP 10"",RC[-6]=""CAP 11"",RC[-6]=""CAP 12"",RC[-6]=""CAP 13"",RC[-6]=""CAP 14"",RC[-6]=""CAP 15"",RC[-6]=""CAP 16"",RC[-6]=""CAP 17"",RC[-6]=""CAP 18"",RC[-6]=""CAP 19"",RC[-6]=""CAP 20"",RC[-6]=""CAP 21"",RC[-6]=""CAP 22"",RC[-6]=""CAP 23"",RC[-6]=""CAP 24"",RC[-6]=""CAP 25"",RC[-6]=""CAP 26"",RC[-6]=""CAP 27"",RC[-6]=""CAP 28"",RC[-6]=""CAP 29"",RC[-6]=""CAP 30"",RC[-6]=""CAP 31"",RC[-6]=""CAP 32"",RC[-6]=""CAP 33"",RC[-6]=""CAP 34"",RC[-6]=""CAP 35"",RC[-6]=""CAP 36"",RC[-6]=""CAP 37"",RC[-6]=""CAP 38"",RC[-6]=""CAP 39"",RC[-6]=""CAP 40"",RC[-6]=""CAP 41"",RC[-6]=""CAP 42"",RC[-6]=""CAP 43"",RC[-6]=""CAP 44"",RC[-6]=""CAP 45"",RC[-6]=""CAP 46"",RC[-6]=""CAP 47"",RC[-6]=""CAP 48"",RC[-6]=""CAP 49"",RC[-6]=""CAP 50"",RC[-6]=""CAP 51"",RC[-6]=""CAP 52"",RC[-6]=""CAP 53"",RC[-6]=""CAP 54"",,RC[-6]=""CAP 55""),RC[-1]*RC[-4],""0"")"



If I try to add RC[-6]=""CAP 56"", RC[-6]=""CAP 57"", RC[-6]=""CAP 58"", RC[-6]=""CAP 59"", etc... It spill into the second row. That is where my formula breaks of the VBA code. Is there way to continue that same formula into the second row in my VBA.

 

[Fluff]

Why do you want to do that?
If you are just checking if the cell starts with CAP try the formula in post#4, or if you want to limit it to (for instance) CAP 1 to CAP 59 try the formula in post#3