Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to enter formula with text
For some reason when I execute my macro, my formulas which include text
won't update unless I select the cell and then click in the formula bar and then hit enter. It was suggested by someone here to therefore create a macro that will re-install the formulas. Is there a way to write code to install a formula like this for several cells : =COUNTIF(A$2:A$374,"RM01"). The only thing that changes in the formula is the text. For each cell the text changes by a value of 1. For example: =COUNTIF(A$2:A$374,"RM02") =COUNTIF(A$2:A$374,"RM03") =COUNTIF(A$2:A$374,"RM04").........and so on. Is there some kind of loop or something I can use to make writing this code less tedious??? Thanks and Happy new year. --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to enter formula with text
That's pretty frustrating converting from text mode.
Originally, I would press F2 then Enter for as many cells as I would need to change. After a while I found a quicker way - I don't know if there is an easier way - probably is. Go to formula mode: Tools | Options | Window options - Formulas = ticked (shortcut key is Ctrl ~) Highlight the column | Edit | Copy. Open Notepad Paste Select All Copy Go back to Excel, Paste - take care to make sure it pasted in the same position. untick formula mode If you really want to be building your formula, you could do something like: ="=COUNTIF(A$2:A$374,""" & B2 & """)" Where B2 = RM02 Then fill the formula down. Copy the result into Notepad, then copy them back to Excel which will then accept them as a formula Rob "bambam77" wrote in message ... For some reason when I execute my macro, my formulas which include text won't update unless I select the cell and then click in the formula bar and then hit enter. It was suggested by someone here to therefore create a macro that will re-install the formulas. Is there a way to write code to install a formula like this for several cells : =COUNTIF(A$2:A$374,"RM01"). The only thing that changes in the formula is the text. For each cell the text changes by a value of 1. For example: =COUNTIF(A$2:A$374,"RM02") =COUNTIF(A$2:A$374,"RM03") =COUNTIF(A$2:A$374,"RM04").........and so on. Is there some kind of loop or something I can use to make writing this code less tedious??? Thanks and Happy new year. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to enter formula with text
Depending on where you're putting the formula, maybe you can use something like:
=COUNTIF(A$2:A$374,"RM"&TEXT(ROW()+1,"00")) But watch out if you add rows! If you really want them hardcoded in your formula, you could use a formula like: ="=COUNTIF(A$2:A$375,""RM"&TEXT(ROW()+1,"00")&""") " and drag down. This'll return a string that looks like your formula. copy|paste special|values then Edit|replace = (equal sign) with = (equal sign) and excel will see it them as formulas again and reevaluate. bambam77 wrote: For some reason when I execute my macro, my formulas which include text won't update unless I select the cell and then click in the formula bar and then hit enter. It was suggested by someone here to therefore create a macro that will re-install the formulas. Is there a way to write code to install a formula like this for several cells : =COUNTIF(A$2:A$374,"RM01"). The only thing that changes in the formula is the text. For each cell the text changes by a value of 1. For example: =COUNTIF(A$2:A$374,"RM02") =COUNTIF(A$2:A$374,"RM03") =COUNTIF(A$2:A$374,"RM04").........and so on. Is there some kind of loop or something I can use to make writing this code less tedious??? Thanks and Happy new year. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to enter formula with text
Edit Replace = with =
That's a cool trick. I love it! Another trick I've learned is to make Excel see values (from text) again, put 1 into a cell somewhere, edit copy, select values, edit pastespecial multiply "Dave Peterson" wrote in message ... Depending on where you're putting the formula, maybe you can use something like: =COUNTIF(A$2:A$374,"RM"&TEXT(ROW()+1,"00")) But watch out if you add rows! If you really want them hardcoded in your formula, you could use a formula like: ="=COUNTIF(A$2:A$375,""RM"&TEXT(ROW()+1,"00")&""") " and drag down. This'll return a string that looks like your formula. copy|paste special|values then Edit|replace = (equal sign) with = (equal sign) and excel will see it them as formulas again and reevaluate. bambam77 wrote: For some reason when I execute my macro, my formulas which include text won't update unless I select the cell and then click in the formula bar and then hit enter. It was suggested by someone here to therefore create a macro that will re-install the formulas. Is there a way to write code to install a formula like this for several cells : =COUNTIF(A$2:A$374,"RM01"). The only thing that changes in the formula is the text. For each cell the text changes by a value of 1. For example: =COUNTIF(A$2:A$374,"RM02") =COUNTIF(A$2:A$374,"RM03") =COUNTIF(A$2:A$374,"RM04").........and so on. Is there some kind of loop or something I can use to make writing this code less tedious??? Thanks and Happy new year. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to enter formula with text
I've switched to copying an empty cell and adding.
then those blank cells stay blank. Rob van Gelder wrote: Edit Replace = with = That's a cool trick. I love it! Another trick I've learned is to make Excel see values (from text) again, put 1 into a cell somewhere, edit copy, select values, edit pastespecial multiply "Dave Peterson" wrote in message ... Depending on where you're putting the formula, maybe you can use something like: =COUNTIF(A$2:A$374,"RM"&TEXT(ROW()+1,"00")) But watch out if you add rows! If you really want them hardcoded in your formula, you could use a formula like: ="=COUNTIF(A$2:A$375,""RM"&TEXT(ROW()+1,"00")&""") " and drag down. This'll return a string that looks like your formula. copy|paste special|values then Edit|replace = (equal sign) with = (equal sign) and excel will see it them as formulas again and reevaluate. bambam77 wrote: For some reason when I execute my macro, my formulas which include text won't update unless I select the cell and then click in the formula bar and then hit enter. It was suggested by someone here to therefore create a macro that will re-install the formulas. Is there a way to write code to install a formula like this for several cells : =COUNTIF(A$2:A$374,"RM01"). The only thing that changes in the formula is the text. For each cell the text changes by a value of 1. For example: =COUNTIF(A$2:A$374,"RM02") =COUNTIF(A$2:A$374,"RM03") =COUNTIF(A$2:A$374,"RM04").........and so on. Is there some kind of loop or something I can use to make writing this code less tedious??? Thanks and Happy new year. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to enter formula with text
Thanks for the help. The find replace = with = worked. I'm glad I found
this site. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula is changing to text when I hit enter | Excel Discussion (Misc queries) | |||
how to enter a formula in a text box | Excel Worksheet Functions | |||
Using a macro to enter a formula | Excel Worksheet Functions | |||
Enter Static Date with a formula or macro | Excel Discussion (Misc queries) | |||
Can you enter a formula in a cell to run a macro? | Excel Worksheet Functions |