ExcelBanter - View Single Post

thomas donino

Thank you, sent to your email

"JP Ronse" wrote:

Hi Thomas,

Reply to group and insert file or send the file to me:

Wkr,

JP

"thomas donino" wrote in message
...
I'm not sure how to attach an example file but am happy to do so if you
can
fill me in as to how to do it

"JP Ronse" wrote:

Hi Thomas,

It is difficult to test without sample data, whowever I would recomment
following:
1. Change the loop from "first to last" to "last to first": for x =
fnalrow
to 2 step -1
2. Your code will only set the font to bold for the last case condition.
You
have to add the font.bold instruction to each case condition.

If you don't mind to send a sample file with the code, Il will have a
look
into.

Wkr,

JP

"thomas donino" wrote in message
...
I have the following code module which is searching through a very large
file
to find only the data we want, which are the case statements. The
problem
is
it does bold the data if i only use 1 case statement and it doesn't
delete
the other rows. It is also a continuous loop for some reason.

Sub FormatCFTCFile()
' figure out what row is the last row of data
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
' loop through all rows from row 2 where data starts to the final row
For x = 2 To Finalrow
Select Case Cells(x, 1)
'check for commodity names that we want to keep the data from
Case "WHEAT - CHICAGO BOARD OF TRADE", "CORN - CHICAGO BOARD OF
TRADE",
"SOYBEANS - CHICAGO BOARD OF TRADE", "U.S. TREASURY BONDS - CHICAGO
BOARD
OF
TRADE"
Case "SOYBEAN MEAL - CHICAGO BOARD OF TRADE", "2-YEAR U.S. TREASURY
NOTES - CHICAGO BOARD OF TRADE", "SUGAR NO. 11 - ICE FUTURES U.S."
Case "5-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE",
"10-YEAR
U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE"
Case "NO. 2 HEATING OIL, N.Y. HARBOR - NEW YORK MERCANTILE
EXCHANGE",
"NATURAL GAS - NEW YORK MERCANTILE EXCHANGE", "CRUDE OIL, LIGHT SWEET -
NEW
YORK MERCANTILE EXCHANGE"
Case "COFFEE C - ICE FUTURES U.S.", "SILVER - COMMODITY EXCHANGE
INC.",
"COPPER-GRADE #1 - COMMODITY EXCHANGE INC.", "GOLD - COMMODITY EXCHANGE
INC."
Case "JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE", "EURO FX -
CHICAGO
MERCANTILE EXCHANGE", "GASOLINE BLENDSTOCK (RBOB) - NEW YORK MERCANTILE
EXCHANGE"
Case "DOW JONES INDUSTRIAL AVG- x $5 - CHICAGO BOARD OF TRADE",
"S&P
500 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE", "DOW JONES INDUSTRIAL
AVG-
x
$5 - CHICAGO BOARD OF TRADE"
Case "NASDAQ-100 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE",
"NASDAQ-100 STOCK INDEX (MINI) - CHICAGO MERCANTILE EXCHANGE"
Case "S&P GSCI COMMODITY INDEX - CHICAGO MERCANTILE EXCHANGE",
"E-MINI
S&P 400 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE"
' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True
'delete all rows of unnecessary data
Case Else
Cells(x, 1).EntireRow.Delete
End Select
Next x
' delete all unnecessary columns
End Sub

Thank you in advance