place a formula in a cell with macro

[Seeker]

I have following formula in a cell which shows output depends on other cells
conditions, it also prompt users to write data in same cell thus this formula
will be erased, I am looking for a macro which can place this formula back
into cell after user finished every single input.

=IF(AND(F2<>"AA",UPPER(F12)<>"BB"),VLOOKUP(F9&"."&G10,Records!C2:R65536,6),IF(OR(AND(F2<>"AA",UPPER(F12)="BB"),AND(F2="AA",UPPER(F12)="BB")),"N/A","write
something"))

Tks

 

[Don Guillett]

The key is to use "" "" instead of " "

Sub placeformula()'one line
Range("f3").Formula =
"=IF(AND(F2<>""AA"",UPPER(F12)<>""BB""),VLOOKUP(F9&"".""&G10,Records!C2:R65536,6),IF(OR(AND(F2<>""AA"",UPPER(F12)=""BB""),AND(F2=""AA"",UPPER(F12)=""BB"")),""N/A"",""writesomething""))"

End Sub

 

[Seeker]

Hi Don,
Tks for your reply. However, when I placed following

Sub placeformula()
Range("F3").Formula = "IF(AND(............)" (had changed all " " to "" ""
for textwithin formula)
End Sub

It prompt me "must be an End Sub" at line above Sub placeformula() ???

 

[Seeker]

P.S. I used the recorder to start with the macro sheet, this macro sheet
start with Sub Macro1() and at the very end with End Sub, so would this cause
the problem? If so, how could I rectify it please?

 

[mudraker]

Place this between macro1 & the end sub


Code
-------------------
Range("F3").Formula = "IF(AND(............)" (had changed all " " to "" ""
for textwithin formula
-------------------


or replacr all of the macro recording with Dons macr

--
mudrake

If my reply has assisted or failed to assist you I welcome you
Feedback.

www.thecodecage.co

 

[Don Guillett]

What I sent was ONE line sub meant to stand alone and not as part of another
macro. If desired, send your workbook to my address below along with
desires, a snippet of this msg in an inserted sheet and before/after
examples.

 

[Seeker]

Tks Don, I sort it out now.

What I sent was ONE line sub meant to stand alone and not as part of another
macro. If desired, send your workbook to my address below along with
desires, a snippet of this msg in an inserted sheet and before/after
examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Seeker]

Tks Mudraker, I sort it out now.

 

[Don Guillett]

ALWAYS post your final result for the archives