Thread: VB doesn't like this Formula?

I don't understand why vb doesn't like this formula. Please help me
see what I am overlooking...

Dim Last_Column As Range
Set Last_Column = Worksheets(sName).Range("T2:T10")

Last_Column.Formula = "=IF(OR(" _
AND(" & Min_Limit & "<=Q2," & Max_Limit & ">=Q2), _
AND(" & Min_Limit & "<=R2," & Max_Limit & ">=R2), _
AND(" & Min_Limit & ">=Q2," & Max_Limit & "<=R2)),1,0)"

double quotations needed? ("""")

Where?

When trying to write a formula to a cell you need to double up on your quotations

Last_Column.Formula = "=IF(OR("" _
AND("" & Min_Limit & ""<=Q2,"" & Max_Limit & "">=Q2), _
AND("" & Min_Limit & ""<=R2,"" & Max_Limit & "">=R2), _
AND("" & Min_Limit & "">=Q2,"" & Max_Limit & ""<=R2)),1,0)"

When it puts the Formula in the cell it will remove the additional quotes. remember only double quotes for the formula that will be in the cell not around the formula. Hope THis Helps

Ok, I changed to the following, but I'm still getting an error...?

Last_Column.Formula = "=IF(OR( _
AND("" & RF_Min_Limit & ""<=Q2,"" & RF_Max_Limit & "">=Q2), _
AND("" & RF_Min_Limit & ""<=R2,"" & RF_Max_Limit & "">=R2), _
AND("" & RF_Min_Limit & "">=Q2,"" & RF_Max_Limit & ""<=R2)),1,0)"

I don't think it is double quotes that you need as I think Min_Limit and
Max_Limit are numbers.

Try

Dim Last_Column As Range
Set Last_Column = Worksheets(sname).Range("T2:T10")

Last_Column.Formula = "=IF(OR(" & _
"AND(" & Min_Limit & "<=Q2," & Max_Limit & ">=Q2)," & _
"AND(" & Min_Limit & "<=R2," & Max_Limit & ">=R2)," & _
"AND(" & Min_Limit & ">=Q2," & Max_Limit & "<=R2)),1,0)"

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"DISMfish" <DISMfish@gmail.com> wrote in message
news:1147787799.682216.206080@i39g2000cwa.googlegroups.com...
> Ok, I changed to the following, but I'm still getting an error...?
>
> Last_Column.Formula = "=IF(OR( _
> AND("" & RF_Min_Limit & ""<=Q2,"" & RF_Max_Limit & "">=Q2), _
> AND("" & RF_Min_Limit & ""<=R2,"" & RF_Max_Limit & "">=R2), _
> AND("" & RF_Min_Limit & "">=Q2,"" & RF_Max_Limit & ""<=R2)),1,0)"
>

You can't put a continuation character inside a string

s = "ABC _
EFG"

doesn't work.

s = "ABC" & _
"EFG"

--
Regards,
Tom Ogilvy


"DISMfish" wrote:

> Ok, I changed to the following, but I'm still getting an error...?
>
> Last_Column.Formula = "=IF(OR( _
> AND("" & RF_Min_Limit & ""<=Q2,"" & RF_Max_Limit & "">=Q2), _
> AND("" & RF_Min_Limit & ""<=R2,"" & RF_Max_Limit & "">=R2), _
> AND("" & RF_Min_Limit & "">=Q2,"" & RF_Max_Limit & ""<=R2)),1,0)"
>
>

Thanks, that did it!

Thanks, that did it!

Where? What's the difference between single and double?

Thanks

^ Please disregard the previous comment. It looks like there was a lag
in posting that comment.

Thanks Tom and Bob!

Last_Column.Formula = "=IF(OR(" & _
"AND(" & Min_Limit & "<=Q2," & Max_limit & ">=Q2)," & _
"AND(" & Min_Limit & "<=R2," & Max_limit & ">=R2)," & _
"AND(" & Min_Limit & ">=Q2," & Max_limit & "<=R2)),1,0)"

HTH
--
AP

"DISMfish" <DISMfish@gmail.com> a écrit dans le message de news:
1147786202.388381.111010@j33g2000cwa.googlegroups.com...
>I don't understand why vb doesn't like this formula. Please help me
> see what I am overlooking...
>
> Dim Last_Column As Range
> Set Last_Column = Worksheets(sName).Range("T2:T10")
>
> Last_Column.Formula = "=IF(OR(" _
> AND(" & Min_Limit & "<=Q2," & Max_Limit & ">=Q2), _
> AND(" & Min_Limit & "<=R2," & Max_Limit & ">=R2), _
> AND(" & Min_Limit & ">=Q2," & Max_Limit & "<=R2)),1,0)"
>

Thanks, that did it!