Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Move Data Only After All Required Range Cells Have Been Filled

  1. #1
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12

    Move Data Only After All Required Range Cells Have Been Filled

    Hi Admin

    Thanks

    Can we finish this with a final alteration to the Clear Data code...can the Clear Data button only be functional once the data for that week has been entered into Records sheet...once the data has been Transferred to the Record Sheet by pressing the Transfer Data To Records button then the Data can be cleared

    Thanks

    Rich
    Attached Files Attached Files
    Last edited by rich_cirillo; 06-26-2013 at 07:58 AM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    hi rich_cirillo

    Thread titles should give a clear and concise objective of the thread. Please do not use words like 'Help', 'Please', etc. Users searching for objectives similar to your request will not be able to search threads with relevant keywords. You have been using online communities for quite some time now, and are expected to adhere to the general guidelines.

    Thanks for co-operating. Please edit the thread, and give an appropriate thread title.

    EF
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Hi excel Fox

    My apologies...I tried to edit my post but was unable to do for some reason

    Thanks

    Rich

  4. #4
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Hi

    Still need help with this request if possible

    Thanks

    Rich

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    try this.

    Code:
    Option Explicit
    
    Dim nmFlag  As Name
        
    Sub insert_data()
        
        Dim d, i As Long, k, q, x, r As Long, Rng1 As Range
        Dim c As Long, lRow As Long, Rng2 As Range, Hdr
        
        lRow = Sheets("records").Range("c" & Sheets("records").Rows.Count).End(3).Row
        Set Rng2 = Sheets("records").Range("c3:m" & lRow)
        d = Rng2.Value2
        q = Application.Index(d, 0, 1)
        
        Set Rng1 = Sheets("data").Range("b32:L37")
        Hdr = Sheets("data").Range("c21:l21")
        
        If Application.WorksheetFunction.CountA(Rng1) = Rng1.Cells.Count Then
            k = Rng1.Value2
            
            x = Application.Match(k(1, 1), q, 0)
            If Not IsError(x) Then
                If Len(d(x, 2)) * Len(d(x, 3)) Then 'check 2 columns whether they have data in those cells
                    MsgBox "It seems data already been entered for date " & CDate(k(1, 1))
                    Exit Sub
                Else
                    For r = 1 To UBound(k, 1)
                        For c = 1 To UBound(k, 2)
                            d(r + x - 1, c) = k(r, c)
                        Next
                    Next
                    For c = 2 To UBound(d, 2): d(x - 1, c) = Hdr(1, c - 1): Next
                End If
            Else
                Set Rng2 = Sheets("records").Range("c3:m" & lRow + 9)
                d = Rng2.Value2
                For r = 1 To UBound(k, 1)
                    For c = 1 To UBound(k, 2)
                        d(UBound(d, 1) - UBound(k, 1) + r, c) = k(r, c)
                    Next
                Next
                For c = 2 To UBound(d, 2): d(UBound(d, 1) - UBound(k, 1), c) = Hdr(1, c - 1): Next
            End If
            Rng2 = d
            Rng2.Columns(1).NumberFormat = "m/d/yyyy"
            With Rng2.Resize(Rng2.Rows.Count + 2, Rng2.Columns.Count)
                .BorderAround , xlThin
                .Borders(xlInsideHorizontal).Weight = xlThin
                .Borders(xlInsideVertical).Weight = xlThin
                .Rows.RowHeight = 25
            End With
            On Error Resume Next
            Set nmFlag = ThisWorkbook.Names("Flag")
            On Error GoTo 0
            If nmFlag Is Nothing Then
                ThisWorkbook.Names.Add "Flag", "TRUE", 1
            Else
                nmFlag.RefersTo = "TRUE"
            End If
        Else
            MsgBox "Cannot transfer until all data entered", vbCritical
        End If
        
    End Sub
    
    Sub ClearData()
        
        Dim Rng     As Range
            
        Set Rng = Sheets("data").Range("c32:l37")
        On Error Resume Next
        Set nmFlag = ThisWorkbook.Names("Flag")
        On Error GoTo 0
        
        If Application.WorksheetFunction.CountA(Rng) = Rng.Cells.Count Then
            If Evaluate("Flag") Then
                Sheets("data").Range("c32:l37").ClearContents
                If nmFlag Is Nothing Then
                    ThisWorkbook.Names.Add "Flag", "FALSE", 1
                Else
                    nmFlag.RefersTo = "FALSE"
                End If
            Else
                MsgBox "Transfer the Data first", vbInformation
            End If
        Else
            MsgBox "Cannot be deleted as incomplete", vbCritical
        End If
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Thanks Admin

    I keep getting 2 errors - 1 for insert data and the other 1 for clear data

    Regards

    Rich
    Attached Files Attached Files

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Adjust your ranges. The number of columns for both the source as well as destination range should be same. The header columns will be one columns less than the source range.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Hi Admin
    Thanks
    Are you referring to the ranges within the code?
    I am not sure i follow where to change.I would like to learn about code
    Thanks
    Rich

  9. #9
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Yes, I'm talking about within the code.

    Code:
    Set Rng2 = Sheets("records").Range("c3:n" & lRow)
    Code:
    Set Rng1 = Sheets("data").Range("B32:m37")
        Hdr = Sheets("data").Range("C21:m21")
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  10. #10
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Hi Admin
    Thank you

Similar Threads

  1. Replies: 4
    Last Post: 07-02-2013, 11:32 AM
  2. Replies: 2
    Last Post: 06-24-2013, 07:40 PM
  3. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  4. Replies: 22
    Last Post: 03-19-2013, 07:57 AM
  5. Get last Filled Cell address in a Range.
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-24-2012, 01:08 AM

Posting Permissions

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