VBA Code to Find Words with $ at The Beginning and then paste in another sheet

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance for your assistance as I will give feedback regardless whether it works or not.

Apologies if the requested or similar code exists as I have searched the internet and this forum, but have not been successful.

I would like to search an existing sheet for all the words/tickers that begin with a $ and return the entire word less the $, do a different sheet. So for example,

Cell A3: $SPY and $QQQ are going up tomorrow.
Cell A4: $VIX and $MRNA will be on a downward trend.

I would like the following posted in the same workbook in a sheet called "Tickers"
Cell A2: SPY
Cell A3: QQQ
Cell A4: VIX
Cell A5: MRNA

Here is the start of my code:


VBA Code:
Sub GetTicker()

'________________________________________________________________________________________________________
'Turn off alerts, screen updates, and automatic calculation
        'Turn off Display Alerts
            Application.DisplayAlerts = False

        'Turn off Screen Update
            Application.ScreenUpdating = False

        'Turn off Automatic Calculations
            Application.Calculation = xlManual


'________________________________________________________________________________________________________
'Dimensioning
    Dim LastRow As Long
    
    Dim Ticker As String






'________________________________________________________________________________________________________
'Find the LastRow
    Sheets("Sheet1").Activate
        LastRow1 = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, _
            LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
    End With
    'MsgBox LastRow


'________________________________________________________________________________________________________
'Loop through and find all the ones with the $ at the beginning of the words
    
    For i = 1 To LastRow1
        Sheets("Sheet1").Activate
            
            'This is the code that is missing
            'Find all values in the sheet that begins with a $ and is followed by letters and then just store the_
                'letters in string "Ticker"
   
                
    
    
        Sheets("Sheet2").Activate
            'Find the last row and past the ticker into it.
                LastRow2 = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, _
                    LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
                    
                Cells("A" & LastRow2).Value = Ticker
    
    Next i

'________________________________________________________________________________________________________
'Turn on alerts, screen updates, and calculate

        'Turn On Display Alerts
            Application.DisplayAlerts = True

        'Turn on Screen Update
            Application.ScreenUpdating = True

        'Turn off Automatic Calculations
            Calculate

'Place the curser in cell



End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Ticker symbols are upper or lower case,
The alteration to my code for upper or lower case values is just
Rich (BB code):
RX.Pattern = " (" & myChars & ") *[A-Za-z].+?\b"


BTW Rick, you may have overlooked/forgotten this requirement with your code.
(1) when a number had a $ in front of it, it copied the number. My objective is to copy just items with letters.

.. and I don't know if it is possible with the OP's data but your code would also pick up any 'word' with any of the symbols anywhere in it, not only as a prefix as per
tickers that begin with a $
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Checking the red sample & results. All other examples were upper case letters. Is this a typo with the sample data or do we also have to consider lower case letters after these symboles?

Assuming all data to be extracted is actually upper case, then try this (processes all columns)

VBA Code:
Sub Get_Tickers_v3()
  Dim RX As Object, m As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
 
  Const myChars As String = "\$|\@|\^|\#"
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = " (" & myChars & ") *[A-Z].+?\b"
  ReDim b(1 To Rows.Count, 1 To 1)
  a = Sheets("Sheet1").UsedRange
  For j = 1 To UBound(a, 2)
    For i = 1 To UBound(a)
      For Each m In RX.Execute(" " & a(i, j))
        k = k + 1
        b(k, 1) = LTrim(Mid(m, 3))
      Next m
    Next i
  Next j
  Sheets("Tickers").Range("A7").Resize(k).Value = b
End Sub
Hi @Peter_SSs thanks for your response. This works. I will use this if Rick does not provide a modification to his code. Basically I'm using both your codes.

The lower case was a mistake. I think when I placed a @ before NFLX, for some reason it made a lower case. For now, I am just using upper case letters.

As in one sense I want all the tickers in one page whether it has a $, @, ^, or #. so I use your code.

I then put the tickers in separate tabs respectively based on $, @, ^, or # and I use a modification of Rick's code.

Question for you @Peter_SSs, what step are you eliminating the $, @, ^, and #?. If I wanted to take those symbols and place them in the column next to it (Column B) without the tickers. If it's in your steps somewhere where you eliminate it (my question) I can just use that before you eliminate it. Thanks!
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
For one column where there Ticker symbols are upper or lower case, give this a try...
VBA Code:
Sub Tickers()
  Dim Txt As String, V As Variant, Arr As Variant
  With Sheets("SheetData")
    Txt = Join(Application.Transpose(.Range("A3", .Cells(Rows.Count, "A").End(xlUp))))
  End With
  For Each V In Array("$", "@", "^")
    Txt = Replace(Txt, V, "#")
    Txt = Replace(Replace(Txt, ".", ""), ",", "")
  Next
  Arr = Filter(Split(UCase(Replace(Txt, Chr(160), " "))), "#")
  With Sheets("Tickers").Range("A7").Resize(UBound(Arr) + 1)
    .Value = Application.Transpose(Arr)
    .Replace "#", "", xlPart, , False, , False, False
  End With
End Sub
Thanks @Rick Rothstein as for now all my tickers are lower case, but with Stock Tickers the person who gives me the data may accidentally put them in lower case or I've also heard that at times a ticker might be lower case.
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
The alteration to my code for upper or lower case values is just
Rich (BB code):
RX.Pattern = " (" & myChars & ") *[A-Za-z].+?\b"


BTW Rick, you may have overlooked/forgotten this requirement with your code.


.. and I don't know if it is possible with the OP's data but your code would also pick up any 'word' with any of the symbols anywhere in it, not only as a prefix as per
Thanks @Peter_SSs and @Rick Rothstein. I have been using a work around for if a number is input. I just use an eliminate/delete row.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The lower case was a mistake. I think when I placed a @ before NFLX, for some reason it made a lower case. For now, I am just using upper case letters.
I think Excel will have interpreted text containing an @ symbol to be an email address and formatted it as lower case. If you use the altered 'Pattern' line I gave in post #31 my code will pick up either case.


I then put the tickers in separate tabs respectively based on $, @, ^, or # and I use a modification of Rick's code.

Question for you @Peter_SSs, what step are you eliminating the $, @, ^, and #?. If I wanted to take those symbols and place them in the column next to it (Column B) without the tickers. If it's in your steps somewhere where you eliminate it (my question) I can just use that before you eliminate it. Thanks!
My code could easily put the prefix ($,@,^,#) beside the ticker for you and/or automatically transfer the tickers to the relevant tabs with or without the prefix.
If you want to pursue that, can you clarify ..
  • Do you actually want all the ticker on the 'Tickers' tab anyway or are you only doing that as an intermediate step to getting them all on their individual tabs?
  • If you do want them on the 'Tickers' tab, would it be okay to have the prefix in column A and ticker in column B (easier) or does it need to be the other way around?
  • What are the names of the individual tabs for the splitting of the tickers?
  • Is there anything already on these individual ticker tabs?
  • If so does it all need to be retained and where do the new tickers go?
  • On the individual ticker sheets should the prefix be removed from the ticker or not?
The more clearly you can show/explain what you have and what you want, the easier the coding would be.


I have been using a work around for if a number is input.
With my code there would be no need for such a work around as it will not pick up anything if the $ (or any other prefix) is followed by numbers.
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
I think Excel will have interpreted text containing an @ symbol to be an email address and formatted it as lower case. If you use the altered 'Pattern' line I gave in post #31 my code will pick up either case.



My code could easily put the prefix ($,@,^,#) beside the ticker for you and/or automatically transfer the tickers to the relevant tabs with or without the prefix.
If you want to pursue that, can you clarify ..
  • Do you actually want all the ticker on the 'Tickers' tab anyway or are you only doing that as an intermediate step to getting them all on their individual tabs?
  • If you do want them on the 'Tickers' tab, would it be okay to have the prefix in column A and ticker in column B (easier) or does it need to be the other way around?
  • What are the names of the individual tabs for the splitting of the tickers?
  • Is there anything already on these individual ticker tabs?
  • If so does it all need to be retained and where do the new tickers go?
  • On the individual ticker sheets should the prefix be removed from the ticker or not?
The more clearly you can show/explain what you have and what you want, the easier the coding would be.



With my code there would be no need for such a work around as it will not pick up anything if the $ (or any other prefix) is followed by numbers.
Thanks @Peter_SSs. Point of clarification as I renamed the “Tickers” tab to “Main”

Q: My code could easily put the prefix ($,@,^,#) beside the ticker for you and/or automatically transfer the tickers to the relevant tabs with or without the prefix.
A: So I have four tabs – “All”, “Main”, “Scalps”, “Backburners”, and “Sympathy”. When it inserts the Tickers in Column C of each tab, I would like to place the type of play or basically the tab name in column E. For all the tabs except “All” it would be easy since it’s just the tab name. On these four tabs (except the “All” tab) it’s not even really needed, but I’m also putting it on the four other tabs for symmetry (so all five tabs have the same columns). The only one where I really need it is the “All” tab because each of the tickers placed in that tab is in one of the four categories – (1) Main (2) Scalp (3) Backburner (4) Sympathy. I left of the “s” off on some of these (difference between tab name and the aforementioned)

If you want to pursue that, can you clarify ..
Q: Do you actually want all the ticker on the 'Tickers' tab anyway or are you only doing that as an intermediate step to getting them all on their individual tabs?
A: As I indicated in the very beginning of this message, I renamed the “Tickers” tab to “Main”. I want to do the following:
• “All” Tab: Has all the tickers in the order as the appear in the sheet whether it is “$”, “@”, “^”, or “#”.
• “Main” Tab: contains tickers with “$” in front of it
• “Scalps” Tab: contains tickers with “@” in front of it
• “Backburners” Tab: contains tickers with “^” in front of it
• “Sympathy” Tab: contains tickers with “#” in front of it

Q: If you do want them on the 'Tickers' tab, would it be okay to have the prefix in column A and ticker in column B (easier) or does it need to be the other way around?
R: I think I answered the first part of this question in my previous response. If I could just place the type of ticker in column E.

Q: What are the names of the individual tabs for the splitting of the tickers?
R: I assume you mean what tabs have the data where I want to extract the tickers. They are Watchlists and they are between the two tabs: “WL.START” and “WL.END”.

Q: Is there anything already on these individual ticker tabs?
R: Yes, and that’s why in the updated code I have attached, I find the lastrow and paste the ticker one row below it. I don’t show it here, but I also past the date in column B which is the Watchlist name. I clarify the logic in my code in the last section of this write up.

Q: If so does it all need to be retained and where do the new tickers go?
R: Yes, it does get retained on each type of play tab. The new tickers just go below the old ones. I does not matter when I paste the new tickers if they were from a previous watchlist as I'm tracking how many times they were on the list.

Q: On the individual ticker sheets should the prefix be removed from the ticker or not?
R: Yes in the end I would like the prefix (“$”, “@”, “^”, or “#”) to be removed, but the reason I was asking you about where do you remove it in your code is so that I could place the type of ticker in Column E.

Q: The more clearly you can show/explain what you have and what you want, the easier the coding would be.
R: Yes, apologies for the confusion as if I tried to describe all the code, it would be a massive process for you guys and also it changes as I encounter new needs and unexpected issues. What I am doing below is placing the main steps and then have placed most of the code (the area where I need help) in the section below.

CODE LOGIC:
(1) Identify objective of the code
(2) Identify the steps in the code
(3) Turn off alerts, screen updates, and automatic calculation (standard I use for all my procedures)
(4) Dimensioning
• I try to dimension all in one section to be able to keep up with if dim has already been used.
(5) Sort all the Watchlists so that the data is pulled in order of the oldest date
(6) Loop through the Play tabs
• These are the tabs where the data will get pasted.
• I do this so I can get the sheet name and based on that the logic is executed.
(7) Loop through the Watchlists
• This has the Playname stored so it will go through each Watchlist and paste in the respective tab based on logic.
• It first stores the Play tab’s name and then it activates the Watchlist tab
(8) This is where it sets how to transfer the data
• I used what you had and modified it for Step 001. The issue is that it only executes it for one of the Plays tabs. The first one in the sequence. As you can see, I set the code:

VBA Code:
Const myCharsA As String = "\$|\@|\^|\#"

to four additional versions so that it will get the individual associated characters:

For the “Main” tab
VBA Code:
Const myCharsM As String = "\$"

For the “Scalps” tab
VBA Code:
Const myCharsS As String = "\@"

For the “Backburners” tab
VBA Code:
Const myCharsBB As String = "\^"

For the “Sympathy” tab
VBA Code:
Const myCharsSS As String = "\#"

At the end of this code, you will see the end of the loop for the Watchlist Tabs (Next j) and go back to Step 07.002
Then you will see the end of the code for the Plays tabs (Next i) and go back to Step 06.002

(9) Place the cursor back in the last Plays tab in the last Ticker cell

(10) Turn on alerts, screen updates, and calculate
A last step I put in all my VBA Macros

CODE IS AS FOLLOWS

VBA Code:
Sub Tickers_Obtain3()
'*********************************************************************************************************
'01. Objective/Overview
    
    'This will obtain the tickers from the Watch List tabs and place it in the following respective tabs:
        '(1) "All"
        '(2) "Main"
        '(3) "Scalps"
        '(4) "Backburners"
        '(5) "Sympathy"


'________________________________________________________________________________________________________
'02. Steps

    '01. Settings
    '02. Dimensioning
    '03. Sort sheets
    '04. Loop through Watch Lists - Start
    '05. Obtain tickers and paste into tab
    '06. Replace characters that do not belong
    '07. Input the date
    '08. Remove the numbers and spaces
    '09. Number the tickers
    '10. Exit if no date?
    '11. Make links
    '12. Format
    



'________________________________________________________________________________________________________
'03. Turn off alerts, screen updates, and automatic calculation
    'Turn off Display Alerts
        Application.DisplayAlerts = False

    'Turn off Screen Update
        Application.ScreenUpdating = False

    'Turn off Automatic Calculations
        Application.Calculation = xlManual



'________________________________________________________________________________________________________
'04. Dimensioning
    
    'Dim long
        Dim SheetStartPlays As Long
        Dim SheetEndPlays As Long
        
        Dim SheetStartWL As Long
        Dim SheetEndWL As Long
        
        Dim i As Long
        Dim j As Long
        Dim k As Long
        
        Dim p As Long
        Dim q As Long
        Dim r As Long
        
        Dim LastRow As Long
        Dim LastRowCol As Long
        Dim LastRowColB As Long
        Dim LastRowColC As Long
            
    
    'Dim Strings
        Dim Date_WL_Raw As String
        Dim Date_WL_Link As String
        
        Dim SheetName As String
        Dim SheetNameWL As String
        Dim SheetNamePlays As String
        
        Dim Ticker As String
        
        Dim Symbol As String
        

    'Dim Range
        Dim SearchRange As Range
        Dim FindRow As Range


    'Dim Objects
        Dim RX As Object
        Dim m As Object
        
        
    'Dim Variants
        Dim a As Variant
        Dim b As Variant

        


'________________________________________________________________________________________________________
'05. Sort the Watch Lists
    
    '001. Activate the tab
        Worksheets("WL.START").Activate
    
    
    '002. Set the sheets to loop within
        SheetStartWL = Worksheets("WL.START").Index
        SheetEndWL = Worksheets("WL.END").Index
                        
                        
    '003. Loop to sort the sheet in decending order so they are place in the tabs accordingly
            For i = SheetStartWL + 1 To SheetEndWL - 1
            
                For j = SheetStartWL + 1 To SheetEndWL - 2
                
                    If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                        Sheets(j).Move After:=Sheets(j + 1)
                        
                    End If
                    
                Next j
                
            Next i
        
        
'________________________________________________________________________________________________________
'06. Loop through the tabs for each type of play and obtain tickers
     'It will go through each play tab and then go through all the watchlists and place the tickers in _
     each tab one at a time. For example, it will start with the "Tickers" tab by activating the first _
     Watchlist and the executing all the steps for the "Tickers" tab and then each Watchlist thereafter _
     till all are complete. It then goes to the next play tab "Scalps" and does the same thing, and then _
     "Backburners" and then finally the "Sympthay" tab.
    
    
    '001. Set the start and end sheets
        SheetStartPlays = Worksheets("PLAYS.START").Index
        SheetEndPlays = Worksheets("PLAYS.END").Index
        
    
    '002. Loop through the play tabs
        For i = SheetStartPlays + 1 To SheetEndPlays - 1
            Worksheets(i).Activate
            SheetNamePlays = ActiveSheet.Name
        


'________________________________________________________________________________________________________
'07. Start the loop to go through the Watch Lists Tabs and then will execute each type of play including _
    the "All" plays tab
    
    '001. Set the Watchlist Start and End Worksheets
        SheetStartWL = Worksheets("WL.START").Index
        SheetEndWL = Worksheets("WL.END").Index

    
    '002. Start the Loop
        For j = SheetStartWL + 1 To SheetEndWL - 1
            Worksheets(j).Activate
            SheetNameWL = ActiveSheet.Name
        
            Date_WL_Raw = Worksheets(j).Name
            Date_WL = Left(Date_WL_Raw, 4) & "/" & Right(Left(Date_WL_Raw, 7), 2) & "/" & Right(Date_WL_Raw, 2)



'________________________________________________________________________________________________________
'08.Transfer the data
        
    
    '001. Setting the constant code
        Const myCharsA As String = "\$|\@|\^|\#"
        Const myCharsM As String = "\$"
        Const myCharsS As String = "\@"
        Const myCharsBB As String = "\^"
        Const myCharsSS As String = "\#"
    
    '002. Action Code
        'Set "RX" & "RX.Global"
            Set RX = CreateObject("VBScript.RegExp")
            RX.Global = True
        
        
        'Setting up the "RX.Pattern"
            If SheetNamePlays = "All" Then
                RX.Pattern = " (" & myCharsA & ") *[A-Z].+?\b"
            
            ElseIf SheetNamePlays = "Main" Then
                RX.Pattern = " (" & myCharsM & ") *[A-Z].+?\b"
                
            ElseIf SheetNamePlays = "Scalps" Then
                RX.Pattern = " (" & myCharsS & ") *[A-Z].+?\b"
    
            ElseIf SheetNamePlays = "Backburners" Then
                RX.Pattern = " (" & myCharsBB & ") *[A-Z].+?\b"
    
            ElseIf SheetNamePlays = "Sympathy" Then
                RX.Pattern = " (" & myCharsSS & ") *[A-Z].+?\b"
        
            End If
    
    
        'Remainder of code
            ReDim b(1 To Rows.Count, 1 To 1)
            a = Sheets(SheetNameWL).UsedRange
        
            For q = 1 To UBound(a, 2)
                For p = 1 To UBound(a)
                    For Each m In RX.Execute(" " & a(p, q))
                        r = r + 1
                        b(r, 1) = LTrim(Mid(m, 3))
                    Next m
                Next p
            Next q
  

            
    '003. Get the last row in the tab for pasting
            Worksheets(SheetNamePlays).Activate
            ColNum = 3
            LastRowCol = Cells(Rows.Count, ColNum).End(xlUp).Row
            
            
            If LastRowCol < 7 Then
                LastRowCol = 7
            Else
                LastRowCol = LastRowCol + 1
            End If


    '004. Put the tickers into the tab
            Sheets(SheetNamePlays).Range("C" & LastRowCol).Resize(r).Value = b
            

Next j

Next i

'________________________________________________________________________________________________________
'09. Select the cell to have the curser in it
        
    'Activate the "Main.Plays" tab
        Sheets("All").Activate
        
        
    'Find the last row
        SheetName = "All"
        LastRow = LastRowF(SheetName)
            
        If LastRow < 7 Then
            LastRow = 7
        End If
        
    
    'Place the cursor in the cell
            Sheets("All").Range("C" & LastRow).Select



'________________________________________________________________________________________________________
'10. Turn on alerts, screen updates, and calculate

        'Turn On Display Alerts
            Application.DisplayAlerts = True

        'Turn on Screen Update
            Application.ScreenUpdating = True

        'Turn off Automatic Calculations
            Calculate

End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for the additional info. There is a bit much in it to follow exactly, especially since I don't have a sample file or the full code. In any case, to analyse all of that might be a bit beyond expectations for a free public forum like this. However, see if you can make something from the code below.

This processes all cells in all sheets between' WL.Start' and 'WL.End'
It does not require to split the RegExp patterns up into individual patterns for each of $,@,^,#
I'm not sure I have understood about inserting the tab names for the last 4 sheets (for $,@,^,#) but see if it is on the right track.
I have assumed less than 65,536 tickers to be processed altogether in a single run of the code.

VBA Code:
Sub Get_Tickers_v4()
  Dim RX As Object, m As Object
  Dim a As Variant, b As Variant, tmp As Variant
  Dim i As Long, j As Long, k As Long, shIdx As Long
  
  
  Const myChars As String = "\$|\@|\^|\#"
  Const TickerSheets As String = "Main|Scalps|Backburners|Sympathy"
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = " (" & myChars & ") *[A-Za-z].+?\b"
  ReDim b(1 To Rows.Count)
  For shIdx = Sheets("WL.Start").Index + 1 To Sheets("WL.End").Index - 1
    a = Sheets(shIdx).UsedRange.Value
    For j = 1 To UBound(a, 2)
      For i = 1 To UBound(a)
        For Each m In RX.Execute(" " & a(i, j))
          k = k + 1
          b(k) = Mid(m, 2)
        Next m
      Next i
    Next j
  Next shIdx
  With Sheets("All").Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(k)
    .Value = Application.Transpose(b)
    .TextToColumns Destination:=.Offset(, 2), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, 9))
    .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
  End With
  For j = 1 To 4
    tmp = Filter(b, Mid(myChars, (j * 3 - 1), 1))
    If UBound(tmp) > -1 Then
      With Sheets(Split(TickerSheets, "|")(j - 1))
        With .Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(tmp) + 1)
          .Value = Application.Transpose(tmp)
          .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
          .Offset(, 2).Value = .Parent.Name
        End With
      End With
    End If
  Next j
End Sub
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the additional info. There is a bit much in it to follow exactly, especially since I don't have a sample file or the full code. In any case, to analyse all of that might be a bit beyond expectations for a free public forum like this. However, see if you can make something from the code below.

This processes all cells in all sheets between' WL.Start' and 'WL.End'
It does not require to split the RegExp patterns up into individual patterns for each of $,@,^,#
I'm not sure I have understood about inserting the tab names for the last 4 sheets (for $,@,^,#) but see if it is on the right track.
I have assumed less than 65,536 tickers to be processed altogether in a single run of the code.

VBA Code:
Sub Get_Tickers_v4()
  Dim RX As Object, m As Object
  Dim a As Variant, b As Variant, tmp As Variant
  Dim i As Long, j As Long, k As Long, shIdx As Long
 
 
  Const myChars As String = "\$|\@|\^|\#"
  Const TickerSheets As String = "Main|Scalps|Backburners|Sympathy"
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = " (" & myChars & ") *[A-Za-z].+?\b"
  ReDim b(1 To Rows.Count)
  For shIdx = Sheets("WL.Start").Index + 1 To Sheets("WL.End").Index - 1
    a = Sheets(shIdx).UsedRange.Value
    For j = 1 To UBound(a, 2)
      For i = 1 To UBound(a)
        For Each m In RX.Execute(" " & a(i, j))
          k = k + 1
          b(k) = Mid(m, 2)
        Next m
      Next i
    Next j
  Next shIdx
  With Sheets("All").Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(k)
    .Value = Application.Transpose(b)
    .TextToColumns Destination:=.Offset(, 2), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, 9))
    .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
  End With
  For j = 1 To 4
    tmp = Filter(b, Mid(myChars, (j * 3 - 1), 1))
    If UBound(tmp) > -1 Then
      With Sheets(Split(TickerSheets, "|")(j - 1))
        With .Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(tmp) + 1)
          .Value = Application.Transpose(tmp)
          .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
          .Offset(, 2).Value = .Parent.Name
        End With
      End With
    End If
  Next j
End Sub
Hi @Peter_SSs and thanks for this as it works.

I understand it was a bit much and that's why I was trying to use the workaround. Also I asked what step you were eliminating the $, @, ^, and #?. If I wanted to take those symbols and place them in the column next to it (Column B) without the tickers." I thought you were doing it with a loop and I could easily insert the Tab Name also in Column C of the Plays tabs.

With that being said, are you storing all the tickers from Watch List sheets at once and then transferring to the tabs? The reason I ask that is because I am trying to put the tab names where the tickers come from in Column C.

Once again thanks and I apologize if it seems that I'm making this a bigger request every time as I'm really not. I'm just leaving things out I think I can do myself. I thought if I could see how you were storing the ticker symbols ($, @, ^, #) and then just add the Watchlist name in Tab C.
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Apologies as at the end
Hi @Peter_SSs and thanks for this as it works.

I understand it was a bit much and that's why I was trying to use the workaround. Also I asked what step you were eliminating the $, @, ^, and #?. If I wanted to take those symbols and place them in the column next to it (Column B) without the tickers." I thought you were doing it with a loop and I could easily insert the Tab Name also in Column C of the Plays tabs.

With that being said, are you storing all the tickers from Watch List sheets at once and then transferring to the tabs? The reason I ask that is because I am trying to put the tab names where the tickers come from in Column C.

Once again thanks and I apologize if it seems that I'm making this a bigger request every time as I'm really not. I'm just leaving things out I think I can do myself. I thought if I could see how you were storing the ticker symbols ($, @, ^, #) and then just add the Watchlist name in Tab C.
@Peter_SSs Apologies I meant to add the Watchlist Tab name in Column B in the type of play.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
If I wanted to take those symbols and place them in the column next to it (Column B) without the tickers."

I meant to add the Watchlist Tab name in Column B in the type of play

I am trying to put the tab names where the tickers come from in Column C.
I'm finding those three statements above confusing.
Hopefully this will clarify. Just suppose there is only one Watch List sheet & it is called 'Sheet1' and contains only this data.
OilEconomist_1.xlsm
A
1
2
3#SPY and $QQQ are going up tomorrow.
Sheet1

What exactly would go on sheet 'All' and in what columns?
What exactly would go on sheets 'Main' and 'Backburners' and in what columns?


With that being said, are you storing all the tickers from Watch List sheets at once and then transferring to the tabs?
Yes I am.
The code looks for a space (I add a space at the start of the cell text for consistency) followed by one of the special symbols followed by a letter followed by any non-space characters to the end of the 'word'. So in the above example we would find " #SPY" and " $QQQ". These are each temporarily stored in the variable 'm'. They are then transferred into the array 'b' but at that point the space is removed from the beginning by using mid(m, 2)
So b(1) = "#SPY" and b(2) = "$QQQ"

These are placed in the various sheets and the symbol is stripped off and moved by using Text to Columns.

Having said all that, I may well choose a slight variation of this process once the answers to my questions above are resolved.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,407
Messages
5,624,587
Members
416,036
Latest member
eloisa manzanarez

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