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]

Oh that formula should result in the string "MT4|B...etc" if you are using the concatiation to create a cell address and return the value in that cell, INDIRECT is needed.

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

 

[slimimi]

OK i have tried that now but still no joy

When you paste this code into a new workbook - what result are you getting in Column S please?

For me - it is now giving me a #REF error in Column S.

 

[mikerickson]

In a new workbook, I get the formula
=INDIRECT( "MT4|BID"&SUBSTITUTE($A1,"/","")&"m")

It sounds like VB isn't the problem.
What formula do you want in the cell?

 

[slimimi]

In the cell - i would like to place the following formula:

=MT4|BID! *and then the value from Cell A1 without the "/"and then "m"*

So if, for example, i entered AUD/USD into Cell A1 - then the formula should be (in this case) :

=MT4|BID!AUDUSDm

 

[mikerickson]

Formulas can't work that way. The formulation in posts 11/13 will put

=INDIRECT("MT4|BID!AUDUSDm") in the cell, which will return #REF if there is no sheet MT4|BID or no namedRange AUDUSDm on that range. If you the named range AUDUSDm is a workbook wide name, you don't need the sheet name.

 

[slimimi]

Hi mike. I done think thats the case because if i manually type in the formula - it works no problem and i get live price feed in the cell.

MT4 is a Trading Platform Application.
AUDUSDm is what you need to type in to get the price for that currency via DDE from MT4.

Hope that helps.

 

[Ragnar1211]

This is the code i have used in the past to convert text to formulas:

Sub ConvertStringToFormula()

ActiveCell.Formula = ActiveCell.Text

End Sub


If you want to convert a selected area:

Sub TextIntoFormulas()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = cell.Text
End If
Next cell
End Sub

 

[mikerickson]

If MT4 is a different application, then there is no spreasheet formula that can help you.

In that case, its an Automation issue. I am not a computer person and have no experience with Automation. To poll another application and send it a control string requires a different approach than putting a formula in a cell.

 

[Ragnar1211]

I use the code posted above to convert formula's for third party apps.

the code will work.