place a formula in a cell with macro

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
 
D

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
 
S

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() ???
 
S

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

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
 
D

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

Seeker

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top