Posts by james8427

  • VBA: Need help on some date functions...

    Hi all


    I have attached an example with this posting.


    In the atachment, you will find 3 worksheets:
    1. September Date Codes
    2. October Date Codes
    3. November Date Codes


    I just need excel to automatically populate dates which are lesser/smaller than TODAY to be current month's name.


    Example:
    Today's Date: 10 September 2003
    In excel spreadsheet, i have dates >> 21 August 2003.


    21 August 2003 (is lesser than) 10 September 2003, hence, the name for 21 August would be September.


    Notice, in the example: I am starting with the current month, September. I have 2 more examples for the next two months.


    I need excel to automatically populate these data for, i dun wanna manually change the names for the dates.


    Is there any way?


    Rdgs
    james

  • VBA: Need help to automate filling of data based on DATES.

    Hi All


    I have a problem. I have attached the problem.


    You will find 3 spreadheet:
    1. data
    2. result.
    3. Next Month



    Background:
    Everyweek, i need to generate this report and i intend to automate it.
    Th raw data is is worksheets("data").
    I need to make these raw data meaningful, hence, needs to feed it into worksheet("Result"). I am dealing with data of products that needs to be received in, but they have yet to arrive.


    Task:
    The issue here is, i am dealing with shipments that couldnt reach on time.
    As of today, this is month of September. I need to do projection for current month and two months ahead; in this example, there's only current month and one month ahead.


    In order to do projection for the current month and future months, i NEED the dates. It is the most crucial!


    Criteria:
    1. I need quantity based on dates before current month to be added to the bucket of current month.


    Example:
    I have data for product::apples and there's quantity for month of june and september.


    I need excel to add both (june+september) together and put it in Septmber bucket.


    Next MONTH (refer to Worksheets("Next Month"), my report would reflect October & November.


    And let's just say, the shipment for Product:: Apples, for the quantity of June and September have yet received, it would be put into the October bucket.


    In conclusion, it would add those products with quantity and whose dates are before the current month to be added into current month's bucket. BEAR IN MIND >> NO Duplicates for the consecutive month. For instance, in Worksheet("result"), you will see september and October.


    September would include those dates with quantity before the current month (May, June, July, August etc) + current month's quantity(September)


    October would include those data of the respective month. ONLY INCLUDE data that have quantity for the month of OCTOBER ONLY. This bucket would not accumulate September's data. THIS IS IMPORTANT!


    well, i used Pivot table, but i guess I need to manually calculate. At the end of the day, i need excel to automate this.


    Hope this is clear.


    Rdgs
    James

  • Need to delete off columns & rows with VBA

    I am aware that i could do that manually. But i would like to make it automated. Well... it would be too much hassle to click here and there.


    I want to automate the process.


    Just need user to input the rows/columns and excel does the rest of the job.


    rdgs

  • Need to delete off columns & rows with VBA

    Hi all


    I have a question and I have attached a file here.


    You will find 2 worksheets here:
    1. Data1
    2. Data2


    I need to delete away columns and rows.
    I need Excel to have a pop up msg, prompting user which row or column to delete.


    I need it to be dynamic enough. for instance, if the user needs to delete off Column A:F, Excel must be able to recognise that. If the User needs to delete off Row 1:3, Excel's prompt must be able to recognise that as well.
    One more condition:
    The deleting of the rows or/and columns in Data1 will result(will also have the same effect on) Data2's column or/and rows to be delete as well.


    Basically the format of the rows and columns in data1 and data2 are the same, just that the data is different.


    For example: when I click the command button to delete off Column A:F in data1, when executed, the column A:F in data2 will also be deleted concurrently.


    I wish to have two command buttons. One for deleting the rows, and the second one for deleting the columns.


    pls help...
    Thanks alot...


    rdgs
    james :tumble:

  • VBA: Need to run background job

    Hi Wil


    The program did work quite well.
    But i realise, it didnt meet what i need.


    I need serious help.


    In this spreadsheet, when Excel do updating, it should update based on concurrent:
    1. Week ID and
    2. Product


    I tried to leave this week's data with only 2 weeks data, end it ended up working wrongly. It seemed to overwrite my existing data.


    My criteria would be:
    1.Quantity WITH reference to (date ID and product Id) of ThisWeek is blank, LEAVE ExpectedResult alone.


    2. Quantity WITH reference to (date ID and product Id) of ThisWeek is <> ExpectedResult would be = ThisWeek.


    One suggestion, i hope excel look for both horizontal(date ID) & vertical(productID) to ensure that the integrity of data is correct. I have no idea how to do, this is my suggestion.


    I need this to be dynamic, there would be times where I would not have data for apples, and hence, the row for apples would not appear.


    Can you help me look at my codes and see where i could improvise?


    I seriously need to update based on Week ID and Product.


    In my work, instead of cells, i used Pivot table, is it alright?


    Rdgs


    I have attached the file.