Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > application.worksheetfunction
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 70
Default application.worksheetfunction
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default application.worksheetfunction
I don't see you using VLookUp, SumIF or any Worksheet functions ??
Or am I missing something ?

NickHK

"Ozgur Pars" wrote in message
...
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
ADG ADG is offline
external usenet poster
  
Posts: 76
Default application.worksheetfunction
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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 70
Default application.worksheetfunction
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default application.worksheetfunction
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(<SheetN ame).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.Ran ge("A" & i),
TMCLookUpRange, 15, False)
'......etc

NickHK

"Ozgur Pars" wrote in message
...
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with Application.WorksheetFunction Ayo Excel Discussion (Misc queries) 4 May 14th 08 11:13 PM
Application.WorksheetFunction._WSFunction R Avery[_2_] Excel Programming 0 May 13th 05 09:00 PM
Application.worksheetfunction Terry V Excel Programming 7 October 12th 04 05:48 AM
application.worksheetfunction.mmult help Alex[_13_] Excel Programming 2 October 29th 03 10:13 PM
Using Application.WorksheetFunction.Ln(...) in VBA doco Excel Programming 4 August 25th 03 01:08 PM


All times are GMT +1. The time now is 05:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement