Populate Cell using VB Code please help!!!!

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532
Hi there. I need to use this code on a spreadsheet

=MT4|BID!EURUSDm

It uses DDE to talk to a program called MetaTrader and this places 'Live' price quotes in that cell.

Problem
1.User selects currency pair in Cell C4
2.I need code to place a formula in Cell F4 constructed as follows:

=MT4|BID!cell c4m

I have been trying to get this work for weeks now but with no joy :(
Whatever i try to do - it only places the above as TEXT in that cell, even if i open a new sheet and cells are set to General format, however - when i type the above code in a cell - i have no problems and start getting price data straight away. Hmmmmmmmmm. I just dont get it.

Would really appreciate someones help on this.
Thanks in advance.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Perhaps:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Columns(3))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If c <> "" Then c.Offset(, 3).Formula = "=MT4|BID!" & c & "m"
    Next
Application.EnableEvents = True
End Sub
 
Upvote 0
Oh Fantastic - thank you - i CANT wait to try this.
Please tell me - where do i put this code?
Do i put it in the worksheet > rightclick > view code? or somewhere else?

Thanks in advance
 
Upvote 0
WOW - hotpepper - you are FANTASTIC - it worked like a treat- THANK YOU THANK YOU...

Please can i ask you one more thing.

Actually the user selects from a list which displays, for example, EUR/USD or USD/JPY

I actually need to add into your code so that it removes the / inbetween the 2 currency pairs.

Would you be able to help me with that please?

Much Appreciated.
 
Upvote 0
Hi hot pepper - i got the substitue "/" working which schiern helped me out with.

with your code - if i didnt want to offset and actually wanted to specify a cell, say, F5 to put the data in - how would i go about doing this?


If c <> "" Then c.Offset(, 3).Formula = "=MT4|BID!" & c & "m"
</pre>
 
Upvote 0
Can i get a little bit more complicated please?

With your code below : (which works great)


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Columns(3))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
If c <> "" Then c.Offset(, 3).Formula = "=MT4|BID!" & c & "m"
Next
Application.EnableEvents = True</pre>

End Sub</pre>i cant figure out how to take c and substitue the "/" with "".
I know how to do that on its own but dont know how to merge it with your code line (in red).

Also - i cant figure out how to expand the code a little so that i can:

place formula

"=MT4|BID!" & c & "m" ----- in cell G4, for example AND

place formula

"=MT4|ASK!" & c & "m" ----- in cell H4, for example

woudl this be possbile?
 
Upvote 0
You don't need SUBSTITUTE, VBA has a function called Replace that will do what you want:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Columns(3))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
If c <> "" Then
    c.Offset(, 4).Formula = "=MT4|BID!" & Replace(c, "/", "") & "m"
    c.Offset(, 5).Formula = "=MT4|ASK!" & Replace(c, "/", "") & "m"
End If
Next
Application.EnableEvents = True
End Sub
 
Upvote 0
thats great - thank you.
If i wanted to clear the contents of those 2 cells, if nothing is entered in target column, could i just type the following underneath :-

If d is nothing then range("D3","E3").clearcontents
exit sub
 
Upvote 0
No, not like that but I assume you mean if you blank the value in Column C, is this correct? Also why are you blanking columns D & E when your formulas are going in G & H?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top