Compile Error while inserting into cell

[pmgibs]

I'm an trying to get concatenate string of text that will be a large formula into I2, but I receive a Compile Error: Argument not Optional. Here is my most recent code.

Dim Form, a, b, c, d, e, f, g, h, i, j, k As String
Dim TxtRng As Range
Dim wb As Workbook
Dim ws As Worksheet


a = "=IF($H2=""No"","""","
b = "IF(($B2-I$1)<100,"""","
c = "IF(((($B2-I$1)*$C2)*1000000)<260000000000,"""","
d = "IF(((I$1*$C2)*1000000)<330000000000,"""","
e = "IF(AND(($B2-I$1)>=200,($B2-I$1)<=800,((($B2-I$1)*$C2)*1000000)>=620000000000,((($B2-I$1)*$C2)*1000000)<=920000000000,I$1>=375,I$1<=420,((I$1*$C2)*1000000)>=680000000000,((I$1*$C2)*1000000)<=790000000000,$C2>=1300,$C2<=2100),1,"
f = "IF(AND(($B2-I$1)>=270,($B2-I$1)<=325,((($B2-I$1)*$C2)*1000000)>=330000000000,((($B2-I$1)*$C2)*1000000)<=500000000000,I$1>=375,I$1<=420,((I$1*$C2)*1000000)>=680000000000,((I$1*$C2)*1000000)<=790000000000,$C2>=1300,$C2<=2000),2,"
g = "IF(AND(($B2-I$1)>=100,($B2-I$1)<=400,((($B2-I$1)*$C2)*1000000)>=300000000000,((($B2-I$1)*$C2)*1000000)<=510000000000,I$1>=375,I$1<=420,((I$1*$C2)*1000000)>=680000000000,((I$1*$C2)*1000000)<=790000000000,$C2>=1300,$C2<=2100),3,"
h = "IF(AND(($B2-I$1)>=200,($B2-I$1)<=400,((($B2-I$1)*$C2)*1000000)>=520000000000,((($B2-I$1)*$C2)*1000000)<=610000000000,I$1>=375,I$1<=420,((I$1*$C2)*1000000)>=680000000000,((I$1*$C2)*1000000)<=790000000000,$C2>=1300,$C2<=2100),4,"
i = "IF(AND(($B2-I$1)>=100,($B2-I$1)<=400,((($B2-I$1)*$C2)*1000000)>=300000000000,((($B2-I$1)*$C2)*1000000)<=510000000000,I$1>=540,I$1<=650,((I$1*$C2)*1000000)>=660000000000,((I$1*$C2)*1000000)<=1000000000000,$C2>=900,$C2<=2000),5,"
j = "IF(AND(($B2-I$1)>=200,($B2-I$1)<=800,((($B2-I$1)*$C2)*1000000)>=620000000000,((($B2-I$1)*$C2)*1000000)<=920000000000,I$1>=270,I$1<=325,((I$1*$C2)*1000000)>=330000000000,((I$1*$C2)*1000000)<=500000000000,$C2>=900,$C2<=2000),6,"
k = "IF(AND(($B2-I$1)>=100,($B2-I$1)<=400,((($B2-I$1)*$C2)*1000000)>=300000000000,((($B2-I$1)*$C2)*1000000)<=510000000000,I$1>=270,I$1<=325,((I$1*$C2)*1000000)>=330000000000,((I$1*$C2)*1000000)<=500000000000,$C2>=900,$C2<=2000),7,"")))))))))))"


Form = a & b & c & d & e & f & g & h & i & j & k


Set wb = ActiveWorkbook
Set ws = wb.Sheets("Uneven Split Job Aid")


Set TxtRng = ws.Range.Value("I2")
TxtRng.Value = Form

The error occurs at this line of code

Set TxtRng = ws.Range.Value("I2")

I have tried multiple ways of selecting the cell and assigning the "Form" value to it, but I received Object not defined errors.

Thanks for any help

 

[frabulator]

Well, (without testing), a Value can not be assigned to a cell range

try ws.Range("I2").value

 

[JoeMo]

Remove the .Value:

Set TxtRng = ws.Range("I2")

Even easier, skip using TxtRng altogether and use:

ws.Range("I2").Formula = Form

 

[pmgibs]

Thanks for the quick reply, I've been looking at this most of the day and missed that silly error

It made it past the bad line of code and now is encountering a Run-time error '1004': Application-defined or object-defined error on the next bit of code

TxtRng.Value = Form

 

[Norie]

That probably means you have an error in the formula that's stored in Form and/or the formula is too long.

What is the formula supposed to do?

PS Normally when putting a formula in a cell you use .Formula (or .FormulaR1C1), not .Value.

 

[pmgibs]

Thanks for you help. I reviewed my code and I missed one set of empty quotes with only two quotes, once I updated it to four it all worked great. I need to stop looking at this for a bit and take a break, bunch of silly errors.