I'm making a line diagram, showing how a value changes over time. My data is measured every 30 min, which results in a mess when it's plotted over a month.
How do I limit the number of times shown on the x-axis, for instance so that only the date (every 24 hours) is shown?
I'm using the code below to form part of staff resource spreadsheet, but I'm having a little difficulty with a piece of the script.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range, res As Variant
Dim cell As Range
Sheets("Input").Protect "password", UserInterFaceOnly:=True, AllowFiltering:=True
[Code] ....
The piece of the script which I'm having difficulty with is this:
Code:
For Each cell In Range("B7:B400")
If cell.Value = "No" Then
MsgBox "If " & cell.Offset(0, 1).Value & " has left R&D, please remember to delete any future resource forecasts"
End If
Next
When the cell value has changed to 'No' the pop up message appears, but for some strange reason the message is shown 5 times, despite their being only one record.The other problem I have is if I add another record and change the value to 'No' in column B, the message is shown twice, once for the previous record and the second for the new.
I have the results of a "IF" formula. I want that number to be shown as currency and allow it to be part of a "sum" formal. Is this possible?
View 2 Replies View Related I'm working on a project where column A will always have numbers added every month. How can I get column B1 to automatically retrieve the last number shown?
Example
Month 1 Month 2
Column A Column A
1254 1254
5871 5871
5654 5654
5301
5024
Can you increase the number of fields shown in the list so that you don't have to scroll through the entire list?
View 2 Replies View RelatedI am running a macro that contains a template of a report and when it is done adding data to that report, it copies it to a new tab in the workbook, gives it a new name, and repeats with the next set of data in the blank template, over and over again.
Things work well, the code is correct. But I get to a point where the "Copy Sheet to new tab at end of workbook" doesn't work anymore. The copy sheet to new tab doesn't even work outside of the macro. Is there a limit on the number of sheets (it stuck at sheet 27) or a limit to the characters used across all sheets (my tab names can be long)?
The code I am using to copy to a new sheet is pretty simple:
Sheets("REPORT").Select
Sheets("REPORT").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = SheetName & " Data Report"
Thanks for some clarifications about this problem. I am wondering if there is a limitation in excel that I don't know about.
Is there a way to fix the the allowed numberformats in a workbook?
For example, is it possible to have only the following numberformats in a file : 0.0, 0.0% and $0.0. The file should not allow any other formats, and the user should not be able to create custom formats.
I have a macro that selects a range of about 20 columns and fills down. It works fine on my smaller set of data, but when I use one that has about 40,000 rows I get the error:
FillDown Method of Range Class Failed
It fills down about 7,000 rows, then fillsdown the first cell of the next row, but leaves the others blank. The entire range (including the nonfilled cells) is still selected.
I am using a diamond shape that is copied into the active sheet when the user runs a macro. The user then selects the shape and enters some text. I want to limit the characters to a certain number or at least be able to give the user some advice when they enter too many characters.
I have found that, as long as I have manually named the shape "Diamond1", I can get the count in my immediate window with the following:
?ActiveSheet.Shapes("Diamond1").TextFrame.Characters.Count
but I want the count to relate to the active shape. So I tried:
?selection.shapeRange.TextFrame.Characters.Count
and I get a "runtime error 438 Object doesn't support this property or method".
How should I count the characters in the selected shape, and then if it is over say 12, require the user to shorten it or at least present yield a message Box?
I want to automatically prevent users from entering more than two digits into a textbox.
i dont want to show a message box or anything like that. I want it to be physically imposible for them to enter more than two digits i.e. when they enter two digits, pressing any other keys on the keyboard should have no effect on the contents of the textbox.
How do I limit the number of digits to the right of the decimal in a calculation?
View 3 Replies View RelatedI'm trying to create a gradesheet in which any score that falls into the
failing range will automatically highlight in red...
I have a macro that runs every 15 minutes and stores data into a table. This table is attached to a chart and auto updates with the new data every 15minutes. The table will house historical data over time, however I only want the chart to reflect the last 96 entries (15min data points over 24hours). So, once i have enough data my table will be A2:A97. When A98 is populated, i want the data in A2 to be removed from the chart only.
Here is a breakdown:
A2:A97 is currently displayed on the chart.
I enter new data into A98.
The chart will now display A3:A98.
I enter new data into A99.
The chart will now display A4:A99.
I enter new data into A100.
The chart will now display A5:A100.
and so on.
How I can accomplish this? Does this need to be done in VBA?
trying to limit the number of characters entered in a cell.
I clicked on Custom then =LEN(A10)=6 which works when i enter more or less than 6 characters, however it lets you paste in something which has more than 6 charters.
I wish to limit the number of text characters in a cell and have excel prevent the entry of additional characters after limit is reached. I have tried the Data Validation but it does not preven the entry of additional characters. I want to be able to show the error immediately when the limit is reached and no additional characters are permitted.
View 3 Replies View RelatedI have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.
I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.
Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:
id
event_name
C83-858
sample_1_arm_1
[Code].....
I am trying to come up with a macro that selects values from one sheet and inserts them into another sheet. The number of values will change each time based on the user's entry, as well as the number of times that each entry should be pasted.
For example:
Entry: X | Y | Z
Number of Times to be Inserted to New Sheet 3 | 2 | 1
Result:
X X X Y Y Z
I have spent a while trying to figure it out, however the best I can come up with is using an array, but I can only get one value from the array to paste multiple times:
(*Note: In my testing, I didn't insert into new sheet or set up the array to handle different values, I was just trying to get the basic idea to work)
Dim A(1, 3) As Variant
A(1, 1) = Range("C3").Value
A(1, 2) = Range("D3").Value
A(1, 3) = Range("E3").Value
[Code].....
I've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....
View 9 Replies View Relatedcontent from form is captured via formulas to a CIMLoad format on another tab
Users keep entering four to six decimal places in weight value
I need to limit the cell to either whole numbers, or a maximum of 2 decimal places
The cell is formatted to two decimal places, but when you activate the cell it shows the full value they entered, and so does my CIMLoad
How to I limit the value in this cell to whole numbers or numbers with a maximum of two decimal places?
I'm using the prob() function, and it works for no more than 61 random numbers. Does anyone know of a limit on the number of random numbers used in the function, and whether it's possible to use more numbers in the function? My formula is:
=PROB($C$11:$C$71,$L$11:$L$71,G11,H11)
I would love to be able to limit the number of rows in a specific sheet so that I can quickly carry formulas to the bottom of a worksheet without carrying them to row 1,048,576. I'm aware of options to hide rows and/or to limit the scrolling with the "view code" option on the worksheet tab; however, I'm mainly worried about worksheet performance. When I carry a formula down, I don't want to see a spinning blue circle run for minutes at a time, and I'm hoping to avoid the same spinning circle when I undo my formula. Is there any way to actually limit the number of rows, or is there any other limitation I could put in that would increase worksheet performance?
View 1 Replies View RelatedI have time start and time end and the difference between the two times. In a third column I have profit and want to calculate the profit per hour. When I divide the profit with the time spent I get a very very high number. If I for instance type in 1 hour of work and profit 100, then I get profit per hour 2400. (profit/hours spent)
On a side note I am trying to get the numbers as 24 hours and not use am or pm and in the spread sheet I get these numbers but in the box at the top where you see the formulas etc. it shows as am and pm. I have uploaded an example.
When you want to use Data Validation to limit the entry of the number of characters to a cell does this apply only to the cell that you are entering the characters or can it also apply to a cell that contains a LEN(A1) formula, for instance? Also,does the Data Validation limitation function includes spaces as well? Will the message appear while you are entering the characters (when it has reached the limit) or will it wait until you have hit Enter?
View 1 Replies View RelatedHow can I create a Venn Diagram in excel, or the closest thing to it. I would like all similar countries in the middle (where two circles overlap), then the differences on the outside with the country names. See my attached list.
MSCI vs. FTSE Emerging Market Countires.xlsx
-- > I have a single button set-up to delete a diagram
-- > The diagram is made up of 7 parts
-- > If all 7 parts are present the button will work perfectly as is.
-- > If one or more parts are missing I get an error.
-- > I tried doing some error traps but it's not working like I know it should (code below).
--> The button assigned to this macro is "Clear Defualt..."
I'm not sure if this is possible or not, but I've exhausted all possibilities. I have a table (see attachment) that has due dates, and person assigned to it. What I am looking for is a way to plot out dots on a scatter diagram, a different color for each assigned person, versus the dates in order to tell when a certain date is busier than another. I can't seem to get Excel to want to do this, as I am unsure what to put on each axis.
View 3 Replies View Relatedi have a stacked bar chart, similar to this: [URL] What i need is a trend line, similar to this: [URL] that shows the average of the bottom bar to date.
View 14 Replies View RelatedI am having a problem when making a XY-plot diagram because I want text values on the x-axis instead of numbers - but that doesn't seem to be possible to get with this type of diagram?
View 2 Replies View Related- I have some buttons set-up to insert pre-set diagrams from sheet “FISH PARTS” onto sheet “THE JUMPER FISHBONE”
- However, before inserting a new diagram onto sheet “THE JUMPER FISHBONE” I need to make sure the default diagram components are all gone.
- There are seven components all with structure Like “BONE_*”
- If one or more of these components are present I want to display a message to confirm the user wishes to delete the default diagram and insert new chart (or abort is they do not wish to delete the default)
- some explaination of structure below
- spreadsheet also attached
