Adding values from two different worksheets from workbook
 

Hi,

Here is the VB code which I have been working on:

Function Defects(MyMonth As Date, Dept As String, DefectCode As
String)

'Windows("Input.xls").ActivateSheets("Customer Returns
(External)").Activate Defects = 0
With Workbooks("Input.xls").Sheets("Customer Returns (External)")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Defects = 0
For RowCount = 3 To LastRow

If (Month(.Cells(RowCount, "A")) = Month(MyMonth)) Then
If (.Cells(RowCount, "E") = Left(Dept, 2)) Then
If (.Cells(RowCount, "F") = DefectCode) Then
If (IsEmpty(.Cells(RowCount, "J"))) Then
If (IsEmpty(.Cells(RowCount, "I"))) Then
Defects = Defects + .Cells(RowCount, "H")
Else
Defects = Defects + .Cells(RowCount, "I")
End If
Else
Defects = Defects + .Cells(RowCount, "J")
End If
End If
End If
End If
Next RowCount
End With

With Workbooks("Input.xls").Sheets("LineReturns(Interna l)")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 3 To LastRow

If (Month(.Cells(RowCount, "A")) = Month(MyMonth)) Then
If (.Cells(RowCount, "E") = Left(Dept, 2)) Then
Defects = Defects + .Cells(RowCount, "C")
End If
End If

Next RowCount

End With
End Function


------------------------------------------------------------------

My objective is to go to customer returns worksheet, check for
conditions if it is the same month, dept, and defect code and then
return the quantity of defects. Then go to line returns worksheet,
check for conditions if it is same month, dept and defect code and
return the quantity of defects. Then the returned value that I must
get is the value from customer defects + value from line returns.

I have tried all means to fix this but it is not working. Can anybody
find the problem with this code. Thanks

Adding values from two different worksheets from workbook
 

I've told you before, you can't activate another worksheet from inside a
function

'Windows("Input.xls").ActivateSheets("Customer Returns(External)").Activate

Defects = 0

"Mahadevan Swamy" wrote:

Hi,

Here is the VB code which I have been working on:

Function Defects(MyMonth As Date, Dept As String, DefectCode As
String)

'Windows("Input.xls").ActivateSheets("Customer Returns
(External)").Activate Defects = 0
With Workbooks("Input.xls").Sheets("Customer Returns (External)")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Defects = 0
For RowCount = 3 To LastRow

If (Month(.Cells(RowCount, "A")) = Month(MyMonth)) Then
If (.Cells(RowCount, "E") = Left(Dept, 2)) Then
If (.Cells(RowCount, "F") = DefectCode) Then
If (IsEmpty(.Cells(RowCount, "J"))) Then
If (IsEmpty(.Cells(RowCount, "I"))) Then
Defects = Defects + .Cells(RowCount, "H")
Else
Defects = Defects + .Cells(RowCount, "I")
End If
Else
Defects = Defects + .Cells(RowCount, "J")
End If
End If
End If
End If
Next RowCount
End With

With Workbooks("Input.xls").Sheets("LineReturns(Interna l)")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 3 To LastRow

If (Month(.Cells(RowCount, "A")) = Month(MyMonth)) Then
If (.Cells(RowCount, "E") = Left(Dept, 2)) Then
Defects = Defects + .Cells(RowCount, "C")
End If
End If

Next RowCount

End With
End Function


------------------------------------------------------------------

My objective is to go to customer returns worksheet, check for
conditions if it is the same month, dept, and defect code and then
return the quantity of defects. Then go to line returns worksheet,
check for conditions if it is same month, dept and defect code and
return the quantity of defects. Then the returned value that I must
get is the value from customer defects + value from line returns.

I have tried all means to fix this but it is not working. Can anybody
find the problem with this code. Thanks

Adding values from two different worksheets from workbook
 

So is there a way to solve this by putting the line returns data and
customer returns data in one worksheet?

On Jul 5, 3:28 pm, Joel wrote:
I've told you before, you can't activate another worksheet from inside a
function

'Windows("Input.xls").ActivateSheets("Customer Returns(External)").Activate

Defects = 0

"Mahadevan Swamy" wrote:
Hi,

Here is the VB code which I have been working on:

Function Defects(MyMonth As Date, Dept As String, DefectCode As
String)

'Windows("Input.xls").ActivateSheets("Customer Returns
(External)").Activate Defects = 0
With Workbooks("Input.xls").Sheets("Customer Returns (External)")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Defects = 0
For RowCount = 3 To LastRow

If (Month(.Cells(RowCount, "A")) = Month(MyMonth)) Then
If (.Cells(RowCount, "E") = Left(Dept, 2)) Then
If (.Cells(RowCount, "F") = DefectCode) Then
If (IsEmpty(.Cells(RowCount, "J"))) Then
If (IsEmpty(.Cells(RowCount, "I"))) Then
Defects = Defects + .Cells(RowCount, "H")
Else
Defects = Defects + .Cells(RowCount, "I")
End If
Else
Defects = Defects + .Cells(RowCount, "J")
End If
End If
End If
End If
Next RowCount
End With

With Workbooks("Input.xls").Sheets("LineReturns(Interna l)")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 3 To LastRow

If (Month(.Cells(RowCount, "A")) = Month(MyMonth)) Then
If (.Cells(RowCount, "E") = Left(Dept, 2)) Then
Defects = Defects + .Cells(RowCount, "C")
End If
End If

Next RowCount

End With
End Function

------------------------------------------------------------------

My objective is to go to customer returns worksheet, check for
conditions if it is the same month, dept, and defect code and then
return the quantity of defects. Then go to line returns worksheet,
check for conditions if it is same month, dept and defect code and
return the quantity of defects. Then the returned value that I must
get is the value from customer defects + value from line returns.

I have tried all means to fix this but it is not working. Can anybody
find the problem with this code. Thanks