| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
place a formula in a cell with macro
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
place a formula in a cell with macro
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)=""B B"")),""N/A"",""writesomething""))" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Seeker" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
place a formula in a cell with macro
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() ??? "Don Guillett" wrote: 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)=""B B"")),""N/A"",""writesomething""))" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Seeker" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
place a formula in a cell with macro
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? "Seeker" wrote: 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() ??? "Don Guillett" wrote: 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)=""B B"")),""N/A"",""writesomething""))" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Seeker" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
place a formula in a cell with macro
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 macro -- mudraker If my reply has assisted or failed to assist you I welcome your Feedback. www.thecodecage.com ------------------------------------------------------------------------ mudraker's Profile: http://www.thecodecage.com/forumz/member.php?userid=18 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67601 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
place a formula in a cell with macro
Tks Don, I sort it out now.
"Don Guillett" wrote: 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 "Seeker" wrote in message ... 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() ??? "Don Guillett" wrote: 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)=""B B"")),""N/A"",""writesomething""))" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Seeker" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
place a formula in a cell with macro
Tks Mudraker, I sort it out now.
"mudraker" wrote: 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 macro -- mudraker If my reply has assisted or failed to assist you I welcome your Feedback. www.thecodecage.com ------------------------------------------------------------------------ mudraker's Profile: http://www.thecodecage.com/forumz/member.php?userid=18 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67601 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
place a formula in a cell with macro
ALWAYS post your final result for the archives
-- Don Guillett Microsoft MVP Excel SalesAid Software "Seeker" wrote in message ... Tks Don, I sort it out now. "Don Guillett" wrote: 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 "Seeker" wrote in message ... 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() ??? "Don Guillett" wrote: 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)=""B B"")),""N/A"",""writesomething""))" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Seeker" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can a formula place a value in another cell | Excel Discussion (Misc queries) | |||
| Formula/Macro to place data on the next blank row | Excel Discussion (Misc queries) | |||
| Filter list in place if a cell has a keyword within a formula | Excel Programming | |||
| Is there a formula that will place the sheet name in a cell? | Excel Worksheet Functions | |||
| how to place formula to cell | Excel Programming | |||
Linear Mode
