Posts by luzmen


    Note: I used selected range instead of entire range of the cell because it will take long time to finish the command.


    Hope it helps (i did not test it).

    Hi & Hello:


    I have a list of data that i wanted to sort for Top amount. I can create it with a formula base on the number of items to be listed in the Top, however, the formula is only for individual items. I want a report that list even duplicate items and aggregate it to calculate for Top rows. I attach sample file for easy reference.


    P.S. If it is possible with formula, its better...


    Thank you in advance.

    Hi everyone,


    Is there any formulas to add spaces in a cell text value?


    Example: ABCDE result to: 1.) A B C D E (1 space in between) or 2. A B C D E (customize space in between).


    Or in short i can judge how many spaces between text value. I've googled it and found only numeric value can customize spaces in between through Format Cells > Number > Custom, can't find it with text value.


    I already thought of using =MID(B1,1,1)&" "&MID(B1,2,1)&" "&MID(B1,3,1)&" "&MID(B1,4,1)&" "&MID(B1,5,1) , but the cell value will not permanently have the same numbers of characters.


    Is this possible with formula?


    Thanks in advance.

    Hi and Hello,



    I have some compile error: Variable not defined at this code:


    The error was at Show_Maximized... how to resolved this problem?


    Thanks in advance...

    Hi and Hello,



    I have this formula in cell H2:
    =INDEX(F2,MATCH(A2,G2,0))


    where:
    cell F2 = 'C:\Users\mpc\Desktop\[DataFile1.xls]1'!B2:B5000
    G2 = 'C:\Users\mpc\Desktop\[DataFile1.xls]1'!A2:A5000


    But the result is #N/A.. I already double check the DataFile1.xls, ID codes for A2 and sheets name, all were correct..


    What is the correct formula for that?


    Thank you.

    I searched a code as follows:


    The problem is, i will specify every arrays/reference in vlookup.. I want to be like this with the above highlighted lines:
    Set Table2 = Workbooks(" =range B ").Sheets(" =range C ").Columns(" =range D : range E ")


    Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cl, Table2, =range F, False)


    I tried with the following code as stated below but no data has been transferred only appear the Msgbox "Done":

    Code
    1. Set Table2 = Workbooks([COLOR=#FF0000]Range("B").Value[/COLOR]).Sheets([COLOR=#FF0000]Range("C").Value[/COLOR]).Columns([COLOR=#FF0000]Range("D").Value & ":" & Range("E").Valu[/COLOR]e)
    2. Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cl, Table2, [COLOR=#FF0000]Range("F").Value[/COLOR], False)

    Hello,


    I have a main report worksheets that gets data from multiple workbooks thru VLOOKUP function. I don't have any problem with a formula, but in VBA code, i can't figure what to do.


    Attach are my sample files (in Zip Files coz it has 3 files). My sample files only have a little data but my real files have at least 1,000 rows per worksheets.


    Thanks in advance

    Files

    Hello,


    I have some difficulty in solving a formula for COLUMNS. I want to get the equivalent number for the last column in a given range.


    This is my table looks like:
    [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    Column where to Start

    [/td]


    [td]

    Column where to End

    [/td]


    [td]

    Formula to get the number of End columns

    [/td]


    [/tr]


    [tr]


    [td]

    G

    [/td]


    [td]

    I

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    I know the formula is =COLUMNS(G:I) and the result is 3. How do i write the COLUMNS formula that instead of typing G:I, it will locate the stated value in column A and column B.


    I tried this formula: =COLUMNS(A3&":"&B3) but the result is #VALUE!.


    Anybody knows if this is possible with formula?


    Thanks in advance.

    Re: Looping through Columns when Copying cell value to Another Workbooks


    I had made it works!


    For references to other searchers, heres the code:

    Re: Looping through Columns when Copying cell value to Another Workbooks


    The template has been protected, it can't be modified the format and inserting rows.. only the cells that you input the value were unprotected.


    The transfer-to-workbook is only my own conversion list...



    Re: Looping through Columns when Copying cell value to Another Workbooks



    Carim,


    I have notice that the template has a column with a code.


    Please see again my attached files.


    Thanks.

    Hi everyone,


    I have this code to copy cell value to another workbook at specified range, it works properly.



    Is there any possibilities that instead of coding bk.Worksheets("Sheet1").Range("B2").Value = .Range("B2").Value every cell range, it will only loop through a columns?


    Example: bk.Worksheets("Sheet1").Range(" =cell C2 of the active worksheet ").Value = .Range(" =cell B2 of the active worksheet ").Value


    I ask this situation because i have at least 400 cells ranges to fill and to lessen the codes. And also for proper monitoring of the exact cell range to fill.


    I attached the files for proper preferences.


    Thank you in advance.

    Re: Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refr


    Thanks Max161 for your reply... i tried what you say, still compile error appears and delete the row before the exact items.


    However, i had manage to correct the code and now its working properly.

    Hi everyone again,


    I have this code to display in the listbox and update it if necessary. Now, I added a command button with a code to delete the selected item in the ListBox, but there is a "Compile Error: Method or data member not found".


    Please note that the source of the listbox with a range name "FilterFR1st" is from other sheets which filtered from the raw source sheet. This is due to filtering for specific ID. Raw source worksheets name is "FR1st".


    Code where Compile Error appears

    Code
    1. ListBox1.ListFillRange = "a:a"


    Code for command button "Delete"


    Whole code of my UserForm:



    Hope i explain it clearly...


    What is the code to delete the entire row from raw source sheet with the selected item in the ListBox? I want also an Auto Refresh from the ListBox after deleting the record..


    Thank you in advance.