VBA not working with imported numbers

[sherman51]

Hi,
Yesterday Sintek very kindly provided me with vba to calc and display the differnce in a col of changing numbers ... which works exactly how I wanted,
However, I find that when I apply the vba in situ on my spreadsheet (that is connected to the source of the numbers) it dosn't react to the incoming changing numbers. It works if I type numbers in, but does nothing with the displayed numbers.

Any help appreciated
Thanks
Paul

[MarvinP]

Hi Paul,

The most likely answer is that the imported numbers are really TEXT!! You can convert all those text-numbers to real numbers by adding zero to them or multiplying them by one!

See if that works.

Here are some other ways to convert them:

https://fasteratexcel.com/excel-conv...umbers-format/

[6StringJazzer]

My guess is that your imported numbers are stored in Excel as text, not numbers. There are many ways to resolve this, here is one:

1. Enter a 1 in a blank cell and copy it.
2. Select the cells containing the numbers, right-click and select Paste Special, Paste Special.
3. From the dialog box that opens, select Multiply, and click OK.
4. The act of multiplying the values by 1 forces the contents of the cells to become their numerical values.

If you find a method that works for you, you can run the macro recorder, and then incorporate that code into your other VBA.

There could be other ways to revise the VBA, but since you didn't provide it I can't offer any further suggestions. If you attach your file we can give a solution for your specific file.

[sherman51]

Thank you both for your suggestions, I have tried various ways like adding 0 and multiply x 1 but to no effect,
also the incoming numbers are formatted as currency, so i sm thinking thats ok from a formatting point of view?
Thanks
Paul

[Sintek]

Perhaps...the the code supplied is not for your actual setup...Not correct Event used...
https://www.excelforum.com/excel-pro...f-figures.html
How is the values in [C3] being updated...

[sherman51]

the sheet connects to a trading platform, they tell me it's an "event change"
Thanks
Paul

[Sintek]

As per Jeff...

If you attach your file we can give a solution for your specific file.

[MarvinP]

Hi Sherman,

If you have a currency symbol in those "text numbers" like ($4.50) then multiplying by 1 or adding zero won't work. You first need to remove the dollar sign (or currency symbol) before doing my techniques above.

Use the Substitute() function in Excel to remove the dollar sign.

[sherman51]

thanks .. I'll try and sort that tomorrow .. the only thing is that there won't be any numbers comming in as it needs to be connected to the trading software, so not sure how that would work.
Thanks
Paul

[sherman51]

As per Jeff...

The thing is you would need to be subscribed at cost to the trading software to be able to reproduce the situation.
What I have found though is that there is nothing special about their spreadsheet, I have been able to connect a new blank sheet and the numbers load just the same, presumably they are fed via an API. If I overtype the incoming numbers they just revert back to the feed numbers .. does that give any clues as to the nature of them ?

Just to update where i am .. I put the code below into the sheet (worked from Sintek example) and find the following ...

ie if I type 24 into H9, then 24 will appear in AN9, then if I change H9 to 26, then AN9 will display 2 ..etc this works fine
But If I connect the sheet to the trading software and lets say H9 shows 24, nothing will appear in AN9, and nothing will happen when H9 changes ..
It seems that the numbers displayed on a "Connected" sheet arn't recognised ..

Thanks
Paul

Please Login or Register  to view this content.

[Sintek]

If I connect the sheet to the trading software and lets say H9 shows 24, nothing will appear in AN9, and nothing will happen when H9 changes ..

I still think a different event needs to be used...Not sure which one though...

If the cell is populated by code and undo function could not work...
Is the cell populated by a formula...Still need to find out how C3 gets its value from the API

[sherman51]

thanks,
the cells that recieve the constantly updated numbers are empty, no code refers to it or formula .. I have tried with a newly created blank sheet ..
cheers
Paul

[romperstomper]

How many cells do you need to monitor?

[sherman51]

up to 30 ..

[romperstomper]

I'd suggest trying the Calculate event of that sheet, or add an ActiveX textbox, link it to the first of the output cells, and then use its Change event to process all of the cells.