| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping issue
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping issue
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping issue
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping issue
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping issue
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping issue
|
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping issue
|
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping issue
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
looping issue
Hi Thomas,
Just send a mail to you. Wkr, JP "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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looping Help Please | Excel Programming | |||
| Help with if and looping | Excel Programming | |||
| UDF Macro Looping Issue Erroring out | Excel Programming | |||
| Rows Group -looping issue | Excel Programming | |||
| Looping issue | Excel Programming | |||
Linear Mode
