Posts by HestiA

    Hi all,

    I have yet another burning question, this time regarding sumproduct. I am having problems getting my sumproduct formula to work, as they would show me a #value error when my referred range has null values.

    Here is my sumproduct formula:


    I am using a two dimensional sumproduct formula, which helps me detect which data belongs to that particular year (matching the year in D3) and sum up all those values.

    Attached is a similar version of my main working file, however, i use index and match in my main working file to reference the data instead of isblank.

    Is there anyone that could help me with this? Sorry if the explanation is rather bad.


    ~ Hestia

    Hi all,

    Sorry I got it solved. Turns out that I was using a cursive " " for the lower half of my codes, so half of my codes did not register and copy pasted the data based on cell destination rather than finding the correct reference.

    Here is the code if anyone needs it.

    Have a nice day! (p.s. will stop spamming this thread alr haha)

    ~ Hestia

    Hi all,

    I tried following a video online using this code:

    However, this only copies and pastes the row of data in the exact cell destination on the next sheet.

    Is there anyone that could help me with this please?


    I think my explanation is not that clear, hence here is a more detailed explanation regarding my question.

    I have 6 rows of data drawn from a web query, and pasted into the "working sheet". These 6 rows include dates in the format of (mmm-yyyy) in column A, different countries in each column and values belonging to the different countries.

    In the "copy paste sheet", i have historical data which follows the same format as the working sheet.

    What I would like to do is:

    If any of the dates for the six rows of data in "working sheet" = any of the dates in "copy paste sheet", then copy all of the row's data (starting from column B) from "working sheet" and paste it onto the row with matching dates in "copy paste sheet". It is like a vlookup but using vba, and i am not sure how to code it as well.

    However, there might be new countries in the columns of working sheet. Besides showing the number of new countries, I would like the macro to show which are the new countries.

    Hopefully this is a clearer explanation.

    ~ Hestia

    Hi Carim,

    Yes I tweaked the codes to my settings and now it works perfectly!

    Here is how it looks now:

    However, I would also like to include a code which detects which is the new column of data and paste the data accordingly to the dates. Is it alright if you could help me with this too please?

    Thanks for your help again!

    ~ Hestia

    Hi all,

    Here is the file that I am working on currently.

    I would like to actually copy data from cells J18 to O73 and copy them according to the months on J14:O14 and also based on countries on column B in "Data Draw sheet". Then I would like to paste them accordingly into the "Working Sheet" (eg: Apr-18 Argentina on cell C221). I have already done a macro to copy and paste the values i needed.

    However i am not sure how to detect if there are any new countries added in column B, then show a notice of the countries that are new.

    Is there anyone that could help me with this?

    ~ Hestia

    Hi all,

    I have yet another burning question regarding macros this time. I am trying to build a macro which detects a new row of data added, which are data drawn from a web query.

    This web query draws rows of data based on countries, and there might be new countries each time it is refreshed in the workbook. I would like to use my copy and paste macro to paste country data into another sheet in the same workbook. However, my copy paste macro only pastes data based on the top row countries (eg A1 = Argentina), but does not detect if there are new country data drawn from the web query.

    Is there anyone that knows how to build a macro which detects the new country row?

    Also, I would like the macro to show a notification to tell the user that there is a new country found (e.g. Country A is new, please insert a new column).


    ~ Hestia

    Hi all again,

    I have yet another question regarding index and match formulas. I would like to draw a set of data based on the year and month of the date data.

    I am not really good at explaining this, so i have attached a sample file of what i will like to do. I will like to use index and match to draw data from the volume column in sheet 1, and it is based on the date column in sheet1 as well. Then i would like to have this data displayed on the monthly table in sheet 2.

    Is there anyone that can help me with this?