application.worksheetfunction

Ozgur Pars · 07-18-2006, 01:50 AM

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

NickHK · 07-18-2006, 02:05 AM

I don't see you using VLookUp, SumIF or any Worksheet functions ??
Or am I missing something ?

NickHK

"Ozgur Pars" <[email protected]> wrote in message
news:[email protected]...
> 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

ADG · 07-18-2006, 02:40 AM

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

Ozgur Pars · 07-18-2006, 03:00 AM

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

NickHK · 07-18-2006, 03:15 AM

I would guess something to do with the activesheet.
I find it more clear to be explicit about which book/sheet you are referring
to. e.g
Dim BNDSheet As Worksheet
Dim TMCLookUpRange As Range

Set BNDSheet =Thisworkbook.Worksheets("BND")
Set TMCLookUpRange =
Workbooks("TMC_PURCHASING.xls').Worksheets(<SheetName>).Range("LANDED_BND_PR
")

For j = 6 To 213 Step 9
For i = 4 To 500
If BNDSheet.Cells(i, 1) = "" Then
Exit For
Else
TMC_PURCH_PRICE =
Application.WorksheetFunction.VLookup(BNDSheet.Range("A" & i),
TMCLookUpRange, 15, False)
'......etc

NickHK

"Ozgur Pars" <[email protected]> wrote in message
news:[email protected]...
> 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