Query date range
I have a query asking for records whose field (CombinedDate) is not null.
I'd like be able to pull out only records whose CombinedDate occurs within
the next 2 weeks.
I have trouble creating expressions for dates & don't really catch when to
use DateDiff, DateAdd, or something on the order of Date()+.
Appreciate your help.
Criteria: Between Date() and DateAdd("d",13,Date())
Use DateAdd when you want to create a date.
Use DateDiff when you want to determine the difference in two dates
Use Date() + when you want to add a specific number of days o...Naming ranges as a copy of another sheet
There are two sheets X and Y in my workbook. On A, there are hundreds of
ranges named locally (i.e. names are like "X!students"). How to write a
macro to name the respective areas in Y with the same local name? For
example, if X!$A$1:$B$4 is named as "X!students", then I want Y!$A$1:$B$4 to
be named as "Y!students".
Thanks in advance.
Dim wksMstr As Worksheet
Dim wksOther As Worksheet
Dim nm As Name
Dim testRng As Range
Dim ExclamPos As Long
...Reference a cell in a named range
How do you reference a single cell in a named range?
A B C
1 13 =A1^2 =A$1^2
2 35 =A2^2 =A$2^2
3 51 =A3^2 =A$3^2
4 79 =A4^2 =A$4^2
Define the name "Alice" for column A
Then the array is
A B C
1 13 =Alice^2 =A$1^2
2 35 =Alice^2 =A$2^2
3 51 =Alice^2 =A$3^2
4 79 =Alice^2 =A$4^2
In a macro I need to use Alice, rather than A because I may insert
columns before A.
That's no problem, but I also need to refer to specific absolute rows
in the column.
I need column D to be:
A B C ...How to write a macro to modify an Excel chart
I want to "record" a macro that, for example, changes the line styles on an
Excel chart. I tried by selecting the chart, then clicking on "record
macro", manually changing the line style, and then clicking on "stop
recording". But then when I tried to use the macro it wouldn't do anything.
When I edited the macro there was no code, just the comment lines giving the
Is it possible to use the "record" feature for macros that work with charts,
or must one start with the VB editor?
leave well enough alone
Here ...How do I create a gnatt chart in Excell
I am trying to create a milestone chart with the timeline on the X axis.
Gantt Charts in Microsoft Excel
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
"Candie" <Candie@discussions.microsoft.com> wrote in message
>I am trying to create a milestone chart with the timeline on the X axis.
...Plotting a datapoint on an existing Line chart
Hi all. I have a line chart, for the sake of simplicity, lets say th
line chart depicts the garph of y = x. What I need to do now is displa
a particular point on this line, lets says (2,2). How can I add thi
point so that I lies on the line chart? Also, what I would like to d
is have a horizontal line connecting the point to the y-axis and
vertical line connecting to the x-axis. On top of that, the vertica
line from the x-axis will have the corresponding x value of the poin
(2) displayed and the vertical line will display the corresponding
value (also 2). The additional complexity is that t...Access chart
I created a chart but I need to show my total on the pie not just my legend.
In this pie shows the model number of the equipment, but it doesn't show me
how many of them I purchased and that's what I need to show in addition to
the model number.
It isn't clear what the pie chart is charting. You can set the labels to
display a number of options. Did you "play" with this property?
Microsoft Access MVP
> I created a chart but I need to show my total on the pie not just my legend.
> For exam...functions in charts
Is it possible to enter a user-defined function (that
returns an array) for values in a chart instead of
specifying a range in the worksheet?
You can specify a named range, with the formula built in. See the
Dynamic Chart examples and links on my web site:
A good example of a formula in a named range can be found on Stephen
Look for the ChtFmla.xls example. Tushar mehta has a similar example on
In VBA it's possible to assign an a...2007
I was hoping someone could help me please. Here's what I have. A
worksheet that has several dates in a row. D1:T1, Of that range, I
have conditional formatting set to highlight a date(s) either = to or
within the next 7 days.
I need to be able to pull the date next closest or equal to today.
Can anyone help me out?
Any help would be greatly appriecated.
> I was hoping someone could help me please. Here's what I have. A
> worksheet that has several dates in a row. D1:T1, Of that range, I
> have conditional formatting set to highlight a date(s) either = to or...How do I make a pie chart with 42 answers that shows % each out of
I want to graph survey responses where peolple could pick more than one
answer. So there would be some overlap in the pie chart, is this possible?
Please give us more details,
> I want to graph survey responses where peolple could pick more than one
> answer. So there would be some overlap in the pie chart, is this possible?
...Creating Dynamic Range
I have created a dynamic range for an excel 2000 spreadsheet that I reference by using a Data Validation List...
The dynamic range almost works, except that is leaves whichever is my last entry to the list off, of my "drop-down" box... So anything I add to the list whether is is several or a couple, will show up in my drop-down box except the very last entry....
My list that I am creating the dynamic range from runs from cell
A3 thru A20
The formula for the dynamic range I have is:
Can anyone recognize something that might be wron...Autoshapes within a chart
I want to place a transparent box behind the bars of a bar
chart to denote a highlighted section of a timespan. I can
get the transparent autoshape in the chart but I can't get
it to move behind the bars. Can anybody help?
I don't think you will be able to do that.
Instead have a look at Jon's example of quadrent area.
You should be able to adapt this technique to give you a highlighted
> I want to place a transparent box behind the bars of...how to copy sheet with charts without link to original data
I have a worksheet with ten charts on it. I need to copy the worksheet with
all its data and related charts to new sheets or files so I have a page for
every day, with different data entered every day. But when I copy the
worksheet with embedded charts and paste it into a new file, the charts pull
their source data from the original worksheet, not the new one.
The source data Value box is written as such, ='03-09-10'!$D$12
Probably wont help you, but I had similar problem, I posted few days
ago. Didn't resolved it yet, but Jon suggested ...Chart/Graph colours
My graph colours have all changed to a wishy washy mix of the same colour. I
know updates happen on a regular basis, but how can I get back to selecting
nice bold colours for my charts without having to revert to selecting each
data series and recolouring them individually.
Try to reset the colour pallette
Tools > Options > Select "Colour" Tab > Reset
"redford01" <firstname.lastname@example.org> wrote in message
> My graph colours have all cha...Compile error on range...
I get a compile error on line 21 below. What is wrong with it?
I am trying to color fill a cell based on the XColor value...
0 r = 1
1 Do While ActiveSheet.Cells(r, 1) <> ""
2 str1 = ActiveSheet.Cells(r, 1)
3 str2 = ActiveSheet.Cells(r, 2)
4 comp1 = strip(str1)
5 comp2 = strip(str2)
7 If (comp1 <> comp2) Then
8 XColor = 5
10 XColor = 25
11 End If
13 If (XColor) Then
14 Range("ActiveSheet.Cells(r, 1):ActiveSheet.Cells(r, 2)").Select
15 With Selection.Interior
16 .ColorIndex = 3
17 .Pattern = xlSolid...Can't edit charts
For some reason, I can't edit any of the charts in an Excel 2007 file. I
can't left-click or righ-click.
Is the file "shared"?
Are sheet grouped together?
Have the sheets or charts been protected?
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
Jon Peltier, Peltier Technical Services, Inc.
"Anawynn" <Anawynn@discussions.microsoft.com> wrote in message
news:FAB71D11-1CE5-4175-B4FF-BD9292C187A3@microsof...How Do I Chart This in Microsoft Access?
I have the following simple data:
Company Monthly Sales Percentage OF Total
Bakery $50,000 0.50
Carpenter $25,000 0.25
Plumber $25,000 0.25
I would like to create some sort of a graph or chart in Microsoft
Access that will show all three fields. When I try to create a pie
chart or column chart it just gives me either the "company and t...using the range method
i want to select certain columns and rows and use this
selection to create a chart. The selection is in a loop
and the cell locations will vary every time it is run.
Here's my code:
Worksheets(1).Range(Cells(startRow, 16), Cells(endRow,
where "startRow" and "endRow" are integers.
I always get the error that "Method 'Cells' of
object '_Global' failed" Am I using the wrong format for
the Range method?
thanks in advance
J.E. McGimpsey wrote:
>or the much more efficient:
> With Worksheets(1)
> ...How to enlarge chart size for viewing?
I have some charts that I created on a sheet itself. However, the chart did
not occupy the whole sheet instead only a small portion in the centre which
is abit too small to view. How can I resize it?
If you move the cursor over one of the square black blocks on a side, or in
a corner, of the chart, the cursor will turn to a resizing arrow.
"GOH" <GOH@discussions.microsoft.com> wrote in message
>I have some charts that I created on a sheet itself. However, the chart did
> not occupy the whol...Efficient Chart Macro
I am using the below procedure (copy from Record Macro) to generate 1
graphs, each graph has 4 rows X 50 columns, by supplying ranges o
In my opinion, the code is not very efficient because it take quite
wait to plot and during the process of plotting a chart, it redraws th
chart many times.
Is there any codes that I can be omitted or rearranged to make it plo
Appreciate your help.
Sub PlotProgressCurve(pSect As String, pRangeDate As String, pRangeN
Dim tRangeDate, tRangeNo As String
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData S...Changing formatting on bar charts
I have a bar chart that shows turn around times for various jobs our group
has in process. The chart shows the customer name @ the bottom, # of days up
the side then the bars are for all the jobs each customer has. Here are my
problems/things I want to change: 1) the spacing for each customer is equal
whether they have 1 job or 10, can that be adjusted as needed? 2) My bars are
so narrow that you can barely see the color because of the customer spacing
issue mentioned above(I assume). How can I change the width of these bars?
...charting a project overview
Is there a simple way to build a chart to show a overview of multiple
including their milestones.
For example I have
Project A, B, C.
Each project has milestones 1, 2, 3, 4, 5, 6 each with start and end
I would like to chart these projects where each project has a bar (or
set of bars) and each milestone has a subbar (assuming no overlap).
The x axis would
be the dates.
Project1 (show bar of milestone1) (bar of milestone2) (bar of
milestone 3) ....
project2 (show bar of milestone1) (bar of milestone2) (bar of
milestone 3) ..
project3 (s...Plot R-Theta [angle (0~360 degrees) and amplitude] chart in Excel
How do you plot an R-Theta chart in Excel, i.e. a circular chart with the
independent axis (angle) in degrees from 0 to 360 degrees and amplitude as
the radial distance from the center point?
In article <CE5D63AB-12A3-4042-928A-6D6C7CA8DBFE@microsoft.com>, Wes12
> How do you plot an R-Theta chart in Excel, i.e. a circular chart with the
> independent axis (angle) in degrees from 0 to 360 degrees and amplitude as
> the radial distance from the center point?
You have to convert the data to (x,y) with the formulas x=r*cost(theta) and
y...Importing data from Access and creating statistics and a pie chart
I have an Access database field that contains the name of a part, which is
one of 93 parts. I want to be able to import this data to Excel so that I
can statistically show the numbers of each type of part sold within a
specific time frame. For instance, we sold 25 widgets last quarter which is a
15% increase over the previous quarter. The imported data would be broken
down by part name, and then converted to a pie chart. I think I know how to
limit the time frame in Access, but am lost when it comes to importing the
data and generating the statistics in Excel and creating the pie char...pivot chart/table
Using the same source date, how can I create a pivot table
and a pivot chart on the same worksheet
See the post above
> Using the same source date, how can I create a pivot table
> and a pivot chart on the same worksheet