Entering Date/time Data As Xvalue For Scatterplot With Vba Arrays

Mar 31, 2007

I am trying to create/modify an XY scatterplot using VBA.

I declare local variables and point them to the chart and a new data series for the chart, such as:

Dim chartone As Chart
Dim chartseries As Series

Set chartone = ThisWorkbook.Charts("Chart1")
chartone.ChartType = xlXYScatterLines
Set chartseries = chartone.SeriesCollection.NewSeries()

I set the series data, using an array, such as:

chartseries.Values = Array(1, 3, 5, 7, 9, 11)

I can set the Xvalues to a set of dates by setting .Xvalues to a woksheet range that includes date-formatted data. (like this):

chartseries.XValues = Worksheets("sheet3").Range("m9:m14")
(where m9:m14) contains dates...

View 4 Replies


Label Scatterplot Chart: Plot Two Values Against Each Other Using A Scatterplot, And Label Each Service

Feb 13, 2008

I'm working for a local authority who have been given a mass of survey data. In this particular task, residents of each small district within our area have been asked their levels of satisfaction with a service, and how important they think that service is. I want to plot these two values against each other using a scatterplot, and label each service.

Excel does not automatically allow this so I used a very good sheet from the forums here: Attach labels with names to the points in a scatter plot. It's the top file, and works well. However, I can't seem to customise it for my own data.

Problems include:

- Excel often freezing when I try to run it
- Not all the data being picked up for the chart
- Incorrect labels being picked up..........

View 4 Replies View Related

Entering Number Arrays In A Spreadsheet (VB)

May 8, 2009

Is there any quick way of passing an array to a group of cells ?

It is easy enough to place an array of numbers in a spreadsheet as follows:-

View 9 Replies View Related

First Code In VBA: Use A Inputbox For Entering The First Date For Monday Of The Month Each Time I Use The Worksheet

Jun 23, 2006

I am writing a module which have a different test based on for each of the previous 2 column cells. It calculate the days passed or in simple way calculate the date difference for the 2 columns and puts them in third. But i need to use a inputbox for entering the first date for monday of the month each time i use the worksheet.
When i try using the module for each cell of the column it display the input box for each cell. is there any way so that i just enter the value in input box and it can be used in rest of the module.Without using it again and again.

View 8 Replies View Related

Entering Data To Two Files Same Time

Jan 25, 2013

I have two excel file name as A.xlsx and B.xlsx. Avoiding double work if I entering the production data in A.xlsx , need to automatically display the same data in B.xlsx. Let me know how the entered data in A.xlsx automatically display in another excel file B.xlsx.

View 1 Replies View Related

Display End Time Automatically Upon Entering Start Time And Time Usage

Dec 19, 2008

I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?

View 2 Replies View Related

Sum Data By Entering Date Range

Feb 2, 2014

I have a spreadsheet that records water consumption in L/s at 5 minute intervals. I've attached an example of the data collected during January 2012.

Is there any formula that will calculate the total amount of water consumed between a user-entered date range? For example:

From: 01/01/12 00:00 (User enters 'from date and time' in an allocated cell)
To: 20/01/12 23:55 (User enters 'to date and time' in an allocated cell)
Consumption: ______ (Formula generated result in Litres)

View 3 Replies View Related

Calculating Time Between 22:00hrs And 08:00hrs Without Entering Date?

Apr 21, 2013

How do i calculate the time between 22:00hrs and 08:00hrs without entering a date? Is this possible or do i need to enter a date?

View 9 Replies View Related

Convert Imported Date / Time Data To Date / Time Format?

Jan 2, 2013

I have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).

View 4 Replies View Related

Changing Xvalue Axes To Minimum Series Value

Jul 11, 2014

I'm trying to change 115 charts so that the Xvalue axes (Y-axes) will start on the lowest value of my data series. This is what I've come up with so far:

[Code] .....

What is the syntax to set instead of X so that all Xvalue axes is set to the lowest Data series value in individual chart?

View 6 Replies View Related

Entering A Time

Dec 18, 2008

If I use format cells and choose time... do I have to enter the time in decimals to get it to show up like 1:30 PM? Or is there a way for me to type something quicker like 130 p?

View 9 Replies View Related

Run-Time Dimensioning Of Arrays

May 6, 2008

I'm trying to create an array of strings and I thought I was doing it correct via :

Dim strProjects() As String
Dim count As Integer
count = 1
For x = ActiveSheet. Range("C65536").End(xlUp).Row To 3 Step -1
If CDate(ActiveSheet.Range("H" & x).Value) = Date And Cells(x, 3) = "1" Then
strProjects(count) = Cells(x, "E") ' error happens at this line?
count = 1 + count
End If
Next x

The error says the cursor cannot be identified, or Run time error 9?

View 9 Replies View Related

Entering Time On Userform?

Nov 23, 2011

If I have 3 text boxes textbox1, textbox2 and textbox3

I want ot be able to enter a time in textbox1 and then a time in textbox2 and textbox3 would give the difference. i.e. 09:00 17:00 then textbox 3 would calculate 08:00

Then all 3 times would automaically be entered in Sheet1 A1,B1 and C1

View 9 Replies View Related

ScatterPlot - Sorting By X Axis?

Oct 18, 2011

Date X-Axis Y-Axis
6/1/2009 1.25% 74.09%
7/1/2009 1.57% 73.77%
8/1/2009 1.65% 76.77%
9/1/2009 1.68% 77.90%
10/1/2009 2.42% 72.87%
11/1/2009 0.65% 71.35%
12/1/2009 0.97% 68.28%

When I plot the above data: Y-Axis against the X-Axis in scatterplot, excel plots the data points in X-Axis order (from smallest value to largest). However, I would like the graph to be plotted in the order by "DATE".

For example, the first plot (furthest left data point) will be June 2009 data (1.25%, 74.09%) and the last plot (furthest right data point) will be Dec 2009 (0.97%, 68.28%).

View 1 Replies View Related

Excel 2010 :: Insert Date And Time In Column Upon Data Change For First Time Only

May 3, 2013

I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.

Note: If the column I already have the date and time inserted before then it should give message record already have date and time.

I am using office 2010.

View 9 Replies View Related

Cell Auto-lock After Entering Time

Sep 16, 2013

I have created a time sheet and I would like to know if there is a way to auto-lock cells after time is entered so that it cannot be changed. As of right now I have employees enter the time by using Shift+Ctrl+;, so the exact time is recorded, however employees can write in whatever time they want. so I guess what I want to do is 2 things:

1- can the cell be formatted to only accept Shift+Ctrl+;, so that the exact time is entered in the cell and prevent employees from entering times they were not at work?


2- can the cell auto-lock after Shift+Ctrl+; is entered so that they can't modify the time sheet the next day if they arrived earlier than the day before?

View 5 Replies View Related

Formula Is Entering A Default Time When It Comes Across An Empty Cell

Nov 9, 2005

I'm using a formula to copy a time from one cell to another
across sheets. The format of the time is h:mm AM/PM.

However, when the formula references an empty cell, it puts in a
default value of 12:00 AM and I need it to remain blank, (just as
the referenced cell)
It's such a simple copy formula. ie:


e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

View 10 Replies View Related

Slicing And Dicing CSV Files - Involves Arrays And Jagged Arrays

May 8, 2013

I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:


. extending until the end of the data set

I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:

Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub

View 1 Replies View Related

Last Row Sum Update After Entering New Date

Apr 11, 2009

I have few sheets in a workbook and last sheet named report, I update each sheet date wise except report, problem is that the report sheet contains formulas of last rows sum, if I update today date report then I should change all formulas in reports. Kindly see attached file.

View 5 Replies View Related

Entering A Date Value Then Incrementing It In VBA

Jul 29, 2008

I am currently working on a button which does the following:

User wants to create a new sheet

Clicks button

Input box prompts user to enter sheet name

User enters the sheet name

The sheet is created and copies over formats and values from another sheet

I then want the user to also enter a date.

The date must then be places (always in "C2") then each second column after that the date entered + one day.

E.g. C2 = 01/08/2008 then C4 will have 02/08/2008
I want this entered for the whole month.

The code is below:

Sub copyformula()
Dim vsheet As String
Dim vdate As Date
vsheet = InputBox("Enter a sheet name:")

View 9 Replies View Related

Type Mismatch 13 Entering A Date

Mar 12, 2007

i want to let a user enter a date and then chk if the user entered the right format.

It is not working....In order to see if works i press entered without entering any value and a TYPE MISMATCH error msg appears.

here is my

NumberEntry = InputBox("Enter Start Date", "Start Date", "dd/mm/yyyy")
Do While Not IsDate(NumberEntry)
MsgBox "The FROM date is not a valid date."
NumberEntry = InputBox("Enter Start Date", "Start Date", "dd/mm/yyyy")
'NumberEntry = InputBox("Please enter the date (dd/mm/yyyy) FROM to work with.")

View 5 Replies View Related

Date/Time Formula: Pick Up A Date With Time Entry On A Worksheet And Place It Into A TextBox On A UserForm

Jun 17, 2006

I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the

Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
TextBox2.Value = ""
End If
End Sub

"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?

View 3 Replies View Related

Formula For Highlighting Whole Row After Entering A Date In One Of The Columns?

May 21, 2014

how to do the formula for highlighting whole row after entering a date in one of the columns.this spread sheet had over 300 rows so I can't do the conditional formatting, I need a formula for whole spreadsheet.

View 11 Replies View Related

Excel 2007 :: Use Of Month Names In Entering Date Through VBA

Jun 26, 2013

In the workbook the sheet names are month names e.g. july,august,September etc and 1st of each month is to be entered in a cell in each the sheets. By some trial and error I wrote this macro

Sub test()
Dim j As Integer, monthnr As Long, monthname As String
For j = 1 To Worksheets.Count


This worked in July sheet it is written as 7/1/2013 and in august sheet it is 8/1/2013 etc Then I shortened the macro like thlis

Sub test()
Dim dte As Date
dte = "july" & "/1/2013"
Range("A1") = dte
End Sub

It worked. Even if I use shortened 3 letter form of month like "Jul" or "aug" then also it works

But if type on the spreadsheet itself
It Gives value error.

Perhaps it works only in vba and not spreadsheet.

I accept nobody is going to use "july" & "/1/2013" instead of 7/1/2013 But this will be useful if sheet names are month names so that when we write the date we can use sheet names Mine excel 2007 and windows 7.

View 2 Replies View Related

Set Default Month And Year In A Cell For Entering Date Fields

Jul 26, 2013

I am entering data for ships arrived every month. There are about 200 records to be entered and for every record there are 4 date fields.

My question is : Is it possible to set default month (for ex = July) and year ( for ex = 2013 ) in these 4 cells so that

If I just type 16 it should be 16/07/2013. Because all my dates will be within that month July 2013.

View 2 Replies View Related

Date And Time From Data Table?

Mar 26, 2013

Okay I'm having a mind block on the best way to create a table from user supplied date and time. Once the user updates the date cell and then the time cell it will return only the data specified provided both arguments are true. This data is provided from sheet one where the data is constantly being added to.

View 1 Replies View Related

Counting Data With Date And Time

May 13, 2014

I'm trying to count how many times an action happens between a time frame on a certain date.

This is how my data looks and comes to me (screen shot of a small portion of it - there are 22876 cells to count and it's very time consuming to manually):

data by rld2m2, on Flickr

There is no way to separate the date and time with the program the data comes from.

What I'd like to do is count what how many transactions take place between 10/1/2013 12:00:01 AM and 10/1/2013 3:00:00 AM (example time frame) from the above data.

View 2 Replies View Related

Subtract A Static Date And Time From The Current Date And Time

Oct 24, 2007

I have a column of values resulting from subtracting a static date and time from the current date and time.

This means it is constantly updating, which makes it impossible to sort.

All my work depends on sorting those values, though.

View 12 Replies View Related

Converting Text Date And Time To Serial Date And Time

Feb 21, 2013

I import data from a program that exports dates and times as text. I have been successful using "text to columns" to separate the time from the date and then using =text(A1,"00:00")+0 to get the time to show as serial time but I'd love to be able to do the whole date/time string in one step. In cell A1 there is data that is general format and is in this format:

01/01/13 00001

No matter how you try to format it, it is not a date or time. For this project I need the serial number for the date/time. Any formula that will format it as date/time and then allow it to show as a serial date/time?

View 3 Replies View Related

Convert Date & Time As Text To Real Date & Time

Apr 22, 2008

I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved