Need Cell Values instead of Cell Address

BDarden

New member
Joined
Dec 23, 2013
Messages
2
Reaction score
0
Points
0
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
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
a comment in your code is ' Activesheet in the Sample Data workbook.

I suspect you would get more/better assistance if you were to upload the sample workbook(s) with your complete code so people don't have to guess at what you are working with.
 
Top