Results 1 to 2 of 2

Thread: Need Cell Values instead of Cell Address

  1. #1

    Need Cell Values instead of Cell Address



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    676
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •