place a formula in a cell with macro

  • Thread starter Thread starter Seeker
  • Start date Start date
S

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
 
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
 
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() ???
 
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?
 
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.
 
Tks Don, I sort it out now.

Don Guillett said:
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)
Click to expand...
 
Back
Top