Posts by KjBox

    To find the Column Number for the "Date" column use

        Dim lColDt As Long
        lColDt = Application.Match("Date", Sheet1.Rows(1), 0)

    Repeat for other columns.

    As your profile shows you are a student I assume that this is an assignment, so the above is the only help I am prepared to provide.


    My bad! Try changing the code in the Standard Module to this

    Payment received, many thanks.

    Make sure all the CFS files and the CFS Report file are in the same folder.

    Put this in the ThisWorkbook Object module

    Private Sub Workbook_Open()
        If MsgBox("Do you want to create a new Report?", vbQuestion + vbYesNo, "Create Report") = vbYes Then CreateReport
    End Sub

    And in a standard module put this code

    1. Use this modified code to copy columns A, B & C from Foglio2 to Foglio1 when a match is found. I have added comments to help you understand the code.
    2. Mid(y(i, 1), 2, 13) The Mid function has 3 elements: String of Characters, Starting point, Number of Characters. So, y(i, 1) is the string, 2 is the starting point (first character is ignored), 13 is the number of characters to return from the starting point onwards.
    3. Application.Match(Mid(y(i, 1), 2, 13), x, 0) VBA does not have a Match function so the "Application." bit tells the code to use the worksheet function MATCH, which has 3 elements: String to match, Array to search for a match, Exact or close match. So, Mid(y(i, 1), 2, 13) is the string that is to be matched, x is the array to search and 0 is "search for exact match"

    The code is faster because it is an "array based" code as opposed to an "object based" code.

    An array based code first loads all required data into arrays, those arrays can then be manipulated and/or modified within the machine memory before place modifications back to a worksheet.

    An object based code has to constantly refer back to a worksheet to place data and update, then pick up the next bit of data to check.

    The array based code refers to a worksheet just once to get data and once to place data, hence much faster.

    This will be faster, especially for thousands of rows of data


    Sub LockCells()
        With Sheet1.Cells(1).CurrentRegion
            .Parent.Unprotect "hello"
            .Locked = 0
            If Application.CountIf(.Rows(2), "TT") > 0 Then .Rows(2).Locked = 1
            If Application.CountIf(.Columns(1), "TT") > 0 Then .Columns(1).Locked = 1
            .Parent.Protect "hello"
        End With
        Sheet2.Visible = 2
    End Sub


    Try the attached file, I have amended the code assigned to the button to:

    Try the attached, click the button on Hot List Sheet. Any existing data will be cleared and new data added.

    Code assigned to the button:

    You will find the code runs much faster than the original, especially if data set is large