VB code to place formula in cell please

[slimimi]

Hello, would anyone know how to do the following?

Upon user making selection from data validation list in Active Row Selection & Column A : i would like to do the following (is possible)

,,,,,,,,,,,,,,,
Place formula in Active Cell Row & Column S

=MT4|BID! (followed by cell A1 but replace the "/" with "") followed by "m"

I have tried several combinations but with no success.
Also - where would i place this code so that it automatically updates the formula in Column S as soon as the user changes selection in Cell A?

Thanks a million in advance.

 

[mikerickson]

ActiveCell.EntireRow.Range("S1").FormulaR1C1 = "=""MT4|BID""&SUBSTITUTE(R1C1,""/"","""")&""m"""

 

[slimimi]

Hi mike - thank you. I will try that out now. Hope you are well.

I still did not manage to get the beep happening on the other formula you kindly helped me with. I figured that it was looking at the whole range of Column G, for example. So if there is any cell in column G which has the value of less than 100 - no matter what cell you go to in Column G - if you try to type 101 - you will not hear any beep

I just need to change the formula to look at every individual cell and match the criteria to that individual cell instead. Is this possible?

I can post the code again if you don't have it handy.

 

[slimimi]

Hi mike - i tried the code - pasted into worksheet module and under worksheet selection change (hope its right).

I realize i need to limit the Entire Row Range to the rows 5,8,11,14,17,20 and 23.
Also - the code just places text in Column S but not an actual formula.

The contents in the cell after i run the code is :

="MT4|BID"&SUBSTITUTE($A$1,"/","")&"m"

Also, the last thing is that i need the formula to change depending on the information in Column A of that particular row. At the moment, it only references to A1. So if i type different currencies into A5, A8, A11, etc, all i can see in Column S is the value entered into A1.

Hope this makes sense

 

[mikerickson]

This will make that reference relative to row.

ActiveCell.EntireRow.Range("s1").FormulaR1C1 = "= ""MT4|BID""&SUBSTITUTE(RC1,""/"","""")&""m"""

 

[slimimi]

Thank you. This is still not placing a formula in the cell though.

 

[mikerickson]

It is on my machine. What is the entire routine that you are using?

 

[slimimi]

I went to The Sheets Module and, under Worksheet Selection Change - i pasted your code. Then went back to Sheet and entered AUD/USD in Cell A1.
Now when i look at S1 - it shows me the following in Column S cells.

MT4|BID!AUDUSDm

But it is not placing this as a FORMULA in the cell - its just displaying the above as text in that cell.

This is all on a New workbook which i have opened just to test the code.

What am i doing wrong?

Can i achieve this through just placing a formula in the relevant cells in Column S that will do this instead perhaps? Maybe VB is not able to place this as a formula in the cell. I dont know.

Any suggestions please?
I am really scratching my head and would love to solve this one cause alot of other things in my workbook depend on this formula in column S.

Thanks a million in advance.

ps. what are you getting in Column S when you enter AUD/USD in A1 for example?

 

[mikerickson]

There is some setting that is causing that, I've never had that problem.
Have you tried running it from a sub in a normal module?

There have been a couple of threads about "Cell shows formula not result", but it sounded like a Windows problem so I didn't pay much attention.

 

[slimimi]

Strange - because when i type in =MT4|BID!AUDUSD
there is no problem and live price feed comes in straight away.