# Posts by ExcelsVBA

Hi,

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

## Files

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

## Images

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

Thanks.

That solved my issue.

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.

## Files

Re: Checking if filtering shows some result

Would like to filter first and then check whether it returns any results.
If yes do something.
Else go to next sequence.

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

Hi,

Another question i need to ask is.

How do i check if the filtering returned any result?

I would like to do this.

If filtering is running then run program,
If not skip until next sequence.

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

## Files

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``