pivot table truncation memo field


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: pivot table truncation memo field

  1. #1
    Join Date
    Feb 2009
    Posts
    3

    pivot table truncation memo field

    Excel Pivot Tables truncate cells to 255 characters when you double-click to show detail. I need VBA code to retrieve the complete cell value from the original detail tab:

    My Code:
    Code:
    Sub fetch_Comments()
    
    Dim rngCurrent As Range
    Dim rngCell As Range
    Dim rowct As Integer
    
    Sheets("Research").Select
    Range("q9").Select
    
    Set rngCurrent = Research.Range("q9").CurrentRegion 
    'here I get an Error Message;"object required"; in the watch window Current Region does not populate a value
    
    rowct = CurrentRegion.Rows.Count
    rngCurrent = Sheets("Research").Range("q9:q rowCt")
    
    rngCurrent.Select
    For Each rngCell In rngCurrent
        If Len(rngCell) > 255 Then
            rngCell = Sheets("Details with Comments").Range("q rowCt")
        End If
    Next rngCell
    
    End Sub
    Last edited by Hack; 02-19-2009 at 12:05 PM. Reason: Added Code Tags

  2. #2
    Join Date
    Feb 2009
    Posts
    7
    Hi

    Have had a play with the code, and changed a little bit of your syntax. Try this:
    Code:
    Sub fetch_Comments()
    
    Dim rngCurrent As Range
    Dim rngCell As Range
    Dim rowct As Integer
    
    Sheets("Research").Select
    Range("q1").Select
    
    Set rngCurrent = ActiveCell.CurrentRegion
    
    rowct = ActiveCell.CurrentRegion.Rows.Count
    Sheets("Research").Range("q1:q" & rowct).Select
    
    For Each rngCell In rngCurrent
        If Len(rngCell) > 255 Then
            rngCell = Sheets("Details with Comments").Range("a rowCt")
        End If
    Next rngCell
    
    End Sub
    Once the cell is selected then ActiveCell can be used to identify those cells occupied within the current region.

    Hope this helps

    Debs
    Last edited by Hack; 02-19-2009 at 12:04 PM. Reason: Added Code Tags

  3. #3
    Join Date
    Feb 2009
    Posts
    7
    OOps.. i changed some of the cell references for testing. Here's a version you can copy to try
    Code:
    Sub fetch_Comments()
    
    Dim rngCurrent As Range
    Dim rngCell As Range
    Dim rowct As Integer
    
    Sheets("Research").Select
    Range("q9").Select
    
    Set rngCurrent = ActiveCell.CurrentRegion
    
    rowct = ActiveCell.CurrentRegion.Rows.Count
    Sheets("Research").Range("q9:q" & rowct).Select
    
    For Each rngCell In rngCurrent
         If Len(rngCell) > 255 Then
             rngCell = Sheets("Details with Comments").Range("q rowCt")
         End If
    Next rngCell
    
    End Sub
    Last edited by Hack; 02-19-2009 at 12:04 PM. Reason: Added Code Tags

Similar Threads

  1. Access memo field 64k limit...
    By Jeff in forum VB Classic
    Replies: 2
    Last Post: 06-28-2001, 12:05 PM
  2. Adding a field to an existing Table
    By Mike in forum VB Classic
    Replies: 1
    Last Post: 05-09-2001, 09:57 AM
  3. Concat table to one field
    By Brian in forum authorevents.vieira
    Replies: 0
    Last Post: 01-22-2001, 03:31 PM
  4. Clearing Pivot table fields "memory" in VB?
    By Fred Giesen in forum authorevents.patrick
    Replies: 2
    Last Post: 09-06-2000, 05:17 PM
  5. Multi-row calculations
    By Bob Hines in forum Database
    Replies: 7
    Last Post: 04-27-2000, 11:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center