Posts by Gadi

    Re: Determine when no items are selected in a multi-select listbox on a userform

    Quote from sb44;762864

    I'm getting an error with "ItemsSelected". I have added the MS Office 15.0 Access database engine object Library in order to make things work without success.

    Can this code work in Excel ? Thanks!

    ItemsSelected is not valid property recognized by Excel for ListBox, it's only valid for Access. I tested setting a reference just like you to Access and it doesn't work still, so I guess it cannot be used. There is no shortcut to avoid looping through each of the ListBox items, to determine whether an item in a multi-selection ListBox was selected or not.

    I also made a Google search on it just to make sure and no results whatsoever: ".ItemsSelected(0)" Excel -Access

    Re: Macro to transfer data as values to another sheet based on the result from a form

    Quote from bpiroma;628796

    one more question though, is it possible that the trigger text "Closed" not being typed but a result of a formula?

    Of course it's possible. In the file I attached, it's a result of a formula in the 4th row that moves the entire row to sheet "Closed". To keep the code being triggered just by the result of the formula remove Private Sub Worksheet_Change from the sheet module and make sure that all the corresponding cells in column A have formulas which can give the result of "Closed".

    Re: Macro to transfer data as values to another sheet based on the result from a form

    See in the attachment how I placed the code in the correct modules. I added a comment with the instructions what to change exactly in the sheet when you test that the code works properly. You should not have problem testing the file.

    Re: Macro to transfer data as values to another sheet based on the result from a form

    Hi, to be able to trigger a macro with a result of a formula you need to utilize the Calculate sheet event. I assume you have a designated range with formulas so modify the code as required. In the code below it checks for any formula results in range A1:A10 each time the sheet calculates.

    Put this in the sheet module:

    And put this in a regular module (notice that now the row to write in sheet Closed is more dynamic, you can revert back to A20 if you wish):

    Sub MoveRow(RowNum As Integer)
        Dim WriteRow As Long
        WriteRow = Sheets("Closed").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets("Closed").Rows(WriteRow).Value = Rows(RowNum).Value
    End Sub

    Re: Comparing worksheets

    Your formula was totally scrambled... The formula in cell J11 should be:

    =INDEX('Rebated Costs'!$A$2:$B$10,MATCH(E11&"*",'Rebated Costs'!$A$2:$A$10,0),2)

    Please check the file which I attached to this message. With the current data sample, the formula will return #N/A when you pull it downwards because there's no part number in column E that matches an entry in sheet Rebated Costs.

    Re: Workaround for dragging down formulas in groups of 3

    Hi Lizzie, I transformed your 3 formulas to this general structure:

    =UPPER(LEFT(INDIRECT("'Prep Autype'!B"&INT((ROW()+1)/3)),16))

    =(UPPER(CONCATENATE(LEFT(INDIRECT("'Prep Autype'!E"&INT((ROW()+1)/3)),LEN(INDIRECT("'Prep Autype'!E"&INT((ROW()+1)/3)))-5),": ",LEFT(INDIRECT("'Prep Autype'!C"&INT((ROW()+1)/3)),57))))

    =INDIRECT("'Prep Autype'!L"&INT((ROW()+1)/3))

    As you can see the new formulas are independent of any fixed row numbers in the cell reference for sheet Prep Autype. In the attachment of this reply, I included the steps to the formula within Sheet1.

    I added the above formulas to cells A20:A22 in sheet AUT - NO TABLE so you can simply pull it down and see that it works as you requested.

    Re: Range.SpecialCells Method Running Extremely Slow In Excel 2010

    I guess that nobody has an idea how to reply this thread...

    Anyway I would highly appreciate if someone can just run the macro within the attached file in Excel 2010 (64 Bit) and tell me how fast it works. That would already be a great help! Anyone willing to test it?

    Re: Find Date Position in Array of Dates

    Thanks for the feedback. The TRUE and FALSE in MATCH works just the same as it works in VLOOKUP/HLOOKUP. Could have used a combination of INDEX and HLOOKUP in the same manner.

    Re: Transpose data with key in the first column, merge dupes and write to other shee

    Please check how fast my code is working on 300,000 rows. My code presumes that in your data the first row is a header row. The output happens in Sheet2, but you can modify this to fit your actual sheet name. I also assumed that for each item you have 3 rows as you indicated in your sample and that the first column is column A. You can test it first on the attachment and see that it works fine.

    Re: Macro to copy two cells into one

    If the values to be merged are in the active sheet, this line should do the job:

    Sheets("Sheet2").Range("C1").value=Range("A1").Value & Range("B1").Value

    Re: Data from 4 columns to go to rows and a blank row after 4 rows

    Hi PCI,

    I tested your code on 10,000 rows, it runs for about a minute.

    The following code does the same in a second:

    This is of course good if only the values have to be transferred to column A.

    Hello, I’ve lately been struggling to speed-up a process of compressing data in cells by utilizing the Range.SpecialCells method. Everything works fine in Excel 2003 and 2007 with execution time of 15 to 20 seconds. Very recently I upgraded to Excel 2010 (32 bit) and over there the execution takes 4 minutes and in some cases even much more. I have a couple of questions in that regard:

    • Would an upgrade to Excel 2010 (64 bit) dramatically speed up the process at least to the speed I experienced with Excel 2003 / 2007? From my understanding, an upgrade should be considered when dealing with 100,000’s of rows. The range my code covers is only B20:MC3020.
    • Is there a parallel method to compress the data in Excel 2010 that I'm unaware of? I tried applying the data compression through an array which I found in MrExcel forum. The speed remains the same in any of the Excel versions.

    This is the code I’m trying to use:

    SpecialCells(xlCellTypeConstants, 2).ClearContents is needed because the cells in the range are results of formulas which are being pasted special (values only) from another sheet. The result can either be 1 or "" (double quote signs). There’s no problem with the execution of that line however.

    This is the line that causes the HUGE delay in 2010:

    .Range("B20:MC3020").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft

    All I want to achieve is to delete blank cells in between the 1’s in each column and compress the 1’s to the left as a consequence. I attached a file that contains the code and some sample data ready for a test run. I would really appreciate if somebody can test how fast it runs in Excel 2010 (64 Bit) and shed light on a possible solution to the low speed performance if an upgrade to 64 Bit won't help.