Thread
:
looping issue
View Single Post
#
10
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
looping issue
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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"thomas donino" wrote in message
...
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
"thomas donino" wrote:
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
"Joel" wrote:
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett