Results 1 to 5 of 5

Amending existing macro (removing restrictions)

Threaded View

index9 Amending existing macro... 11-03-2013, 11:31 PM
HaHoBe Re: Amending existing macro... 11-03-2013, 11:49 PM
index9 Re: Amending existing macro... 11-04-2013, 12:58 AM
HaHoBe Re: Amending existing macro... 11-04-2013, 01:04 AM
index9 Re: Amending existing macro... 11-04-2013, 01:47 AM
  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Amending existing macro (removing restrictions)

    Hi,

    I have copied and pasted a code (courtesy of contexture) and adapted it to work with my worksheet. This macro allows to add info from one sheet to another if all cells are filled in, however, I don't wish to have such restriction and that it can still add info to another sheet if some cells are empty (it currently has a pop up message alerting me to 'Please fill in all the cells'). How do I go about doing this in the current code?

    Your help is much appreciated.

    Sub UpdateLogWorksheet()
    
        Dim databaseWks As Worksheet
        Dim formWks As Worksheet
    
        Dim nextRow As Long
        Dim oCol As Long
    
        Dim myRng As Range
        Dim myCopy As String
        Dim myCell As Range
        
        'cells to copy from Form sheet - some contain formulas
        myCopy = "D3,D5,D7,D9,D11,D13,D15,D17,D19,D21,D23,D25"
    
        Set formWks = Worksheets("Selection Profile")
        Set databaseWks = Worksheets("Database")
    
        With databaseWks
            nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
        End With
    
        With formWks
            Set myRng = .Range(myCopy)
    
            If Application.CountA(myRng) <> myRng.Cells.Count Then
                MsgBox "Please fill in all the cells!"
                Exit Sub
            End If
        End With
    
        With databaseWks
            With .Cells(nextRow, "A")
            End With
            .Cells(nextRow, "B").Value = Application.UserName
            oCol = 1
            For Each myCell In myRng.Cells
                databaseWks.Cells(nextRow, oCol).Value = myCell.Value
                oCol = oCol + 1
            Next myCell
        End With
        
        'clear form cells that contain constants
        With formWks
          On Error Resume Next
             With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
                  .ClearContents
                  Application.Goto .Cells(1) ', Scroll:=True
             End With
          On Error GoTo 0
        End With
    End Sub
    Last edited by index9; 11-04-2013 at 01:50 AM. Reason: formatting

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help amending a macro needed
    By AdamBee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2013, 08:53 AM
  2. [SOLVED] Need help amending a find and mark macro.
    By ANDREWA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2012, 04:07 AM
  3. Amending basic Macro function
    By makana in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2009, 08:24 PM
  4. Help amending existing marco for a criteria search on a database
    By howardjo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2008, 07:01 AM
  5. [SOLVED] Amending an existing function
    By Paydog4 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2006, 11:55 PM

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