Difficulty Changing Data with a Hyphen "-"

[Tremolo]

I have a formula to change Financial products into a different format but it isn't working on data with a Hypon/-

Examples:
CME E-MINI RUSSELL 2000 INDEX JUN21
NASDAQ100 Micro JUN21
E-MICRO COMEX Gold JUN21

It will change the dates to CQG dates, remove spaces and change the Product e.g. to NASDAQ100 Micro to desired sybmol i.e "MNQ". However, for the products with "-" it doesn't seem to work. Please help, file and photo attached

[macropod]

There is no Find code in your macro for CME E-MINI RUSSELL 2000 INDEX JUN21. Hence no transformation will occur there.

Moreover, your code is horribly inefficient, with masses of unnecessary selections, duplicate Find/Replace opertions, etc.

Your existing code could be reduced to:

Sub TVMacro()
Application.ScreenUpdating = False
' TVMacro Macro
'
'Headings
Range("A1").Value = "Date"
Range("B1").Value = "Time"
Range("C1").Value = "Symbol"


'Column A into American Date Format
Columns("A:A").NumberFormat = "mm/dd/yy"
    
'Column B into Time Format
Columns("B:B").NumberFormat = "[$-x-systime]h:mm:ss am/pm"


With Columns("C:C")
  'Product Change
  .Replace What:="EURO/GBP", Replacement:="RP", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="FTSE", Replacement:="Z", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Light Sweet Crude Oil (Phy)", Replacement:="CL", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Corn", Replacement:="ZC", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="ICE Brent Crude", Replacement:="BRN", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Mini-S&P500", Replacement:="ES", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Mini-Nasdaq100", Replacement:="NQ", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="LR", Replacement:="R", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="LL", Replacement:="L", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="AUD", Replacement:="6A", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="GBP", Replacement:="BP", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="CAD", Replacement:="6C", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="EURO-FX", Replacement:="EC", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="NZD", Replacement:="6N", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="AUD/JPY", Replacement:="AJY", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="LEAN HOG", Replacement:="HE", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Natural Gas(Phy)", Replacement:="NG", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="LIVE CATTLE", Replacement:="LE", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Soybeans", Replacement:="ZS", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="SoybeanMeal", Replacement:="ZM", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="SoybeanOil", Replacement:="ZL", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Wheat", Replacement:="ZW", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="NYBOT Coffee C", Replacement:="KC", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="NYBOT Cotton No 2", Replacement:="CT", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="COFFEE 409", Replacement:="RC", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="COMEX Silver", Replacement:="SI", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="ICE ECX CFI Futures", Replacement:="ECF", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="COMEX Copper", Replacement:="HG", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="S&P500Micro", Replacement:="MES", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="CME Bitcoin", Replacement:="BTC", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="FEXD", Replacement:="FEXD", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Natural Gas (Phy)", Replacement:="NG", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="NASDAQ100 Micro", Replacement:="MNQ", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="E-MICRO COMEX Gold JUN21", Replacement:="RTY JUN21", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="E-MICRO COMEX Gold", Replacement:="MGC", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="COMEX Gold", Replacement:="GC", LookAt:=xlPart, SearchOrder:=xlByRows

  'Dates into TVUE Format from Stellar Format
  .Replace What:="Jan2", Replacement:="F", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Feb2", Replacement:="G", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Mar2", Replacement:="H", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Apr2", Replacement:="J", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="May2", Replacement:="K", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Jun2", Replacement:="M", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Jul2", Replacement:="N", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Aug2", Replacement:="Q", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Sep2", Replacement:="U", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Oct2", Replacement:="V", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Nov2", Replacement:="X", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="Dec2", Replacement:="Z", LookAt:=xlPart, SearchOrder:=xlByRows
End With


'Remove Spaces
Range("C2:C1400").Replace " ", ""


With Columns("F:F")
  'Removing the "-" in Crops/Bonds
  .Replace What:="-6", Replacement:=".75", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="-4", Replacement:=".5", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="-2", Replacement:=".25", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="-0", Replacement:=".0", LookAt:=xlPart, SearchOrder:=xlByRows
End With


Application.ScreenUpdating = True
End Sub

[Tremolo]

Honestly, not much of a coder, recorded the macro and then copy and paste job. Should've used MGC as an example, either way it can't convert hyphons into desired outcome. Any ideas? Thanks for your help

[macropod]

The problem has nothing to do with the hyphen, as such. Rather, it's caused by having:

  .Replace What:="COMEX Gold", Replacement:="GC", LookAt:=xlPart, SearchOrder:=xlByRows

before:

 .Replace What:="E-MICRO COMEX Gold", Replacement:="MGC", LookAt:=xlPart, SearchOrder:=xlByRows

and

  .Replace What:="E-MICRO COMEX Gold JUN21", Replacement:="RTY", LookAt:=xlPart, SearchOrder:=xlByRows.

What all this means is that your Find/Replace operations are failing because you're replacing 'COMEX Gold' in the last two with 'GC' before you look for the last two expressions - which no longer exist - and, even if that were not the case, the data the last expression was looking for would already have become 'MGC JUN21', so it, too, would never be found.

You need to ensure any potential sub-string Find/Replace operations occur after you've processed any longer strings that will be affected. See the revised code, which now has:

  .Replace What:="E-MICRO COMEX Gold JUN21", Replacement:="RTY JUN21", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="E-MICRO COMEX Gold", Replacement:="MGC", LookAt:=xlPart, SearchOrder:=xlByRows
  .Replace What:="COMEX Gold", Replacement:="GC", LookAt:=xlPart, SearchOrder:=xlByRows

[Paul_Hossler]

You could put all of those 'From-To' pairs into a worksheet and make it sort of a mini-database, and run the macro off of that

It's be easier to maintain