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&"NYS E_"&ABS(G10)&"_1_100_"&M10,IF(G10<0,"=ANVDdeSrv|"& B10&"!SendOrder"&L10&"NYSE_"&ABS(G10)&"_1_100_"&N1 0,)))

ANVDdeSrv|AMD!SendOrder_Sell_NYSE_300_1_100_3.3

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&"!SendOr der"&L10&"NYSE_"&ABS(G10)&"_1_100_"&N10,)))

--
Daryl S


"MadDog22" wrote:

> 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&"NYS E_"&ABS(G10)&"_1_100_"&M10,IF(G10<0,"=ANVDdeSrv|"& B10&"!SendOrder"&L10&"NYSE_"&ABS(G10)&"_1_100_"&N1 0,)))
>
> ANVDdeSrv|AMD!SendOrder_Sell_NYSE_300_1_100_3.3
>

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" wrote:

> 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&"!SendOr der"&L10&"NYSE_"&ABS(G10)&"_1_100_"&N10,)))
>
> --
> Daryl S
>
>
> "MadDog22" wrote:
>
> > 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&"NYS E_"&ABS(G10)&"_1_100_"&M10,IF(G10<0,"=ANVDdeSrv|"& B10&"!SendOrder"&L10&"NYSE_"&ABS(G10)&"_1_100_"&N1 0,)))
> >
> > ANVDdeSrv|AMD!SendOrder_Sell_NYSE_300_1_100_3.3
> >

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.

--
Daryl S


"MadDog22" wrote:

> 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" wrote:
>
> > 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&"!SendOr der"&L10&"NYSE_"&ABS(G10)&"_1_100_"&N10,)))
> >
> > --
> > Daryl S
> >
> >
> > "MadDog22" wrote:
> >
> > > 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&"NYS E_"&ABS(G10)&"_1_100_"&M10,IF(G10<0,"=ANVDdeSrv|"& B10&"!SendOrder"&L10&"NYSE_"&ABS(G10)&"_1_100_"&N1 0,)))
> > >
> > > ANVDdeSrv|AMD!SendOrder_Sell_NYSE_300_1_100_3.3
> > >

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


"MadDog22" wrote:

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/tutorials...evelopers.aspx

bump

"MadDog" wrote:

> 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
>
>
> "MadDog22" wrote:
>
> 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/tutorials...evelopers.aspx
> .
>

Bump

On Mar 4, 6:16*pm, MadDog wrote:
> 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
>
> "MadDog22" wrote:
>
> 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/tutorials...5c-49b7-a0d8-3...

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


--
Regards
Roger Govier

MadDog22 wrote:
> bump
>
> "MadDog" wrote:
>
>> 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
>>
>>
>> "MadDog22" wrote:
>>
>> 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/tutorials...evelopers.aspx
>> .
>>

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,"=ANVD deSrv|"&B17&"!SendOrder"&H17&"NYSE_"&ABS(E17)&"_1_ "&ABS(E17)&"_"&J17,))

"Roger Govier" wrote:

> 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
>
>
> --
> Regards
> Roger Govier
>
> MadDog22 wrote:
> > bump
> >
> > "MadDog" wrote:
> >
> >> 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
> >>
> >>
> >> "MadDog22" wrote:
> >>
> >> 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/tutorials...evelopers.aspx
> >> .
> >>
> .
>

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
--
Regards
Roger Govier

MadDog22 wrote:
> 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,"=ANVD deSrv|"&B17&"!SendOrder"&H17&"NYSE_"&ABS(E17)&"_1_ "&ABS(E17)&"_"&J17,))
>
> "Roger Govier" wrote:
>
>> 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
>>
>>
>> --
>> Regards
>> Roger Govier
>>
>> MadDog22 wrote:
>>> bump
>>>
>>> "MadDog" wrote:
>>>
>>>> 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
>>>>
>>>>
>>>> "MadDog22" wrote:
>>>>
>>>> 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/tutorials...evelopers.aspx
>>>> .
>>>>
>> .
>>