PDA

View Full Version : Solved: How to display information in other columns with missing information



Derry
01-03-2011, 02:01 AM
Hey all,

My question is how to display the columns in A to C to columns H to J using Excel VBA Macro. Find the attached file and the data is in sheet 1.

After reading the data, you will realise the yellow tabs which means that even if the price of the security on that date is not available, the cell should be empty or zero.

The listing of information stops at EndDate which is 17/11/2010. Assuming the price is daily basis but sometimes don't have price at certain days.

My version is Excel 2003.

Bob Phillips
01-03-2011, 02:54 AM
Try this

Derry
01-03-2011, 07:22 PM
I tried but no result being displayed. By the way, I re-attached another file with pivot table this time round. The question now is how to display the yellow colour tab row even if there is no data on that day itself. As I doing a macro that can display prices or no prices of variables on business days.

Aussiebear
01-03-2011, 08:46 PM
The pivot table is taking its data from the table A1:C13. if this contains no date information as regards to the 15th how will it know to include this as a data set?

Derry
01-03-2011, 10:45 PM
Yeah that the problem and if that the case is there other way to do it?

Bob Phillips
01-04-2011, 01:10 AM
I forgot to adjust my formula when I moved the data.

Try this

Derry
01-04-2011, 01:15 AM
Thanks Xld you posted the file already? how come I din see it in the post.

Bob Phillips
01-04-2011, 01:41 AM
Seems to be a problem on my laptop. See if this works better

BTW I hope you are not suffering in Queensland.

Aussiebear
01-04-2011, 07:01 AM
Bob, the formula is an array formula, but when I highlight it, by clicking on it in the formula bar, Excel removes the curly brackets. Why does it do this?

Bob Phillips
01-04-2011, 09:17 AM
Because highlighting it is putting it into edit mod4. The brackets are not part of the formula, but rather an indication that it is an array formula. So Excel is 'smart' (ish), it removes them for you so that you can properly edit the formula. But of course, it means that you have to put them back.

To my mind, Excel would be far smarter if it recognized array formulae automatically, so we didn't have to bother with Ctrl-Shift-Enter.

Derry
01-04-2011, 07:06 PM
Thank you for your help and concern. I'm all right no worries.