ADG,
I am actually trying to get rid of spreadsheet formulue because the size and
links of the spreadsheet make it inefficient. Nick is right there were no
app.worksheetfunctions on the code I pasted. I use them to calculate the
purchase amounts...sorry my mistake I assume they might be the cause and
jumped the gun.
However I still dont know why the code work fine step by step but not all at
once...


Sub BND_PURCH_AMT()
Sheets("BND").Select
For j = 6 To 213 Step 9
For i = 4 To 500
If Cells(i, 1) = "" Then
Exit For
Else
TMC_PURCH_PRICE = Application.WorksheetFunction.VLookup(Range("A" &
i), Range("TMC_PURCHASING.xls!LANDED_BND_PR"), 15, False)
TMC_PURCH_AMT = Cells(i, j - 1) * TMC_PURCH_PRICE
Cells(i, j) = TMC_PURCH_AMT
End If
Next
Next
Call BND_USAGE_AMT_JANCY
End Sub


Sub BND_USAGE_AMT_JANCY()
Sheets("BND").Select
'JanCY
For i = 4 To 500
If Range("A" & i) = "" Then
Exit For
Else
TMC_BEG_INV_QTY = Cells(i, 3).Value
TMC_BEG_INV_AMT = Cells(i, 4).Value
TMC_PURCHASE_QTY = Cells(i, 5).Value
TMC_PURCHASE_AMT = Cells(i, 6).Value
TMC_USAGE_QTY = Cells(i, 8).Value
If (TMC_BEG_INV_QTY + TMC_PURCHASE_QTY) = 0 Then
TMC_USAGE_AMT = 0
TMC_USAGE_MAC = 0
Cells(i, 9) = TMC_USAGE_AMT
Cells(i, 7) = TMC_USAGE_MAC
Else
TMC_USAGE_AMT = (TMC_BEG_INV_AMT + TMC_PURCHASE_AMT) /
(TMC_BEG_INV_QTY + TMC_PURCHASE_QTY) * TMC_USAGE_QTY
Cells(i, 9) = TMC_USAGE_AMT
TMC_USAGE_MAC = (TMC_BEG_INV_AMT + TMC_PURCHASE_AMT) /
(TMC_BEG_INV_QTY + TMC_PURCHASE_QTY)
Cells(i, 7) = TMC_USAGE_MAC
End If
End If
Next
TMC_BEG_INV_QTY = 0
TMC_BEG_INV_AMT = 0
TMC_PURCHASE_QTY = 0
TMC_PURCHASE_AMT = 0
TMC_USAGE_QTY = 0
TMC_USAGE_AMT = 0
TMC_USAGE_MAC = 0
Set TMC_BEG_INV_QTY = Nothing
Set TMC_BEG_INV_AMT = Nothing
Set TMC_PURCHASE_QTY = Nothing
Set TMC_PURCHASE_AMT = Nothing
Set TMC_USAGE_QTY = Nothing
Set TMC_USAGE_AMT = Nothing
Set TMC_USAGE_MAC = Nothing
Call BND_USAGE_AMT_FEBCY
End Sub

Sub BND_USAGE_AMT_FEBCY()
'FebCY
For i = 4 To 500
If Range("A" & i) = "" Then
Exit For
Else
TMC_BEG_INV_QTY = Cells(i, 12).Value
TMC_BEG_INV_AMT = Cells(i, 13).Value
TMC_PURCHASE_QTY = Cells(i, 14).Value
TMC_PURCHASE_AMT = Cells(i, 15).Value
TMC_USAGE_QTY = Cells(i, 17).Value
If (TMC_BEG_INV_QTY + TMC_PURCHASE_QTY) = 0 Then
TMC_USAGE_AMT = 0
TMC_USAGE_MAC = 0
Cells(i, 18) = TMC_USAGE_AMT
Cells(i, 16) = TMC_USAGE_MAC
Else
TMC_USAGE_AMT = (TMC_BEG_INV_AMT + TMC_PURCHASE_AMT) /
(TMC_BEG_INV_QTY + TMC_PURCHASE_QTY) * TMC_USAGE_QTY
Cells(i, 18) = TMC_USAGE_AMT
TMC_USAGE_MAC = (TMC_BEG_INV_AMT + TMC_PURCHASE_AMT) /
(TMC_BEG_INV_QTY + TMC_PURCHASE_QTY)
Cells(i, 16) = TMC_USAGE_MAC
End If
End If
Next
TMC_BEG_INV_QTY = 0
TMC_BEG_INV_AMT = 0
TMC_PURCHASE_QTY = 0
TMC_PURCHASE_AMT = 0
TMC_USAGE_QTY = 0
TMC_USAGE_AMT = 0
TMC_USAGE_MAC = 0
Set TMC_BEG_INV_QTY = Nothing
Set TMC_BEG_INV_AMT = Nothing
Set TMC_PURCHASE_QTY = Nothing
Set TMC_PURCHASE_AMT = Nothing
Set TMC_USAGE_QTY = Nothing
Set TMC_USAGE_AMT = Nothing
Set TMC_USAGE_MAC = Nothing
Call BND_USAGE_AMT_MARCY
End Sub

"ADG" wrote:

Hi Ozgur

All of your code could be replaced by formulae in the spreadsheet. The
process seems relatively straight forward.

Can you give an example line of data, and which cell is being updated
incorrectly.

Regards

--
Tony Green


"Ozgur Pars" wrote:

Hello,
I am using a Vlookup and a sumif function in loops... when I gor through the
sub via F8 the calculation is correct but when I execute at once with F5 I
get a incorrect answer... I have to find a solution to this.
Here is a sample of it:
(I tried everything I know please aby help would be appreciated greatly)

For i = 4 To 500
If Sheets("DLF").Range("A" & i) = "" Then
Exit For
Else
TMC_BEG_INV_QTY = Sheets("DLF").Cells(i, 3).Value
TMC_BEG_INV_AMT = Sheets("DLF").Cells(i, 4).Value
TMC_PURCHASE_QTY = Sheets("DLF").Cells(i, 5).Value
TMC_PURCHASE_AMT = Sheets("DLF").Cells(i, 6).Value
TMC_USAGE_QTY = Sheets("DLF").Cells(i, 8).Value
If (TMC_BEG_INV_QTY + TMC_PURCHASE_QTY) = 0 Then
TMC_USAGE_AMT = 0
TMC_USAGE_MAC = 0
Sheets("DLF").Cells(i, 9) = TMC_USAGE_AMT
Sheets("DLF").Cells(i, 7) = TMC_USAGE_MAC
Else
TMC_USAGE_AMT = (TMC_BEG_INV_AMT + TMC_PURCHASE_AMT) /
(TMC_BEG_INV_QTY + TMC_PURCHASE_QTY) * TMC_USAGE_QTY
Sheets("DLF").Cells(i, 9) = TMC_USAGE_AMT
TMC_USAGE_MAC = (TMC_BEG_INV_AMT + TMC_PURCHASE_AMT) /
(TMC_BEG_INV_QTY + TMC_PURCHASE_QTY)
Sheets("DLF").Cells(i, 7) = TMC_USAGE_MAC
End If
End If
Next
TMC_BEG_INV_QTY = 0
TMC_BEG_INV_AMT = 0
TMC_PURCHASE_QTY = 0
TMC_PURCHASE_AMT = 0
TMC_USAGE_QTY = 0
TMC_USAGE_AMT = 0
TMC_USAGE_MAC = 0
Call DLF_USAGE_AMT_FEBCY
End Sub

Sub DLF_USAGE_AMT_FEBCY()
'FebCY
For i = 4 To 500
If Sheets("DLF").Range("A" & i) = "" Then
Exit For
Else
TMC_BEG_INV_QTY = Sheets("DLF").Cells(i, 12).Value
TMC_BEG_INV_AMT = Sheets("DLF").Cells(i, 13).Value
TMC_PURCHASE_QTY = Sheets("DLF").Cells(i, 14).Value
TMC_PURCHASE_AMT = Sheets("DLF").Cells(i, 15).Value
TMC_USAGE_QTY = Sheets("DLF").Cells(i, 17).Value
If (TMC_BEG_INV_QTY + TMC_PURCHASE_QTY) = 0 Then
TMC_USAGE_AMT = 0
TMC_USAGE_MAC = 0
Sheets("DLF").Cells(i, 18) = TMC_USAGE_AMT
Sheets("DLF").Cells(i, 16) = TMC_USAGE_MAC
Else
TMC_USAGE_AMT = (TMC_BEG_INV_AMT + TMC_PURCHASE_AMT) /
(TMC_BEG_INV_QTY + TMC_PURCHASE_QTY) * TMC_USAGE_QTY
Sheets("DLF").Cells(i, 18) = TMC_USAGE_AMT
TMC_USAGE_MAC = (TMC_BEG_INV_AMT + TMC_PURCHASE_AMT) /
(TMC_BEG_INV_QTY + TMC_PURCHASE_QTY)
Sheets("DLF").Cells(i, 16) = TMC_USAGE_MAC
End If
End If
Next
TMC_BEG_INV_QTY = 0
TMC_BEG_INV_AMT = 0
TMC_PURCHASE_QTY = 0
TMC_PURCHASE_AMT = 0
TMC_USAGE_QTY = 0
TMC_USAGE_AMT = 0
TMC_USAGE_MAC = 0
Call DLF_USAGE_AMT_MARCY
End Sub