OilEconomist
Active Member
- Joined
- Dec 26, 2016
- Messages
- 421
- Office Version
- 2019
- Platform
- 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:
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