Is there a better way to do this

[vavs]

I import a csv file into Excel. I use the file to create a dashboard for executive management. We recently picked up a customer that ships in metric weights as opposed to pounds. For this customer, I need to change the weight field in the Excel spreadsheet to pounds. I wrote a macro that runs after the import is done. The macro looks like this:

For i to LR
If Customer# = MB then
Weight=Weight*2.2045
Else
Next
End if

The variable i is the row number initialized to row 2
The variable LR is the last row which I calculate prior to starting the for loop
The weight is actual shown as cells(5,i).value and customer# is configured the same way.

The problem is that there are 25000 records in the spreadsheet and it is taking over 10 minutes to sort through them all.

Due to a constraint in an archaic ERP system, there is no way to convert the information prior to import. The system does not use any qualifiers for weight, it is simply a number(yes I know that it is stupid, but I have to deal with it for about 6 more months and then it is gone).

I need this conversion for a pivot table, summary spreadsheet and graphs.

 

[Andrew Poulsom]

It's best to post actual code rather than pseudo code. Have you tried setting calculation to Manual before your loop?

 

[Greg Truby]

Try this:

• Two columns after your last column or two rows after your last row, find a blank cell and enter 2.204623
• Then take your data block and use autofilter to show only that customer's records.
• Go back to your cell that contains 2.204623 and copy the cell.
• Go back to your filtered data and select the weight cells.
• Hit Ctrl+G (or F5 key) and click the Special... button. The select Visible Cells Only
• Do a PasteSpecial and pick Divide as the operation (you can also select the Values option if you don't want this to impact the weight cells' formatting).
• Remove autofilter
• Clear your "2.204623" cell.

Once you get the hang of doing this by hand, you might then try using the macro recorder to develop a macro to automate the process. If doing it in a macro, the "find a blank cell and put 2.204623 in it" bit would probably be quicker and easier to code as "create a new worksheet and put 2.203623 into cell A1", and then kill the temp WS when done.

 

[vavs]

I modified Greg's response to get this to work quickly. I added a column with the following formula: =if($B2='MERBE',$E2*2.2045,$E2). This is in column G. I copied it down. There were about 50K records. I changed my other formulas to look at this column as opposed to the original E column. This solved my problem. Thanks for your help.

 

[Greg Truby]

Using an approach like that - next time consider making the first arg inside the IF something like ISNUMBER(MATCH(customer ID, list of ID's,0)). That way if you later get another customer that is also squirrely, all you need to due is add them to your list of squirrely customer ID's and you done. :wink:

 

[BrandonWLH]

Application.ScreenUpdating=False may speed it up for you if you don't already have that at the start of your code.

Just be sure to Application.ScreenUpdating=true at the end or before any exit sub/end sub