Compile Error while inserting into cell

pmgibs

New Member
Joined
Apr 5, 2017
Messages
12
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.

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
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

Board Regular
Joined
Jun 27, 2014
Messages
107
Well, (without testing), a Value can not be assigned to a cell range

try ws.Range("I2").value
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,669
Remove the .Value:
Code:
Set TxtRng = ws.Range("I2")
Even easier, skip using TxtRng altogether and use:
Code:
ws.Range("I2").Formula = Form
 
Last edited:

pmgibs

New Member
Joined
Apr 5, 2017
Messages
12
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
Code:
TxtRng.Value = Form
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,364
Office Version
365
Platform
Windows
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

New Member
Joined
Apr 5, 2017
Messages
12
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.
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top