Database


?

:)

Hope this is not a duplicate thread by me as got problem to upload my
question earlier.

Faced with this for a while. I have the following data:

A B C D E F G
H
1 Date Price Date Price Date Price
2 3-Jan-05 1.2590 Jan-03 0.8480 Jan-03 0.8480
3 4-Jan-05 1.2590 Feb-03 0.8330 Feb-03 0.8330
4 5-Jan-05 1.2590 Mar-03 0.8200 Mar-03 0.8200
5 6-Jan-05 1.2590 Apr-03 0.8330 Apr-03 0.8330
6 7-Jan-05 1.2590 May-03 0.8810 May-03 0.8810
7 10-Jan-05 1.2590 Jun-03 0.9650 Jun-03 0.9650
8 11-Jan-05 1.2260 Jul-03 1.0430 Jul-03 1.0430

Column A and B are growing everyday, except weekends. D is derived from A by
=EOMONTH(D85,1) and E =LOOKUP(D86,Sheet2!$A:$A,Sheet2!$B:$B)

I created 2 command buttons (Daily, Monthly) to get date in G. Here is the
problem: how do I get the price in H? I have tried using the same method I am
using to get G but as the macros is written to extract unique values, it
works for date but not price.

'---Start of Code-------
Option Explicit

Sub PullUniqueData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=True
End Sub
'---Start of Code-------

As the final data is use to create a chart, I would prefer not to use
formulas and drag in H, otherwise for monthly chart it will have a long list
of empty values
 
Ad

Advertisements

?

:)

I have solved my earlier issue, with just a minor obstacle to go. Would
appreciate if anyone can help on this. I have change my macro for the daily
and monthly command button.

Daily:

Sub prime()
Set myrange = Range("Date", "Price")
For Each f In myrange
f.Offset(, 8).Value = f.Value
Next
End Sub

Monthly:

Sub prime2()
Set myrange = Range("D2:E200")
For Each f In myrange
f.Offset(, 5).Value = f.Value
Next
End Sub


Now, the issue is that as my daily date is in the format of dd-mmm-yy and
the monthly is in mmm-yy. Is there anyway so that the end result when I click
the daily button, the date format is as dd-mmm-yy and monthly button will
show the date in the format of mmm-yy?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top