[SOLVED] Problem with data type- Stock (VBA, simple formulas)

Problem with data type- Stock (VBA, simple formulas)

I have a problem with stock data type, because i don't know how to remake it, that formulas and VBA code will be working e.g.:

I have data type: 11 bit studios S.A. (XWAR:11B) and i want to extract the ticker (in this case it is 11B), with the formula SEARCH(":", A11), and then i have the Error #ARG. In VBA it is the same situation:

Sub AAAA
If Instr(Cells(1,1), ":")>0 then
Msgbox Succes
End If
End Sub

If anyone would help me with this i will be greatfull

Solution

It is not clear what you are trying to do.

Since this is a rich data type, you will need to examine the .Text property of the Range object in order to examine the contents.
Even if you do that, your MsgBox will be blank since you have never defined your variable Succes.

On your worksheet, you would obtain the Ticker with the formula:

=A1.[Ticker Symbol]

In VBA, to have the Msgbox return something, you would use:

If InStr(Cells(1, 1).Text, ":") > 0 Then

But you will have to be certain that column A is not hidden or too small to show the text string.

By the way, with regard to VBA, it is good practice to always declare your variables. In the VBA GUI, you can enforce this by Tools/Options/Editor/Code Settings and selecting Require Variable Declaration. This will place OPTION EXPLICIT at the top of each inserted module (and you can also manually add this to modules created before you selected this option). This will be extremely useful in catching typos, variables of the incorrect type, etc.