Posts by james8427

    Hi all

    I have a question.
    I have attached the file below.

    Range A3:E4 are the raw data that i actually got from the database.

    Notice that the data for QTY SOLD & QTY ON HAND, are in certain format.

    I went to ... Edit>>Replace...
    Find What >> ",000"
    Replace With >> "i left this field blank"

    Excel run through the data and replace all data with 0,000 with blanks, but i realise that data with 261,000 is actually 261000. It doesnt trim off the back ,000. This caused my data to be inaccurate. Data supposed to be read as 261 is default read as 261000.

    Could this be solved?
    Thank you for taking time off to read this.


    Hi all

    I have a questions again.
    I have attached a file with this.

    I need to delete only data from sheet one when i run the macro called.. "Delete Sheet1".

    I realise that which ever the worksheet i am on, the macro will delete that page's details. For example, I am on sheet 2, when i click the macro named "Delete Sheet1", it actually deletes details in sheet 2 instead of 1.

    I need Excel to be sensitive enough to delete Sheet1's data instead of others.

    BEaring in mind that data in other sheets are important. It is also good that the user doesnt need to MANUALLY go to SHEEt1 to activate the macro.

    Could anyone help?

    Cheers... thank you for taking time off to read this. :)

    Hi all

    I have a question regarding pivot tables.
    I have attached a file along with this.

    There are actually 3 data fields; Product, Region and Quantity.

    I would like to put the information onto a pivot table. Appended in the attachment is only 1% of my data, actual size would be about 5000 records.

    The data changes weekly.
    (1) I would like to create a macro that idendify the range of data and then, create a pivot table. Just a click at the command button would actually activate this macro..


    (2) I am wondering if the data changes, would the pivot table to sensitive enough to change as the data in the range changes? would it be dynamic?


    Hi all

    hehe... i have a question below.
    I have attached a file with it as well.

    The scenario...

    I get raw data from the database, the numbers that i get is not regular, as such, i couldnt compute my total quantity.

    Only correct number format works well.

    is there any procedure that i could do to change the numbers so that i could compute my totals?

    In this case, the irregular number format is 0,000. I tried trimming the code with =TRIM() but i guess its irrelevant.

    Thank you for taking time off to read this.


    Can someone help me with a procedure that could clear all the spaces in a cell, but keeping the word.

    I have attached an example of what i mean.
    In the file, Cell A1 is the word with extra spaces at the back, i want the word to be no spaces at the back of the word.

    In this case, the word is CAKE, i wan the word to be CAKE ... with no spaces after the letter "E".

    Could there be a VBA procedure to do that?

    Bearing in mind that I have a whole column of words with spaces to clear.

    Hi all.

    i have encountered a new problem again.

    This is a piece of raw data (Range B:C) that I need to pull out from the database. I created 2 columns, mainly CODE and REGION. Code is a combination of ENTITY&DEPOT&SUBREGION. Region is generally a VLOOKUP function used to lookup a value from CODE and return me a value from Worksheet ("REFERENCE"), column 5. The problem now is that, I couldnt derive my result for region although the CODE for Worksheet"Result' and "Reference" looks the same. Then, i realise that the problem is... the data generated from the database leave a FEW SPACES on the SubRegion in Worksheet 'Result". Now i need help in clearing the space for my Worksheet ("Result")'s Sub Region field. Bearing in mind that the data for Worksheet ("Results") keeps on changing, but the columns is the same. The Worksheet("Reference") only serves the purpose of reference, it doesnt change. Hopefully there is a procedure that could help to clear the spaces.

    Thank you for taking time off to read this.
    Hear from you guys soon. :)


    Hi all

    I have a question regarding finding out blank cells in my project

    I have attached a file.

    Excel couldnt help me to determine the blank cells.
    I need to assign the blank cells with a name:" others"

    I have created a macro to search for that, but the result returned from Excel was that there werent any BLANK cells.
    I have highlighted in GREEn for the blank cells.. and have no idea why Excel couldnt find.

    is there any code to solving that?

    Thankz a million

    I am not gd a excel VBA, but hope to master it... :) cheers

    Hi Brandtrock

    I dun rreally get what you mean... hmm...
    Basically I need Excel to recognise the data, ok, Offset may have be able to do that. But i need a macro to paste all the data together as seen on the "Pivot" worksheet, bearing in mind that the data should be continuous after consolidating the data from Data1 and Data2.

    On the other hand, i don't get to see the countries in details from the pivot table.

    Hi all,
    i have a scenario here. I have attached a sample file.

    The background of this spread:
    I have 3 worksheets, named Data1, Data2 & Pivot. I have actually created macros to format my raw data to derive at Data1 & Data2. In this file, the macros are not attached. Let us just assume the data is formatted and ready. Now, I need to analyse the consolidated data from Data1 & Data2, hence i would need to manually cut and paste the data and paste it at Worksheet("Pivot"), and allow the pivot tool to create a pivot table for me.

    I created a Macro to actually select the sensitive range in Data1 and Data2, bearing in mind that my data every week for Data1 & Data2 keeps on changing. The macro i created doesnt work, as nothing get pasted to my Worksheet ("Pivot"). The Macro named UpdateData is created in this worksheet, you can take a look at it.

    what needs to get done?
    1. Need to get excel to recognise the data range (for Data1 & Data2) and copy and paste it to Pivot worksheet. The function must be dynamic in the sense, it is clever enough to detect the ever-changing range every week.
    For example, my data range for Data1 for week1 is from A1:C12, and data range for Data1 for week2 is from A1: C20. Hence, excel must be able to recognise the changing range of data. That applies the same for Data2 as well.

    2. Notice that there are 2 worksheet that i would get my data from, Data1 & Data2.
    After Data1's data is being pasted onto Pivot worksheet, in this case, refer to the attachment, WorkSheet "Pivot", Excel should be able to recognise and identify A14 as the cell for Data2 to be pasted. Bearing in mind that the data changes everyweek, excel must be clever enough to detect that cell for the next data to be pasted.

    How do i go about doing it?

    I hope this is concise enough.
    Hear from you guys soon. Thank you for taking time off to read this. = )

    hey all, thank you.

    I think I shall keep to the spreadsheet itself, would not work on the user form.

    Wll be back with more queries later on. Cya pals.

    Thanks a million. Ozgrid is the place to be. :sing:

    Hi all

    I have a question regarding user interface. Should I use the spreadsheet or the Form for user interface?

    I am stuck here, in evaluating the choice. I understand that both the spreadsheet and the Form allows me to create command buttons and list box etc. But what are their competencies?

    Currently working on a project using Excel as my base. Need to import and format tons of data before i could actually perform logic and formulaes. and the presentation of the results is very huge. So any gd suggestions on whether woiuld a spreadsheet or form? i am more into reverse engineering, meaning analysing data structure, on the other hand, also designing the GUI.

    have a ncie day.