CODEWITHSUNDEEP
excelvba
sheela
sheela

Reputation: 29

Calculation operation of Filtered Inventory Data

Below is the program for calculation of Date Diff. between a cell and a column, and then a small calculation with in column and then Filtering Inventory till a particular date (It is the same Date used for calculating the Date. Diff. in first line of the programm. In the programm, I am not getting the result of the calculation operation. I get only 0 as answer only one cell. Can some one help to identify the problem. 

Sub Filter_RPCALC()

'Calculation of Date Diff.
Range("N2").Formula = "=DAYS($A$590,D2)"
Range("N2").AutoFill Destination:=Range("N2:N585"), Type:=xlFillDefault

'Calculation of Rp
Dim Rp As Integer
Dim var1 As Integer
Dim var2 As Integer
Dim var3 As Integer
var1 = Range("M2:M585").Select
var2 = Range("02:0585").Select
var3 = Range("L2:L585").Select
Rp = var1 * var2 + var3
Range("P2:P585").Select
ActiveCell.FormulaR1C1 = Rp

'Filter the coils for Deliver Date
ActiveSheet.Range("$G$1:$G$585").AutoFilter Field:=1, Criteria1:="<" & CLng(Range("A590"))

End Sub

Upvotes: 0

Views: 36

Answers (1)

MGP
MGP

Reputation: 2551

Try not to use Select. Try this instead:

Sub Filter_RPCALC()

Dim was As Worksheet

'Calculation of Date Diff.
Range("N2").Formula = "=DAYS($A$590,D2)"
Range("N2").AutoFill Destination:=Range("N2:N585"), Type:=xlFillDefault

'Calculation of Rp
Dim var1 As Variant, var2 As Variant, var3 As Variant
Dim Rp As Variant
Dim i As Long
var1 = Range("M2:M585").Value
var2 = Range("02:0585").Value
var3 = Range("L2:L585").Value
Rp = var1
For i = LBound(Rp,1) To UBound(Rp,1)
    Rp(i,1) = var1(i,1)*var2(i,1) + var3(i,1)
Next i
Range("P2:P585").Value = Rp

'Filter the coils for Deliver Date
ActiveSheet.Range("$G$1:$G$585").AutoFilter Field:=1, Criteria1:="<" & CLng(Range("A590"))

Set ws = ThisWorkbook.Sheets.Add(After:= ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

ActiveSheet.Range("G1:G585").SpecialCells(xlCellTypeVisible).Copy Destination:=ws.Range("A1")

End Sub

Upvotes: 1

Related Questions