I have different series number from column B to Q in Data Sheet, what I want is see strat and end range in temp sheet and delete(Shift xlup) numbers from Data Sheet from col a to q. If Series is like .....
View 9 RepliesI have a excel sheet of data numbers which blongs a packet of inventory where i found number series like this:
Start Range End Range Qty
2101200 2101499 300
I have to draw this onto a series like 2101200 then 2101201 then 2010202 etc.
but some time these numbers are in qty 30000 or more. I have questions
1- If any macro colud fill series suppose if write number in A2 and qty in B2 so it reads the qty of b2 and fill the series in A. If i write 1 in A2 and write 50 in B2 so the series should be auto fill 1 to 50.
My Second question is opposit of my above question.
2- if i have different number series in column a and i want them to be as start number in column b and end number in column c and qty in column d based like mentioned below example.
Series [b]Start Range End Range Qty
1 1 1 1
3 3 3 1
5 5 7 3
6
7
i am trying to both identify the start date and the end date of a series in excel.
if you will, picture a spreadsheet with two rows with data. the top row is comprised of dates in mm/dd/yy (end of month) format. the bottom line is percentages . . . a corresponding % for each date directly above. these percentages add to 100%. in two separate formulas, I would like to identify:
1. the date in which the first %>0 occurs (start date)
2. the date in which the cumulative addition of all months %'s equate to 100% or the date in which the last %>0 occurs (end date)
keep in mind many if not most of the %'s will be 0%.
The formula used to create the marked series line.
Code:
=SERIES(Rolling_window_data!$B$2;Rolling_window_data!$A$3:$A$122;Rolling_window_data!$B$3:$B$122;1)
The statistics in Rolling_window_data.
I need the marked line to be different from/to a point in time (x scale), depending on the backfill date in Rolling_window_data. All the contents are generated programmatically so the backfill date cell with a value will almost always be different. From what I've researched, I should make 2 series that overlap each other (in the current case, the 1st series should end on line 27 and the second should start from line 27 and continue to the end). The start is always line 3 and the end is always line 122.
How do I dynamically set the series start/end so I can make them overlap each other? I'm assuming I should add some kind of 'IF Statement' to the series code but I'm very new to excel formulas.
Code:
=SERIES(Rolling_window_data!$B$2;Rolling_window_data!$A$3:
IF(FOR Each Cell in Range("D3:D122") cell.value #N/A, Cell, "D122");Rolling_window_data!$B$3:$B$122;1)
I'm trying to create a spreadsheet for irrigation system program times. Each program has a start time, say 2 a.m. Multiple zones then run for varying times in a cycle. I need to add the run time to the start time to produce the next start time, and continue that through many zones. I can get the first few by simply formatting the cells as ##:## and using =F2+G2 (the start time is in F and the run time is in G) using 20 minute run time as a test. But when it gets to the third zone, my results show 2:60 a.m. and all results are incorrect from there on. Is there any way to do what I need in Excel?
View 2 Replies View RelatedI run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
I often have to work in Excel-spreadsheets which provide values to me in a rather unpractical way. For some reason, every cell entry starts with a space, followed by the value. It makes it impossible to start calculating with the values right away.
I tried
replace: " " with: ""
But that way all the spaces in the sheet are removed, not just the spaces at the start of each cell. I would like to avoid that, as it makes the descriptions a bit difficult to read...
So, any ideas on how to write a little macro (or a tric in Excel itself) which I can use to quickly get rid of those spaces and start calculating?
Sub DeleteRowsBasedOnCriteria()
'Assumes the list has a heading.
Dim cl As Range
For Each cl In Range("A6", Range("A65536").End(xlUp))
If cl.Value <> "=~*" Then
cl.EntireRow.Delete
End If
Next cl
End Sub
but it is deleting every row, I am not sure what is wrong?
starting in cell A6
if cell does not start with *, then
delete entire row,
next cell
that is the logic im seeing...ive tried a few o ther combinations like "~*" etc...
I am using following codes to find and delete number in series.
HTML Option Explicit
Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
If Val(Me.TextBox1) > Val(Me.TextBox2) Then
MsgBox "End number must be greater than start number"
Me.TextBox2.Value = ""
Me.TextBox1.SetFocus
Me.TextBox2.SetFocus
Exit Sub
End If
lVal = Val(Me.TextBox1): uVal = Val(Me.TextBox2)
Application.ScreenUpdating = 0
For i = 1 To Sheets.Count
With Sheets(i)
Set Rng = .Range("a1", .Range("a1").SpecialCells(xlCellTypeLastCell))
For Each r In Rng
If r >= lVal And r
I am using the following code to delete all of the series in the chart. It is give type mismatch error.
Sub allchannelchart()
Dim i As Integer, s As SeriesCollection
ActiveSheet.ChartObjects("allchan").Activate
'ActiveChart.PlotArea.Select
For Each s In ActiveChart.SeriesCollection
s.Delete
Next s
I have a spreadsheet (see attached) in which there are many series and i want to be able to delete most of the series except the first 4.
(The 0%, 5% 10% air voids and the 95% comp line)
iv got this code, which is attacted to a command button, which works fine to delete all the series but makes the graph a blank white box. id prefer it so it delete just the series not the graph "picture?" ...
My friend has set up an excel workbook with a sheet for every month (labelled Jan graphs, Feb graphs, Mar graphs etc) which has 7 pie charts per sheet. The problem is the pie charts are showing the 0% fields and the data labels are overlapping. I have found a macro that gets rid of the 0% labels but I need to repeat the macro for all the 7 charts on each sheet and all 12 worksheets for each month of the year. I am very new to VBA and coding of any type and have looked but can't find the solution.
The macro I am currently using for "Chart1" on "Jan graphs" is below. The charts are labelled Chart1, Chart2, Chart3 etc.
Sub ClearLabels()
Worksheets("Jan graphs").ChartObjects("Chart 1").Chart.ApplyDataLabels _
Type:=xlShowLabelAndPercent
For Each x In Worksheets("Jan graphs").ChartObjects("Chart 1"). _
Chart.SeriesCollection(1).Points
I have phone numbers imported from outlook in csv format. Now these numbers are like 2782512512, i want to add Zero at the start of this number. So, it will become 02782512512. How can i do this. Find and Replace method doesn't work.
View 9 Replies View RelatedI have the following formulae to pick up a random number from 0 to 9999.
=TEXT(RANDBETWEEN(0,9999),"0000")
I want to start this random search from a specified number. for example if i press F9 it should show 3456 at first press and then onward.
I have 2 dates a Start Date and an End Date.
I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column.
Start End Yr1 Yr2 Yr3
11/01/99 11/01/00 11/01/01
10/31/00 10/31/01 10/31/02
Col A Col B Col C Col D Col E
10/31/00 07/05/01 365 117 0
I thought a series of IF statements but I am open to an easy solution...
I have a column that is listing steps in multiple processes. I have each step 1 marked with a number 1 and would like to have a macro run that will read the column and when it sees a 1 move down to the next row and insert a 2, 3, 4, etc. until it hits the next 1 and then repeat the process. I am unsure how to build this as I am just starting to learn some VERY basic VB scripting.
View 5 Replies View RelatedMy finacial year starts on the 1st of October of each year and ends 30th of September each year.
My week is from Monday to Saturday - though for calculation ease: lets say that my week is from Monday to Sunday.
I need to calculate the week numbers of a date based on the above two criteria.
I understand that I need to ascertain the day of the start date first i.e: did 01/10/07 fall on Monday, Tuesday etc. - In fact it was Monday!
so for my year 07/08:Week 1 was 01/10/07 to 07/10/07Week 2 was 08/10/07 to 14/10/07ETCSo in the attached worksheet - what formula can I use to populate column D - by using the data in column E?
I.e. All I want to do, is input date into E and B, C, D will be calculated automatically. It would be great of excel could populate A - aswell ;-)
if A1 is a start date and B1 is the end date, I want C1 to return the number of months there are between the start and end dates?
View 9 Replies View RelatedI vant to get a random number i a cell whenever my file is opened, but the random function is not working well for me as I always get the same number.
Sub Auto_Open()
MyValue = Int((99 - 1 + 1) * Rnd + 1) ' Generate random value between 1 and 99.
Range("A1").Select
Selection.Value = MyValue
End Sub
I have a spread sheet with some data strings in Column E.
In Column A I want to extract a series of number from within the data in column E.
Example data in Column E
C:cleanedsystems1021_update_log_2-12-2012.csv
C:cleanedsystems42_update_mail_log_2-12-2012.csv
I created a formula in column A "=MID(E2,20,4)". I drag this down the column and I get the following results
1021
42_u
The first result is fine but the problem is the second result. Because I am looking at fixed length I am often getting something other than the file number which I seek.
Is there a way to do a formula that will go to my starting positions (this is a constant starting point) and then look for the numbers before the first "_" as a stopping point? The maximum will always be 4 digit numbers but the problem is there are 1-3 digit numbers as well.
I have a userform which saves its info to a spreadsheet. what i want to do is to also open a new workbook, save the same info to the new workbook, copy and paste from existing spreadsheet will do instead of linking the userform to a different workbook (i can do all this) I then want to save the workbook in a specific folder(folderA) but named by the next number ie workbooks 1, 2, 3, 4, 5, 6, 7, 8 etc exist so the next workbook should be called 9 so I need some code to look for the names of existing workbooks in the folder and determine the next. Then I want a spreadsheet to at a pre-set interval(15 minutes) open each workbook , copy the line to the next blank row and move the workbook to a second folder(folderB). The folders A & B will only contain these workbooks.
View 2 Replies View RelatedI am trying to create a simple set up where the operator can enter a starting date and the number of weeks they want this event to occur. This then produces a column of dates from the starting date to the end date for the number of week entered.
E.g. - start date = 16/04/2014 - No. of Weeks = 3
The output would be as below
16/04/2014
23/04/2014
30/04/2014
I would like to use an (I think) easy SUMIFS formula. In this case I'm using only 3 criteria, and 2 of them work perfectly. With the third I want to SUM only IF the series begins with 112 in the example.
Criteria 1 Criteria 2 Criteria 3 SUM
SYS 1 111234 105
SYS 1 111345 111
ORA 2 112456 33
SYS 2 112998 70
SYS 2 112856 56
[Code] ..........
From what I've found out so far I could use "112*" ore something like it, but I cant get it to work.
I need to fill multiple number series in the same column. Example:
Have
Want
1
1
[Code]....
As you can see below, I've written code that writes random numbers into three columns of a spreadsheet (10 numbers in each column).
What I want to do is create code that will run the random number generator for a period of 1 minute and then stop. I know that I will need to write a timer subroutine to do this but I'm how unsure how to do this.
I have a list of numbers with 2, 3, and 4 digits. Is there a formula that can recognize if there are only 2 digits, it will add 2 zeros in front of the number.
For example,
47 will become 0047
234 will become 0234
1234 will become 1234
How to get the index number of a seriescollection within the activechart user has selected .
I am able access the chart selected by the user by using the activechart function, but next i am trying to access the seriescollection selected by the user, in other words the activeseriescollection thing.
Is there any way to access the active seriescollection of an activechart or any other way to access the seriescollecion of a chart selected by a user.
I googled a lot but what i found that we have give the index no. i.e; activechart.seriescollection(1)......
I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....
View 9 Replies View RelatedI'm holding a Reverse Auction where people pay a dollar to place a bid, BUT the twist is they are giving me a number that they how will be the lowest number but it has to be the only occurrence of that number (greater than 0).
During the party people can guess as much as they want to pay. I think i'll use a spreadsheet with their names in column A and go out in the row with however many cells for how many numbers they guess (so there would be blank cells in the overall range of the whole list if one guy buys 10 numbers and another only 1, for example).
So, i need a cell at the bottom that tells me the lowest number that wasn't guessed more than one time.
I've found how to FIND duplicates and the lowest number but i don't know how to write it so that it discards the duplicates.