Posts by ExcelsVBA

    Hi,


    The file: forum.ozgrid.com/index.php?attachment/69459/


    I need to do a few things to rearrange the data in the excel sheet so that it is easier to be processed and analysed.
    The actual output wanted as a sample is in sheet "Data_Result_Wanted"
    Attached is the excel sheet.


    • Loop to all the data in "data processing" and find the unique value in column A3 onwards and copy the unique value to a new sheet named "Data_Result_Wanted"
    • Copy the nominal and paste below the unique value of the data in a new sheet named "Data_Result_Wanted" This is the final output i need.
    • After that loop through column of actual value and copy the result below nominal.
    • Finishing that, then add some formula below to calculate the max,min,average and also std deviation.


    I have only managed to do part 1 to find the unique value via using macro recorder and filter the unit value but i have not rearranged the data yet in the result. This is in sub macro 3
    Also tried to rearranged the data using link from here and editing it but am not successful. the macro file is in sub Filter_Result


    Thanks in advance.

    Re: Checking if filtering shows some result


    Hi,


    Tried using both code of jindon and KjBox.
    Unfortunately the code can't be use as i will do auto filtering 1st.


    So both code will actually indicate that the cell is filtered although there is no result from the filtering.


    This is a snippet of my code




    The called sub




    Re: if nesting of len and Left in excel


    I think i just figured it out,


    Should just simplify it using


    =LEFT(O75,LEN(O75)-3)


    since my condition is always 3 number to the left is fixed.


    Also dividing by a thousand can also work i guess. just thought about it after i posted this.


    Thanks

    Hi,


    Am trying to execute this logic but am facing syntax error.
    What should be the correct nesting logic?


    Quote

    IF (LEN(O76) =6, LEFT(O76,3), IF (LEN(O76) =5, LEFT(O76,2)), IF (LEN(O76) =4, LEFT(O76,1))


    Searches numbers
    and determine the length the manipulate the numbers.


    The logic is cutting at the final 3 numbers and then combine it back. For the right side i can use RIGHT function but for LEFT i can't use that.


    Example 1:
    109384


    I will need to convert to
    109.384


    Example 2


    85162
    I will need to convert to
    85.162

    Re: Checking if filtering shows some result


    Hi,


    Would like to simplify it even further to just show RANGE "C4" is actually showing any filter result or not.
    Is there any filter result shown?


    Modified the code above to suit this case but am not able to think of the shortest code to determine cell currently filtered or not.



    Snapshot and supposed result shown


    [ATTACH=CONFIG]64668[/ATTACH]


    Result should be
    Filter applied at RANGE(C4)


    Then another snapshot if filtering not applied


    [ATTACH=CONFIG]64669[/ATTACH]


    Result should be
    RANGE(C4) No filter currently applied

    Re: Checking if filtering shows some result


    I think i need to rephrase it a little as to not cause more confusion.
    Maybe the code i posted above caused some confusion.


    The filtering criteria is 5500.


    Let's assume that i already apply a filter criteria and it is currently showing filter of operation 5500.
    Now i would like to check whether the cells(4,3) is currently filtering and showing all operation 5500.

    Re: Reference code of macro from another worksheet


    Quote from royUK;742597

    Why have you got a non event macro in the WorkSheet module?


    Attach example workbooks


    Hi,


    Shortcut key for macro is
    CTRL+Q


    Using shortcut key at MACRO FINETUNE is fine.


    Using at Book1 causes error.


    Both macro supposed to use at YIELD Worksheet.


    forum.ozgrid.com/index.php?attachment/64652/
    forum.ozgrid.com/index.php?attachment/64653/

    Hi,


    How do i check whether filtering have result or not.
    Would like to do multiple looping and check whether there are filtering being done. If filter shows result then run code if no result then go to next filtering.


    Continued from this thread
    http://www.ozgrid.com/forum/sh…p?t=193908&goto=newpost**


    This code is working, but would like the filtering to be outside.

    Hi,


    Am having an issue where i want to run a macro but am unable to run it due to my code referencing it to the active worksheet.


    The case is:


    Open workbook is
    Book 1
    MACRO FINETUNE (This is where the macro code is located)
    -Having a code inside this MACRO FINETUNE workbook named inside a worksheet named "FILTERING CRITERIA"


    Having the error subscript out of range.


    This is the code snippet where this error happen.


    Code
    Cells(4, 3).AutoFilter Field:=3, _
    Criteria1:=Array(Split(Worksheets("FILTERING CRITERIA").Range("A2").Value, ",")), Operator:=xlFilterValues


    Am running this code using shorcut key from Book1.
    How do i reference the code of the macro worksheet?

    Re: Filter Data And Pick Up Criteria of hardcoded array From The Spreadsheet using VB


    Hi,


    What if the filtering is done outside n confirmation is only done to confirm whether filtering is there or not?


    Current code edited that is working


    Once i want to loop to another instances using this code.
    Is there some way to simplify and shorten this code?


    Re: Filter Data And Pick Up Criteria of hardcoded array From The Spreadsheet using VB


    It worked.


    Thanks.


    Forgot that i can use split instead.
    Can you explain the Array that you used. Is it not necessary to have

    Quote

    "

    when declaring fixed array?


    Quote

    Array(Split(Worksheets("FILTERING CRITERIA").Range("A2").Value, ","))


    The output from that array function is what? Assuming the text is 5500,5700

    What i need to do is to grab data from a cell where there are hardcoded array and filter the list.


    Am not getting the filter result as needed and getting empty filtering instead.


    The data in my cells are
    "5480", "5500", "5700"



    Below is my code snippet which is not working


    Code
    Cells(4, 3).AutoFilter Field:=3, Criteria1:=Array(Worksheets("FILTERING CRITERIA").Range("A2").value), Operator:=xlFilterValues


    the hardcoded code in VBA is working fine


    Code
    Cells(4, 3).AutoFilter Field:=3, Criteria1:=Array("5480", "5500"), Operator:=xlFilterValues



    Am related to this thread imo
    http://www.ozgrid.com/forum/sh…58270&p=741961#post741961

    Re: Filter Data And Pick Up Criteria From The Spreadsheet


    Hi,


    Am continuing this thread as i am trying to do something similar here.
    What i need to do is to grab data from a cell using array and filter the list.


    Am not getting the filter result as needed and getting empty filtering instead.


    The data in my cells are
    "5480", "5500", "5700"



    Below is my code snippet which is not working


    Code
    Cells(4, 3).AutoFilter Field:=3, Criteria1:=Array(Worksheets("FILTERING CRITERIA").Range("A2").variant), Operator:=xlFilterValues


    the hardcoded code in VBA is working fine


    Code
    Cells(4, 3).AutoFilter Field:=3, Criteria1:=Array("5480", "5500"), Operator:=xlFilterValues