Help with forumula

[davide128]

Hi I have this formula and would like to know how to copy it to a range while maintaning the reference..

=IF(Q2="MAP1234","Qualified in all 50 States",IF(Q2="MAP123","Southern;South and North Central",IF(Q2="MAP12","Southern;South-Central",IF(Q2="MAP13","Southern;North-Central",IF(Q2="MAP14","Southern;Northern",IF(Q2="MAP1","Southern",IF(Q2="MAP234","South and North Central;Northern",IF(Q2="MAP23","South and North Central",IF(Q2="MAP24","South-Central;Northern",IF(Q2="MAP2","South-Central",IF(Q2="MAP34","North-Central;Northern",IF(Q2="MAP3","North-Central",IF(Q2="MAP4","Northern","Does Not Qualify")))))))))))))

so if I do something like this to select range

Range("A2", "A" + LTrim(Str(ActiveSheet.UsedRange.Rows.count)))

so the end result I need the reference to maintain so that the formula for A3 will be Q3 A4 Q4 A5 Q5 etcc....

I also tried copying the formula into a cell on sheet "Upload" and then ran code below but all it does it paste into specified range as text

'Add EstarWW Formula
    Sheets("Uploads").Select
    Range("DG2").Select
    Selection.Copy
    Sheets("PCR Format").Select
    Range("C2", "C" + LTrim(Str(ActiveSheet.UsedRange.Rows.count))).Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

 

[BobUmlas]

Not sure I follow, but it seems like you can simply change Q2 to $Q2

 

[jmthompson]

Not tested

Range("A2", "A" + LTrim(Str(ActiveSheet.UsedRange.Rows.count))).Formula = "=IF(Q2=""MAP1234"",""Qualified in all 50 States"",IF(Q2=""MAP123"",""Southern;South and North Central"",IF(Q2=""MAP12"",""Southern;South-Central"",IF(Q2=""MAP13"",""Southern;North-Central"",IF(Q2=""MAP14"",""Southern;Northern"",IF(Q2=""MAP1"",""Southern"",IF(Q2=""MAP234"",""South and North Central;Northern"",IF(Q2=""MAP23"",""South and North Central"",IF(Q2=""MAP24"",""South-Central;Northern"",IF(Q2=""MAP2"",""South-Central"",IF(Q2=""MAP34"",""North-Central;Northern"",IF(Q2=""MAP3"",""North-Central"",IF(Q2=""MAP4"",""Northern"",""Does Not Qualify"")))))))))))))"

</pre>

 

[davide128]

that gives me run-time error '1004'

application-defined or object-defined error

 

[jmthompson]

Is Q2 always supposed to be Q2? Meaning do you want the same result in every cell of column A?

 

[jmthompson]

BTW, I think the reason for the error is there are too many nested IF's

 

[davide128]

yeah..I saved as excel macro enabled and not campatiblity mode and it worked...

how about this formula..how do u know where to put the extra quotations marks?

=IF(AND(D2="",F2=""),"Single Glazed",IF(AND(D2="",F2<>""),"Single Glazed * Low E",IF(AND(D2<>"",F2="",H2="AIR"),"Dual Glazed * Air Fill",IF(AND(D2<>"",F2<>"",H2="AIR"),"Dual Glazed * Air Fill * Low E",IF(AND(D2<>"",F2="",H2="ARG"),"Dual Glazed * Argon Gas",IF(AND(D2<>"",F2<>"",H2="ARG"),"Dual Glazed * Argon Gas * Low E"))))))

 

[jmthompson]

In VBA, the entire formula always needs to be enclosed with quotes. If something within your formula is in quotes, you need to enclose that in an extra set of quotes.

yeah..I saved as excel macro enabled and not campatiblity mode and it worked...

how about this formula..how do u know where to put the extra quotations marks?

=IF(AND(D2="",F2=""),"Single Glazed",IF(AND(D2="",F2<>""),"Single Glazed * Low E",IF(AND(D2<>"",F2="",H2="AIR"),"Dual Glazed * Air Fill",IF(AND(D2<>"",F2<>"",H2="AIR"),"Dual Glazed * Air Fill * Low E",IF(AND(D2<>"",F2="",H2="ARG"),"Dual Glazed * Argon Gas",IF(AND(D2<>"",F2<>"",H2="ARG"),"Dual Glazed * Argon Gas * Low E"))))))