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