looping issue

[thomas donino]

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

 

[JP Ronse]

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

 

[Joel]

When deleting rows you have to step backward from last row to first row. If
you delete row 5 then what was row 6 becomes row 5 and you will skip
processing the orignal row 6 becasue the for loop went to the next row. See
code below. I simply changed the For statement. I also made the code more
readable.

You also have to add the bold for each case, otherwise, only one case will
get higlighted


Sub FormatCFTCFile()

wheatBoards = Array("WHEAT - CHICAGO BOARD OF TRADE", _
"CORN - CHICAGO BOARD OF TRADE", _
"U.S. TREASURY BONDS - CHICAGO BOARD OF TRADE")

' 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 = Finalrow To 2 Step -1
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"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "SOYBEAN MEAL - CHICAGO BOARD OF TRADE", _
"2-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", _
"SUGAR NO. 11 - ICE FUTURES U.S."

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "5-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", _
"10-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

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"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "COFFEE C - ICE FUTURES U.S.", _
"SILVER - COMMODITY EXCHANGE INC.", _
"COPPER-GRADE #1 - COMMODITY EXCHANGE INC.", _
"GOLD - COMMODITY EXCHANGE INC."

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE", _
"EURO FX - CHICAGO MERCANTILE EXCHANGE", _
"GASOLINE BLENDSTOCK (RBOB) - NEW YORK MERCANTILE EXCHANGE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

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"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "NASDAQ-100 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE", _
"NASDAQ-100 STOCK INDEX (MINI) - CHICAGO MERCANTILE EXCHANGE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

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

Case Else
Cells(x, 1).EntireRow.Delete
End Select
Next x
' delete all unnecessary columns
End Sub

 

[thomas donino]

I made the changes which all make sense but the loop is still continuous and
debugger said its breaking on the End Select at the bottom

 

[thomas donino]

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]

Hi Thomas,

Reply to group and insert file or send the file to me:
(e-mail address removed)

Wkr,

JP

 

[Don Guillett]

Bottoms up to delete.
Change "A", "C",etc to suit

Sub selectcasebold()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Select Case UCase(Cells(i, 1))
Case "A": x = 1

Case "C": x = 1

Case Else
Rows(i).Delete
End Select
If x = 1 Then Rows(i).Font.Bold = True
Next i
End Sub

 

[thomas donino]

Thank you, sent to your email

Hi Thomas,

Reply to group and insert file or send the file to me:
(e-mail address removed)

Wkr,

JP

 

[thomas donino]

Here is the current code with all comments and suggestions implemented. The
code does nothing now )-:

Sub FormatCFTCFile()

' figure out what row is the last row of data
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Finalcol = Cells(1, Columns.Count).End(xlLeft).Column
' loop through all rows from row 2, where data starts, to the final row but
starting in final row and working up
For x = Finalrow To 2 Step -1
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", _
"SOYBEAN MEAL - CHICAGO BOARD OF TRADE", "2-YEAR U.S. TREASURY
NOTES - CHICAGO BOARD OF TRADE", "SUGAR NO. 11 - ICE FUTURES U.S.", _
"5-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", "10-YEAR
U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", _
"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", "COFFEE C
- ICE FUTURES U.S.", "SILVER - COMMODITY EXCHANGE INC.", _
"COPPER-GRADE #1 - COMMODITY EXCHANGE INC.", "GOLD - COMMODITY
EXCHANGE INC.", "JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE", _
"EURO FX - CHICAGO MERCANTILE EXCHANGE", "GASOLINE BLENDSTOCK
(RBOB) - NEW YORK MERCANTILE EXCHANGE", _
"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",
"NASDAQ-100 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE", _
"NASDAQ-100 STOCK INDEX (MINI) - CHICAGO MERCANTILE EXCHANGE",
"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
Case Else
Cells(x, 1).EntireRow.Delete
End Select
Next x
' delete all unnecessary columns
End Sub


Here is sample row in comma delimited
WHEAT - CHICAGO BOARD OF TRADE,090811,8/11/2009,001602,CBT, 00 001,324507

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[JP Ronse]

Hi Thomas,

Just send a mail to you.

Wkr,

JP

Here is the current code with all comments and suggestions implemented.
The
code does nothing now )-:

Sub FormatCFTCFile()

' figure out what row is the last row of data
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Finalcol = Cells(1, Columns.Count).End(xlLeft).Column
' loop through all rows from row 2, where data starts, to the final row
but
starting in final row and working up
For x = Finalrow To 2 Step -1
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", _
"SOYBEAN MEAL - CHICAGO BOARD OF TRADE", "2-YEAR U.S. TREASURY
NOTES - CHICAGO BOARD OF TRADE", "SUGAR NO. 11 - ICE FUTURES U.S.", _
"5-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", "10-YEAR
U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", _
"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", "COFFEE
C
- ICE FUTURES U.S.", "SILVER - COMMODITY EXCHANGE INC.", _
"COPPER-GRADE #1 - COMMODITY EXCHANGE INC.", "GOLD - COMMODITY
EXCHANGE INC.", "JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE", _
"EURO FX - CHICAGO MERCANTILE EXCHANGE", "GASOLINE BLENDSTOCK
(RBOB) - NEW YORK MERCANTILE EXCHANGE", _
"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",
"NASDAQ-100 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE", _
"NASDAQ-100 STOCK INDEX (MINI) - CHICAGO MERCANTILE EXCHANGE",
"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
Case Else
Cells(x, 1).EntireRow.Delete
End Select
Next x
' delete all unnecessary columns
End Sub


Here is sample row in comma delimited
WHEAT - CHICAGO BOARD OF TRADE,090811,8/11/2009,001602,CBT, 00 001,324507