Sum If/ Data output problems
Well, thanks for the help with the sumif function work that I asked for on the last post, however, all is not right with my world ^^;
What I need to do, is basically have this part of hte module set to grab information from one sheet, and port it over to another sheet. There are a few things that it needs to take into account when it's putting it onto the new sheet:
First, it is date sensitive, so it's looking for information in there for all entries within two dates. Secondly, it (and this is the reason why I used the SumiIf function) was that it added up all the information within those ranged (i don't think sumproduct would help here based on what it does). Lastly, I would rather the formulas be placed in VB format instead of a cell = "<insert function here>" method. (I got the syntax for the sumif if it was on the same sheet, but i honestly don't know how to have it look for a different sheet).
I included the 'For' method that i managed it to look up all information for each of the reps on here, but it unfortunately is not searching only for the date range.
Here is the code:
[VBA]Sub OverallQC()
Set sht6 = Worksheets("Overall Stats")
Set sht7 = Worksheets("QCInfo")
Set sht8 = Worksheets("DataDump")
Dim i As Integer 'row counter for the data page "QCInfo" for finding last row
Dim j As Integer 'row counter for the data page "Overall Stats"
Dim k As Long 'row counter for the data page "DataDump"
i = 1
j = 10
k = 1
Application.ScreenUpdating = False
'Clear all information
Range("A10:D150").Select
Range("A10").Activate
Selection.ClearContents
Range("B6:D6").Select
Selection.ClearContents
Application.Goto Reference:=Range("A10"), Scroll:=False
'Add the updated list of reps
Sheets("DataDump").Select
Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp)).Select
Selection.Copy
Sheets("Overall Stats").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:=Range("A10"), Scroll:=False
Do While IsEmpty(sht7.Cells(i, 1).Value) = False 'keep doing until we have searched through the whole data page "Comments"
If sht6.Range("C3") <= sht7.Range("B" & i) And sht6.Range("D3") >= sht7.Range("B" & i) Then 'this places the data if the names match
sht6.Range("B" & j) = "=IF(RC[1]=0,0,RC[1]/RC[2])"
sht6.Range("C" & j) = "=SUMIF(QCInfo!C1,RC1,QCInfo!C3)"
sht6.Range("D" & j) = "=SUMIF(QCInfo!C1,RC1,QCInfo!C4)"
j = j + 1
End If
i = i + 1
Loop
' For k = 10 To Cells(Rows.Count, "A").End(xlUp).Row
' If sht6.Cells(k, "A").Value = "" Then
' sht6.Range("B" & k) = "N/A"
' sht6.Range("C" & k) = "0"
' sht6.Range("D" & k) = "0"
' Else
' sht6.Range("B" & j) = "=IF(RC[1]=0,0,RC[1]/RC[2])"
' sht6.Range("C" & j) = "=SUMIF(QCInfo!C1,RC1,QCInfo!C3)"
' sht6.Range("D" & j) = "=SUMIF(QCInfo!C1,RC1,QCInfo!C4)"
' End If
' Next k
sht6.Range("C6") = Application.WorksheetFunction.Sum(Range(Cells(10, 3), Cells(Rows.Count, 3).End(xlUp)))
sht6.Range("D6") = Application.WorksheetFunction.Sum(Range(Cells(10, 4), Cells(Rows.Count, 4).End(xlUp)))
sht6.Range("B6") = "=IF(RC[1]=0,0,RC[1]/RC[2])"
Range(Cells(10, 1), Cells(Rows.Count, 4).End(xlUp)).Select
Selection.Sort Key1:=Range("B10"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A10").Select
Application.ScreenUpdating = True
End Sub[/VBA]
Thanks for all help that can be provided on this.
