Posts by KjBox

  • Data list summary-2D array

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

    Code
        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.

  • need this macro, in cfs on hand report, changed as described. $20.00

    Try

  • need this macro, in cfs on hand report, changed as described. $20.00

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

  • need this macro, in cfs on hand report, changed as described. $20.00

    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

    Code
    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

  • Finding a match in strings in 2 columns n 2 sheets and if match found copy cell to sheet 1

    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.

  • Finding a match in strings in 2 columns n 2 sheets and if match found copy cell to sheet 1

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

  • VBA code to prevent typing in Cells and Columns

    Maybe

    Code
    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

  • VBA to sum column with particular header and capture in a master file

    Maybe:

  • Need help modifying a code to copy only certain rows based off a criteria.

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

  • Need to copy only part of a row if conditions are met and paste into new worksheet.

    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