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/

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/

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

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

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

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/