Confilicting info in a value??

rkcim

Board Regular
Joined
Nov 19, 2003
Messages
87
I have a cell that is formated as currency. Every once in a while an ^ arrow gets placed in front, due to data coming from a website. Since any functions can't use this data the arrow has to be removed. Here is an example:

On Sheet3(Web Query data gets updated)
C44
^$5.23

On Sheet1 B10 is referenced to C44
#VALUE!

Here are the two different ways the data is refreshed:
1. Each time I click a button on Sheet1 the data on Sheet3 is refereshed therefore refreshing any cells on Sheet1 that are referenced to cells on Sheet3.

2. Each time I click on a stock name in a combobox, which is on Sheet3, a new stock is added on Sheet1 along with its data from Sheet3. (A stock name will only appear in the combobox only if within the list of stock names on Sheet3 are not on Sheet1)

The quetion is how can I prevent the up ^ arrow from showing up in the data, while noting how the data is refreshed?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. Windows
The following should take care of item #1. Unsure of #2...

Right click on the sheet tab name at the bottom of the page and select "View Code". Paste this code in the resulting box:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error Resume Next
    Cells.Replace What:="^", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False
    On Error GoTo 0

End Sub

This should automatically look for and replace any "^" any time a cell is selected.
 
Upvote 0

Forum statistics

Threads
1,186,908
Messages
5,960,524
Members
438,481
Latest member
KBChristensen

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
Top