Here is my code I have, I keep getting my master data sheet filled with the cell address as opposed to the cell value... can someone tell me what I'm doing wrong?
Code:
Sub AddData(ByRef Wks As Worksheet)
' Written: Decemeber 22, 2013
' Updated: December 23, 2013
Dim AgentId As Variant
Dim AuditInfo As Variant
Dim AuditMonth As Integer
Dim AuditYear As Integer
Dim CallNumber As Integer
Dim Cell As Range
Dim cnt As Integer
Dim DataRng As Range
Dim DstRng As Range
Dim FilterRng As Range
Dim n As Integer
Dim Row As Range
Dim SrcRng As Range
Dim SrcWks As Worksheet
' Activesheet in the Sample Data workbook.
Set SrcWks = Wks
' Database worksheet in Master Ddata workbook.
Set DstRng = Sheet1.Range("A1").CurrentRegion
Set DataRng = Intersect(DstRng, DstRng.Offset(1, 0))
' Record must contain one or more logged calls.
If Application.Sum(SrcWks.Range("G4:I10")) = 0 Then
MsgBox "Records Without Call Information Can Not Be Searched or Saved."
Exit Sub
End If
' Size the AuditInfo array to hold all the call audit info.
ReDim AuditInfo(1 To 3, 1 To 40)
' Load the AuditInfo array will audit data from all 3 calls.
For n = 1 To 3
cnt = 0
For Each Cell In SrcWks.Range("C3:C4, C6:C8, G3:G10, G13:G19, G25, G29:G31, G35, G39:G40, G44:G45, G49:G58, G73")
cnt = cnt + 1
If cnt < 6 Then
AuditInfo(n, cnt) = Cell.Value
Else
AuditInfo(n, cnt) = Cell.Offset(0, n - 1).Value
End If
Next Cell
Next n
' Autofilter the Database using three criteria from the scorecard.
DstRng.Parent.AutoFilterMode = False ' Clear the AutoFilter
DstRng.AutoFilter Field:=1, Criteria1:=SrcWks.Range("C4") ' Agent ID
DstRng.AutoFilter Field:=4, Criteria1:=SrcWks.Range("C7") ' Audit Year
DstRng.AutoFilter Field:=3, Criteria1:=SrcWks.Range("C6") ' Audit Month
' Get only the filtered rows.
Set FilterRng = DstRng.SpecialCells(xlCellTypeVisible)
' Check the filtered results.
If FilterRng.Address = DstRng.Rows(1).Address Then
' Only the header row is present - Add the new information.
Sheet1.Cells(DataRng.Rows.Count + DataRng.Row, 1).Resize(3, 40).Value = AuditInfo
Else
' One or matching rows are present - Overwrite the old information.
For Each Row In FilterRng.Rows
If Row.Row <> 1 Then
n = FilterRng.Item(Row.Row, 13)
Row.Cells.Value = Application.Index(AuditInfo, n, 0)
End If
Next Row
End If
Bookmarks