Excel 2007 Macro/VB Question DDE Question

[MadDog22]

Hi,
So I have a column of cells that are the combination of a other cells in
the row and a result of a few "if functions." The value comes out exactly as
planned however in order for the data to be sent to the DDE server I need to
press F2, go the beginning of the cell to insert an "=" sign and then press
enter. I would like a macro to do what I just mentioned and then go down to
the cell in the column. I used to write code using C++, but I'm a little
rusty and unfamiliar with VB. Below is the formula and then the result of
the cell. Any information or suggestions would be greatly appreciated!


=(IF(G10>0,"=ANVDdeSrv|"&B10&"!SendOrder"&L10&"NYSE_"&ABS(G10)&"_1_100_"&M10,IF(G10<0,"=ANVDdeSrv|"&B10&"!SendOrder"&L10&"NYSE_"&ABS(G10)&"_1_100_"&N10,)))

ANVDdeSrv|AMD!SendOrder_Sell_NYSE_300_1_100_3.3

 

[Daryl S]

MadDog22 -

Can you just change your formula to put the "=" in front, like this:

=(IF(G10>0,"=" &
"ANVDdeSrv|"&B10&"!SendOrder"&L10&"NYSE_"&ABS(G10)&"_1_100_"&M10,IF(G10<0,"=ANVDdeSrv|"&B10&"!SendOrder"&L10&"NYSE_"&ABS(G10)&"_1_100_"&N10,)))

 

[MadDog22]

Awesome! Half way there. It adds the = at the beginning now, but I still
have to copy it and paste special(values) into another cell, hit F2 and enter
for it to send the order to the DDE. I'm looking to have it send the order
and then move down to teh next cell in the column and repeat until about row
70 at 15:59:55.

 

[Daryl S]

MadDog22 -

You can't have a formula and a value in the same cell, so some actions will
still be needed. To make it easy, you can record a macro of your copy/paste,
F2, Enter, and move to the next cell, and then adjust the macro to work on
the current cell. You can run this macro on each cell, or if you have all
the data ready at once, put a loop in your macro to start at the top cell,
and for each cell do the copy/paste, F2, Enter, and move to the next cell.
End the loop when it gets no data, or maybe this is a fixed range in your
case.

 

[MadDog]

I have figured out how to make a macro copy and paste the value to the cell in the next column and then go to the next row in the column. However, I cannot figure out how to create a macro that would enter a cell, hit F2, ?press enter? then proceed to the cell below it. The macros I create just paste the value from the first cell in the column to all the cells below it. Any ideas?



Daryl S wrote:

MadDog22 -You cannot have a formula and a value in the same cell, so some
26-Feb-10

MadDog22 -

You cannot have a formula and a value in the same cell, so some actions will
still be needed. To make it easy, you can record a macro of your copy/paste,
F2, Enter, and move to the next cell, and then adjust the macro to work on
the current cell. You can run this macro on each cell, or if you have all
the data ready at once, put a loop in your macro to start at the top cell,
and for each cell do the copy/paste, F2, Enter, and move to the next cell.
End the loop when it gets no data, or maybe this is a fixed range in your
case.

--
Daryl S


:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
What's New for Developers in SharePoint 2010 Object Model?
http://www.eggheadcafe.com/tutorial...d8-3e2773fa29b5/whats-new-for-developers.aspx

 

[MadDog22]

bump

 

[Doug Travers]

Bump

 

[Roger Govier]

Hi

The following code assumes that your formulae are in cells Q10 onward,
and you want the values in R10 onward

Sub FixValues()
dim lr as long
lr = Cells(Rows.count,"Q").|End(Xlup).Row
Range("R10:R" & lr).value = Range("Q10:Q" & lr).value
End Sub

 

[MadDog22]

It copies, but it comes up as #N/A in R10 even though there is a value
calculated from the formula. Below is the Formula for the Q10 Cell. Your
help is VERY appreciated. Thank you.

=IF(E17>0,"=ANVDdeSrv|"&B17&"!SendOrder"&H17&"NYSE_"&ABS(E17)&"_1_"&ABS(E17)&"_"&I17,IF(E17<0,"=ANVDdeSrv|"&B17&"!SendOrder"&H17&"NYSE_"&ABS(E17)&"_1_"&ABS(E17)&"_"&J17,))

 

[Roger Govier]

Hi

If you want to send me your workbook, i will take a look
send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

 

[MadDog22]

I just emailed you my workbook. Your help is VERY appreciated for I've been
stuck on this for almost two weeks now. Thanks.
Doug

 

[Roger Govier]

Hi Doug

Nothing has arrived here yet 9:44 GMT
I am out now till this afternoon, but will look at it then if it arrives.

 

[MadDog22]

I emailed it to (e-mail address removed), but it bounced back. Is that your
correct email address?

 

[Roger Govier]

Yes that is correct - but I wish you hadn't typed it in the email!!!
The spambots will now get o work and I will have another heap of cr*p
mails for a while.

you could try
roger dot govier at btinternet dot com