Posts by FaKest

    Whilst you have declared these as public at the top of Module "Test"

    Code
    1. Public country_name As String
    2. Public shape_name As String

    You have also declared them again within some of the procedures. Make sure you remove the declarations from those procedures.

    Fuff13, you are totally right.


    Thank you so much.

    :)

    When the excel user clicks on the shape with a country name a userform will popup and the excel user can choose 2 buttons to change the shape color and the the string; However, this same code works fine in another workbook, but for this workbook I am getting variable not defined and error 1004 (application-defined or object-defined).

    Maybe someone can tell why this code is not working for this workbook??

    The first code is to change the shape color for green and add KYC in front of the country name.

    the second code is to add numbers in front of the country name and to change the shape color.


    Would testing the Start tab columns CV and CW give you the results you want?

    I Tried to and I created a new part for the code, however it is taking the information from the wrong column and I dont know why. 13.trytryagain.xlsm



    The criteria I have for this code to run and add "Consider" in the column C are:


    If consumer meets one of these rules, value should be set to Consider:

    • Consumer has only 1 Transaction -- (is done)

    • Consumer has 2 - 4 Transactions but total volume < 10,000 USD --- (is done)

    • Consumer Level (based on rule below) is Level 2 or Level 3 --- ( this information are on column CV and CW)

    • If dropdown is 60 Days and max transaction date is older than 30 days

    • if dropdown is 1 year and max transaction date is older than 90 days

    • If dropdown is 5 years and max transaction date is older than 180 days


    It seems that my code is looking in the wrong column. ex:
    client number 3 are on columns H and AJ (so for this client need to check the column CV when checking the lines where he is on column H; and CW when the client number are on column AJ (if client number 3 will be Level 2 or Level 3 on column CV or CW then it need to have a consider). However, if you filter the column H and later the column AJ the client number 3 level will always be Level 1.



    The level for column CV is when the client number is on column H

    The level for column CW is when client is on Column AJ

    Hello all,


    I managed to continue the code and to add more criteria to it.


    What I am missing is to link the consumer Level with the last date of his/she input.
    60 days and the maximum input date is older than 30 days and the consumer Level is 2 or 3

    1 year and the maximum input date is older than 90 days and the consumer Level is 2 or 3

    5 year and the maximum input date is older than 180 days and the consumer Level is 2 or 3


    any advises how to do it?


    thank you

    I need help to populate the Column C on sheet named Interdiction Review with a string "Consider" (need to run the code for sheet interunion review be created)

    the code is placed on Thisworkbook


    on Column C need to be populate with a string "Consider" is:

    - consumer has only 1 input (this is done)

    - consumer has 2 to 4 inputs and total volume is <10K

    - consumer Level (based on the rules below) is Level 2 or Level 3 (columns with levels values are CV and CW from Start sheet)

    - If Review Period is = 60 days and the maximum input date is older than 30 days -- (review period is a dropdown on sheet named SStart, input dates are located on columns E and AG on Start sheet)

    -If Review Period is = 1year and the maximum input date is older than 90 days --- (review period is a dropdown on sheet named SStart, input dates are located on columns E and AG on Start sheet)

    - f Review Period is = 5year and the maximum input date is older than 180 days --- (review period is a dropdown on sheet named SStart, input dates are located on columns E and AG on Start sheet)


    The problem is how to populate the column C taking in consideration so many criteria in a way that the code will not transposes each other .

    I tried to use excel formula on VBA, however I cold not get the result I wished.

    what would be the best way to do it? how should I start?


    7exemplo. xlsm (1).xlsm



    for - consumer has only 1 input (this is done) criteria the code used was:



    not sure if I could use the code above for all the criteria at the same time.



    Here is the answer for my question.


    And it is possible to use a excel a formula as well.

    Assuming you do not have more than one year of data in the table, given the workbook you downloaded at the other website, for the Amount average for the previous six existing months:


    =AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))

    If you might have more than 12 months in the Pivot Table, then you need to check for the year also:

    Code
    1. =AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0})),"Years",YEAR(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))

    Correcting myself

    The rule for the semester would be:

    The semester start in the month we are now (like April) and goes back 6 months. Apr, Mar,Feb,Jan,Dec,Nov (it would be the months of the last semester and from this months take the Active months in the pivot) and The active month would be Mar,Feb, Jan 2020 & Dez 2019

    Hello,

    I need a code that will count how many months was active in the past semester.

    I have a pivot and the data changes all the time when the pivot is refreshed, However, I need to calculate the last 6 months average but only using the months that was active in the last semester. e.g
    2020 2019
    Jan May
    Feb Jun

    Mar Jul

    Aug

    Sep

    Oct

    Dec


    in this case I would take the average for the last 6 months only using 5 months (2020 Jan, Feb, Mar & 2019 Dec, Oct) because it is the months there person was active in the last semester).

    I have this code for the last 6 months in a row



    But I am guessing I need to use IF the last 6 months is = 6 active months in a row then go to SUB Sub Last_6_Months_Average()
    else
    But IF the last 6 months is < 6 active months in a row Then count the number of active months in the last semester and select the total amount of this active months and do the average of the values.
    cells(number of active months, 3).value ="Average"


    Many someone can help me with this code?

    Hi all,

    Not sure how to go about these formulas I'm working on with the data I have been given.


    I have this formula in all my C column and when I don't get a correct date result it instead to be blank will show the date 1/0/1900. The problem is that I am using this column C to create a pivot and the column C is the Pivot's date, however I need to get rid of this year 1900


    =IF(COUNTIF($B$1:B2,B2)=1,A2,0)


           any ideas how I could get rid of it or maybe replace for the word Blank?

    Hello,

    The excel user will export the data from an online website to excel (12 months data), so the data will be all the time different. So the formula would need to be dynamic.


    Want to calculate the past 6 months average (However, the calculation need to use the months I have in the data, and sometimes there will be less than 6 or 12 months, or it may be like Jun, Aug, Sep, Dec, -2019 & Feb, Mar, Apr 2020), but I still need to get the average and frequency for it. I am trying different way with this formula



    =IF(MONTH(MAX('12 Months'!A:A)-MIN('12 Months'!A:A))>=6,COUNTIF('12 Months'!A:A,">="&EDATE(MAX('12 Months'!A:A),-6))/6,AVERAGEIF('12 Months'!F:F,">="&MIN('12 Months'!F:F))/MONTH(MAX('12 Months'!F:F)-MIN('12 Months'!F:F))) ''' this well, I simple dont know. :D


    and


    =AVERAGEIF(('12 Months'!A:A),">="&EDATE(MAX('12 Months'!A:A),-12),('12 Months'!F:F)) ''' this calculates as full calendar year and not only 6 months on my data.


    However it doe snot give me the correct Data.



    Ps: I post a similar question on https://stackoverflow.com/ques…st-12-and-6-month-average