Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a spreadsheet that has five fields for each entry
    Date
    S/F
    Re-dial V/A
    S/F
    notes

    All entries will have a date and some will have the other info. I want to export to another sheet in the workbook the DC Name (BUF, LAN, LIV, ETC), DATE AND NOTE fields only if there is something in the note field. If the field is empty do nothing.
    This is essentially a summary of the entries that have notes in them.

    Each group of entries has as the first entry Date and last entry notes. There will be approximately 50 columns like this

    thanks
    I have attached sample.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Create a sheet named Summary and enter DC Name in A1, Date in B1 and Note in C1.
    Run the following code:

    Code:
    Sub FillSummary()
      Dim wshS As Worksheet
      Dim wshT As Worksheet
      Dim m As Long
      Dim n As Long
      Dim r As Long
      Dim c As Long
      Dim t As Long
    
      Set wshS = Worksheets("callouts")
      Set wshT = Worksheets("Summary")
      wshT.Range("2:" & wshT.Rows.Count).ClearContents
      t = 1
      m = wshS.Cells.Find(What:="*", SearchOrder:=xlByRows, _
    	SearchDirection:=xlPrevious).Row
      n = wshS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
    	SearchDirection:=xlPrevious).Column
      For r = 9 To m Step 5
    	For c = 2 To n
    	  If Not wshS.Cells(r, c) = "" Then
    		t = t + 1
    		wshT.Cells(t, 1) = wshS.Cells(4, c)
    		wshT.Cells(t, 2) = wshS.Cells(r - 4, c)
    		wshT.Cells(t, 3) = wshS.Cells(r, c)
    	  End If
    	Next c
      Next r
    End Sub
    See the attached version. [attachment=83228:callouts1.xls]
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='769455' date='06-Apr-2009 15:48']Create a sheet named Summary and enter DC Name in A1, Date in B1 and Note in C1.
    Run the following code:

    Code:
    Sub FillSummary()
      Dim wshS As Worksheet
      Dim wshT As Worksheet
      Dim m As Long
      Dim n As Long
      Dim r As Long
      Dim c As Long
      Dim t As Long
    
      Set wshS = Worksheets("callouts")
      Set wshT = Worksheets("Summary")
      wshT.Range("2:" & wshT.Rows.Count).ClearContents
      t = 1
      m = wshS.Cells.Find(What:="*", SearchOrder:=xlByRows, _
    	SearchDirection:=xlPrevious).Row
      n = wshS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
    	SearchDirection:=xlPrevious).Column
      For r = 9 To m Step 5
    	For c = 2 To n
    	  If Not wshS.Cells(r, c) = "" Then
    		t = t + 1
    		wshT.Cells(t, 1) = wshS.Cells(4, c)
    		wshT.Cells(t, 2) = wshS.Cells(r - 4, c)
    		wshT.Cells(t, 3) = wshS.Cells(r, c)
    	  End If
    	Next c
      Next r
    End Sub
    See the attached version. [attachment=83228:callouts1.xls][/quote]

    GENIUS - Thanks so much for your help

Posting Permissions

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