Results 1 to 9 of 9

Find, loop and paste code not working

Threaded View

Sc0tt1e Find, loop and paste code not... 03-15-2014, 07:56 PM
Sc0tt1e Re: Find, loop and paste code... 03-15-2014, 07:59 PM
Sc0tt1e Code won't paste 03-15-2014, 10:24 PM
6StringJazzer Re: Code won't paste 03-16-2014, 08:58 AM
Sc0tt1e Re: Code won't paste 03-16-2014, 07:55 PM
Sc0tt1e Re: Code won't paste 03-16-2014, 07:50 PM
Sc0tt1e Re: Find, loop and paste code... 03-19-2014, 01:30 PM
Sc0tt1e Re: Find, loop and paste code... 03-19-2014, 02:07 PM
Sc0tt1e Re: Find, loop and paste code... 03-19-2014, 10:31 PM
  1. #1
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    640

    Find, loop and paste code not working

    The below code is used on the "Documents (Attach") button using the "Letters" userform.

    The idea is if a template letter is created there will only be one of them and it will be pasted into a specific cell (omitted from this code), but if a case handler creates a free form letter, or a letter from a customer is received it can be saved and then entered into the database.

    The idea of the code is meant to find a documents location and paste it into the "Database" tab. If it fits the select case criteria it pastes it into a defined row (left out of this book), if it doesn't it is pasted into a free cell from cell CH (I think it's somewhere around there) onwards.

    I have been playing with this for over a week and still can't quite get it right, any help would be greatly appreciated.

    Document attached to see how it works.


    Private Sub CommandButton2_Click()
    Dim UserId As String
    Dim CaseId As String
    Dim SearchString As String
    
       UserId = Letters.User.Value
       CaseId = Letters.CaseNo.Value
       SearchString = UserId & CaseId
       
       Select Case Description.Value
        Case "Customer Complaint Acknowledgement", "Complaint to Bank", "8 Week Breach to Bank", "Final Response Received", "Referal to FOS", "FOS Decision", "Payment Due", "Payment Overdue - Chaser 1", "Payment Overdue - Chaser 2", "Case Closed"
             Description.Value = "Templates"
        End Select
        
        Letters.Browse.Copy
        
                If Letters.Browse.Text <> "" And Letters.Description.Text = "" Then
            MsgBox "Complete Document Description"
            End If
       
    '   Search Case Reference Number column from database based on two values in Userform
    
    '
                If Letters.Browse.Text <> "Templates" And Letters.Description.Text <> "" And Sheets("Database").Cells(WorksheetFunction.Match(SearchString, Sheets("Database").Range("B:B"), 0), 86).Value = "" Then
    On Error Resume Next
    Sheets("Database").Cells(WorksheetFunction.Match(SearchString, Sheets("Database").Range("B:B"), 0), 86).Value = Letters.Browse.Value
    Sheets("Database").Cells(WorksheetFunction.Match(SearchString, Sheets("Database").Range("B:B"), 0), 87).Value = Letters.Description.Value
    On Error GoTo 0
    
                ElseIf Letters.Browse.Text <> "Templates" And Letters.Description.Text <> "" And Sheets("Database").Cells(WorksheetFunction.Match(SearchString, Sheets("Database").Range("B:B"), 0), 86).Value <> "" Then
                    Do Until IsEmpty = False
                        ActiveCell.Offset(0, 3).Select
                            Loop
                            
                Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
                    
        End If
        
            Letters.Hide
            
    End Sub
    Attached Files Attached Files
    Last edited by Sc0tt1e; 03-15-2014 at 07:58 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code not working the 2nd time in For loop
    By rbs123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2013, 06:19 AM
  2. Code: Find, Copy, Paste not working
    By ufopilot3 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-26-2012, 03:40 PM
  3. code to find text, offset 1 column and paste to new workbook not working
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-10-2011, 07:55 AM
  4. Loop to find next empty cell to the right not working.
    By Casey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2006, 10:16 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1