Posts by james8427

Hi all

I use vlookup formula.

There are times where it would retur #NA.
How can i change it such that if the result is #NA, it would be numeric ZERO?

my formula is: VLOOKUP(A1,REF!$A:$C,3,FALSE)

Rdgs

Hi Rem

this has been a great help!

I am working on the formulas now. Will post back for some query later on.

Have a great night ahead.
Cheers

Rdgs

Hi all

I have attached a file below which I need help.

I have 2 worksheets: Data1 & Data2

I need Data1 to look exactly like Data2
I need the macro to be dynamic.

I have created a macro, but I dun think it works the way I want.

When I click on the button, it prompts me for heading for new row, and when I filled it up and also hit ENTER, one of the existing rows would be OVERWRITTEN, which is not what I need. I need to have an extra row instead.

I also need help to create a new row in the product list.

thanks alot!

Rdgs

Hi Roy

Thanks for taking time off to reply my post.

Pls take a look at the previous few follow up for this post.. i need help...
This post is in this thread...

posted on 7/28/2003 at 03:47 PM

hey hey...

Hi Roy,

I also need your help, i was thinking to make it more dynamic. I have attached a file with this.

I need three different kinds of InsertColumns.

You can teach me how to create multiple and I will play around with it.

If I click on Button(InsertForecast Column), it would have one prompt for heading and and one prompt for Country.

If I click on Button(InsertWarehouse Column), it would have one prompt for heading and and one prompt for Plant.

If I click on Button(InsertShipment Column), it would have one prompt for heading and and one prompt for Country.

The result would look like sheet2.

Rdgs
James

Attachment: InsertNewColumn009.xls (24kb)
This file has been downloaded 0 times

http://ozgrid.com/forum/viewthread.php?tid=5240

Hi Roy,
This one can work for one time.
The second time, it is errorneous.

I think because of this particular line:
Selection.AutoFill Destination:=Range("D3:D6")

Rdgs

Hi Roy,

I also need your help, i was thinking to make it more dynamic. I have attached a file with this.

I need three different kinds of InsertColumns.

You can teach me how to create multiple and I will play around with it.

If I click on Button(InsertForecast Column), it would have one prompt for heading and and one prompt for Country.

If I click on Button(InsertWarehouse Column), it would have one prompt for heading and and one prompt for Plant.

If I click on Button(InsertShipment Column), it would have one prompt for heading and and one prompt for Country.

The result would look like sheet2.

Rdgs
James

Hi Roy

I think I maneged to figure out after playing aroiund with your code

But i do need help on how to copy the formulae over.

And also, can you also teach me to delete away the columns?
Let say I want to delete the whole column of Warehouse A, how?

Thanks

Rdgs

James

Hi Roy

you have been a great help!!

I understand that I need to wrap the text.
But Could I actually ask for 2 InputBox prompts?

1st InputBox: Ask for heading
2nd Input Box: Ask for PLant Name? Eg: A, B,C etc. If you copy my formulas from Column C3-C6 to D3-D6, you would understand that I need the 2nd Input Box.
Cos I need it to Vlookup later On.

Do you want to copy the formula into the new column as well ?

and yes, i need excel to copy the formulae over.

Thanz

Hi all

I have a attached a filed with this.

Task:

I have a worksheet "Data", and it had category of warehouse.
there might be times where the user need to add in new warehouse in the report, hence, I would like to automate the process where user can add a new column, example in green color. I was thnking that an interface would be gd. But, I want it to be dynamic, i need excel to actually add in the new column and ALSO prompt me to enter the Headings for the column, preferably an Input Box function... hmm... bearing in mind that... i may need to add 1 or 2 or even more columns...
The data for the quantity are all VLOOKUPS from the Ref worksheet.

Anyone to help?

Rdgs
james :wink1:

Hi all

As attached is the fiile that I need help on.

Background:
You see 3 sheets: FGI, Data1 & Data2

FGI is the raw data that is being pulled out from a data base.

Data1 is the FINAL RESULT i would like to achieved.

Data2 is for the testing.
Hmm...

Task: I would like excel to automate these worksheet. When I click on a command button, it would automatically EXTRACT(COPY) data from "FGI" and Paste it in "Data2".
Conditions : The raw file is VERY raw and unformatted, hence, i would like Excel to pulled the meaningful data only. Data are those with Div = VF (Refer to Column B of Worksheets("FGI")).
Reason: The fact that there are Blank Cells and Other stuffs, Which are irrelevant for me, I would not want them, hence, just by identifying DIV = VF would be great. Cos, the final result would look something like Data1.

Another thing is, you guys may suggest:
1. I manually do it and format them.
I cant manually do it, because I am doing an automation of generation of report, and this data consist only 10% of the overall, if I manually do it, really a hassle.

2. I can record a macro with autoFilter.
Yes, that one I have tried, it works perfectly good, BUT, I thought of the functionality in the long run(as long as possible). I am creating an automation of this reports for a group of users who doesnt have any knowledge of VBA and Excel, hence I need to make it robust. If they have the knowdge, change the macros would be a piece of cake, but I have constraints here. Moreover, the format of the raw file "FGI" may change over time, BUT the data is still intact, that's why I am thinking that by identifying Div = VF would be a great help.

Conclusion:
VBA must be able to recognise the DIV = VF, and copy the whole row of data which consist of Div = VF.

For instance, Cell B3:B54 contains data that have Div = VF, it would copy the whole range and paste it at Data2.

Hope that helps

Thankz
James :spin:

Hey Dave

Sorry to trouble you.

I managed to figure the formulae and additional field out.

You have been a great Help

Thankz!

Cheers

Rdgs
JAmes :spin:

Hi Dave

Could you please explain how do you add Rounded Up field into the pivot?

Rdgs

Hi Dave

your example is great, its something I want

But I am really apologetic

I have no idea about what you explained:
"While in the example Workbook, select any of the 'Rounded Up" numbers in the Total field. Now, from the Pivot table toolbar go to Formulas>Calculated Field. Select "Rounded Up" from the Name: box.
"

Rdgs
james

Hi Dave

Thank you for the file
Well...
1. I have no idea how to add in the Rounded Up field
2. Can 1.236 be rounded up as 2 ?

Rdgs
James

Hi all

How do I use the function ROUNDUP in my pivot table? I need to round up the data.

Rdgs
James

Hi NEale

It works perfectly for excel
cos I am also working on Access as well
thought that the same function would help for Access.

Thankz alot

JAmes:wink1:

Hi

How can i used that for Access?
When i typed the ROUNDUP, they dun have the function.

rdgs

Hi all

I have a question.

I have to round UP my data(numbers). But, i have no idea what to do?

I have calculated result for cell "Number of boxes" and the result is 4.4.

I need excel to Round Up the value to 5. Any number's that's more than 0.01, needs to be rounded UP instead of down.

I tried Round(A1,0) and Round(A1,1), it doesnt work.

Pls help..

Rdgs
James
:tumble:

Hi Rem

That's great
Thankz alot

ceers
james
:yes:

Hi all

I need to create a dropdown list, showing me dates for every monday for the next one year.

I have no idea how to go about doing that.

I want this dropdown list to be shown in a form.

Hope to hear from you guys soon

Rdgs
james:wink1: