Hi Bill,

What can I say, but Thank You! Thank You! Thank You! It took only seconds
to run!

Yes there are a lot of VLOOKUPs running against that sheet. I didn't even
think of that, but I just starting doing Excel Programming about 1.5 months
ago so I am still a newbie.

Reading the threads on this forum is really helping me to expand my
knowledge and I am so glad I found it.

Thanks again!
Sherry


"Bill Manville" wrote in message
...
Sherry wrote:
If I copy the results into another sheet and then run
the code it takes 2.5 minutes, but in the original sheet it takes over 3
hours. Anyone got any ideas on this?


Surprising. Are there formulas referencing this query table, e.g. doing
VLOOKUPs? If so, setting Application.Calculation = xlManual would be very
beneficial. You can set Application.Calculation = xlAutomatic again at
the end.

You could get faster performance by using the Paste Special / Values /
Multiply option.

Sub SetUnsetExchangeRate(strType As String)
Dim MaxRow As Integer, colFCLP As Integer, colCLP As Integer
Dim colPriceBase As Integer, wsSource As Worksheet
Dim vCols, vCol
Application.ScreenUpdating = False
Application.Calculation = xlManual
Set wsSource = Worksheets("Products")
'Get the Exchange Rate
'Set the column numbers variables
colFCLP = 6
colCLP = 16
colPriceBase = 15
'Get the total number of rows loaded in Products page.
' something missing here in your original?!
MaxRow = Worksheets("Products").Range("Productsloaded").

vCols = Array( colFCLP, colCLP, colPriceBase)

Worksheets("Start").Range("ExchangeRate").Copy

For Each vCol In vCols
'apply the exchange rate to all cells in column at once.
With wsSource.Cells(4, vCol).Resize(MaxRow-4+1)
If strType = "Apply" Then
.PasteSpecial xlValues, Operation:=xlMultiply
Else
.PasteSpecial xlValues, Operation:=xlDivide
End If
End With
Next vCol

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup