I am having an issue with looping through data ranges. Below is a subset of my macro. What I require is for LegendRng to stay the same and DataRng to move after each loop.
However everytime the vba runs through a loop, the previous range is recorded i.e. the source data for the chart is "A1:D5" instead of "A1:D1, A5:D5").
[Code] .....
I have an excel sheet populated with loads of values. There is a space between each set of data. I need to draw a graph for each section and i cant work out how to do it. I have attached the sheet in question. The gap between each column has "space" written on row, which is needed to prevent it being deleted. The drawing of the graphs needs to be automated as there is going to be 100's of sheets containg lots of data. The column size of each section can change so it needs to be dynamic. I have attached an example sheet
View 5 Replies View RelatedI'm trying to create multiple Charts with VBA, each chart on a seperate Sheet. The link below works but i need to change it some and i'm stuck. What i'm trying to do is create a new sheet for each new graph, but I need it to create a graph for the 250 associates in column B then plot 52 weeks of their % from the Row their name shows up in. First name is in (B3), his % data is in range (N3:BO3). I have the weeks numbered out 1-52 in (N2:BO2) and i'd like their name to appear in the Tab and on top of the graph. I applied the VBA code from the link below to my sheet and changed what i knew i needed to change, but it debugs. I use to do each graph manually when i had 25 associates but i would like to use this for the whole building now.
[URL]
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want:
Sub Macro5()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveWindow.SmallScroll Down:=-3
End Sub
How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).
The macro creates multiple charts from data in one sheet, but now when I'm trying to scale it up to multiple sheets, the previous charts created disappear. E.g Creates 5 charts for sheet 1 (can see them being created, using debugger mode), looping to the next chart and they are created but the first ones has disappeared! What am I doing wrong? I'm creating my chart by the following code:
[Code] .....
The startpoint variable is changed for every new chart within the one sheet and the outputsheet is changed when you change the sheet.
I need to make a macro that creates a specified number of graphs depending on the file's number of data sets. I know the number of sets that are in the data, and I know the number of data points that were taken. Here is what I have:
Sub Graphs()
Dim Startpoint As Integer
Dim Endpoint As Integer
Dim count As Integer
Dim xStart As String
Dim xEnd As String
Dim NumberSets As Integer
Dim yStart As String
Dim yEnd As String
Dim DataSet As Integer
Dim Data
Startpoint = 11 'The first set always starts in row 11
Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10
NumberSets = Range("L7").Value 'number of times I need the loop to work
count = 1..........................
I have this piece of code which just copies every 3rd cell in column B from sheet1 and pastes it going down column A in sheet2. This works fine for me.
Code:
Sub Macro1()
Dim FirstCopy As Integer
Dim FirstPaste As Integer
FirstPaste = 1
For FirstCopy = 1 To Range("B" & Rows.Count).End(xlUp).Row Step 3
If Range("B" & FirstCopy) "" Then
Range("B" & FirstCopy).Select
Selection.Copy
[code]....
However, now I want to copy every third cell from columns B, D, and F in sheet1 and paste them going down columns A, B, and C in sheet2. When I run the code below I am receiving this error: "Compile error: Wrong number of arguments or invalid property assignment"
Code:
Sub Macro1()
Dim FirstCopy As Integer
Dim FirstPaste As Integer
FirstPaste = 1
For FirstCopy = 1 To Range("B" & Rows.Count).End(xlUp).Row Step 3
[code]....
I am trying to create a loop to add multiple worksheets
I want to add a new sheet for each company (A2:A14)
I am also wanting to add the sheets after the current last sheet if possible.
Sub addnewsheet()
x = 2
Do Until Cells(x, 1) ""
Sheets.Add.Name = Worksheets("securities").Cells(x, 1).Value & ".ax"
x = x + 1
Loop
End Sub
I'm trying to create a single array from multiple ranges... I'm not sure what syntax to use:
Code:
Dim dat4() As Variant
Set r = Sheet13.Range("rsqlassetid")
Set r2 = Sheet13.Range("rsqlparentcat")
dat4() = (r , r2)
I can create an array with multiple columns from a range if the columns are next to each other but in this instance they're not.
These 2 ranges both have the same number of rows and I'm trying to combine them into a 2 column array, but not sure how to make it work without looping, rediming the array and using a secondary array to preserve the data...
I have values in Worksheet 1, spread over A1:D25 and A200: D250.
In worksheet2 I have values again from A1:D25 and A200:D250.
Is is possible to only get the unique values of those 4 ranges with the advanced filter? They all need to be shown in eg worksheet 3 starting in A1, (so kinda merged in a sense)?
Is that a thing more for a UDF, or is there a excel function/option that does exactly that?
I have been looking for ages for that kind of function/option, since I thought it must be possible. But this sure does not look to be a standard functionality, or is it?
Is there a (free) add-in that might do this kind of thing?
I found this code on some office help page:
Sub SortAllRangeData()
' Place column header for temporary sort area.
Range("IV1").Value = "Numbers"
It kinda does what I needed, but it lists the actual data in the same spot it used to be. I want to be able to list the sorting in a different column on a different sheet and in 1 column only. Is this difficult to modify so it becomes a UDF or is this something totally different?
I have a VBA function with the header:
cubspline(Xval As Double, XRange As range, YRange As Range) As Double
The problem is that XRange (and also YRange) is in different areas of the spreadsheet. I want to combine these areas into one range which I can pass on to the cubspline function. What is the easiest way of doing it? I'm looking for something like a "union" function for ranges in Excel.
I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.
VB:
Sub GetTabNames()
Dim wkBook1, wkBook2 As Workbook
Dim stFilePath1 As String
Dim FileList(1 To 18) As String
Dim iLoopSheet, iLoopProg As Integer
Application.ScreenUpdating = False
[Code] .....
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is
Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName
It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
I want to update a number of charts using data that covers a rolling 12 months - this will be amended each month by addition of new data but I will be retaining the full history. I thought the easiest and quickest way would be to name the data range and just amend it each month as I added new data. However, when I use the named range in the chart wizard it is replaced by direct cell references. Can anyone suggest how I can retain the name in the chart or suggest an easy alternative method?
View 9 Replies View RelatedI am using defined names to build a chart. This is working fine, but I also would like to use a defined name to control the +/- fields for the error bars. The defined names I have set up are correct, and I am able to enter them into the sorce data form but Excel does not display them.
View 9 Replies View RelatedI want to find the number of similar values contained in a column based on a date range I specify.
I have a data set that's formatted like the following (you can see the full data set at [URL] but I've hidden the irrelevant columns and highlight the relevant ones)
[Code] .......
I want to use this data to create two types of graphs. One showing the ratio including all values in the Status column. There are 4 total (Open, Lost, Won, Won-Job Cancelled). I want another which excludes the "open" status from the ratio figure. I've create some images demonstrating what I want to accomplish at [URL] .....
Additionally, I want to be able to specificy date ranges for these charts. Specifically 0-30 days from todays date, 30-90 days and older than 90 days.
I want to accomplish this using Google Sheets (the latest version) and know how to use the graph total. It's just creating the data the way I need it which is proving to be the most challenging.
I have created a line chart that plots weight of a person, but want to show on the plot area what a certain range of weight represent, Ive attached images to show this concept.
View 4 Replies View RelatedI don't mind paying for the add-on/add-in, I need to have a chart displayed of 100 rows and 1000 cells of data.
View 6 Replies View RelatedI'm trying to dynamically populate ranges to facilitate dynamic charts being generated.
I use excel 2010 at work, and 2011 for mac at home.
Dynamic chart ranges populated from named ranges as selected in nested indirectly sourced validation lists
I want any selection made in a dependent validation list which contains a list of named ranges to trigger a worksheet_change event which copies the range the selection points to and pastes it into a dynamic range in another column, beginning as a specified cell.I've tried using this, put together from some code examples from similar, but different issues.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
Range(Range("B2").Value).Copy
Range("P2").Paste
End If
End Sub
Trouble is, I don't really understand this code. It doesn't appear to do anything when I make a worksheet change in "B2", but I don't know exactly what it is. I suspect that perhaps the fact that "B2" is validated from an indirect source might be difficult?
I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
FC_Macro_Sample.xlsm
how to create dashboards or charts. I am looking for similar website like http://www.datapigtechnologies.com/. this is very good website for those of you who need to do some fancy staff with excel.
View 2 Replies View RelatedBasically I want to have a macro to loop through and make all the pie charts for each location but only for the top 10 of each location.
I also want the legend to be more precise for example, for London the first one in the legend should show:
Column C + "-" + Column I + "-" + Column J
Which would be: London - Rol9 -13
Instead of it being: London data rol9 13
Attached is the document in question where I've made the graphs manually : Example_Pies.xlsx
I need a macro to create the following worksheets and charts from an Excel data set:
Three (3) worksheets (already created manually in attached Excel file):
1. Chart Data.
(a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr).
(b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset.
2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data).
3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data)
At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.
Create an individual pie chart for each row of data (roughly 2000 rows in master list). Data to be charted contains 2 cells (Benefit and Salary). Once the chart is created I need to save the chart as an image (assuming it will need to go into Paint or another program?) Once the images are created and saved to a folder I can add the image link (file location) to a merge document data source. This way I will be able pull in the pie chart (now a picture) with a merge field during a mail merge.
Sample Data
Employee
Benefit
Salary
[Code]....
I normally create charts and use the same formatting for my colours i.e. Gradient fill with Shadow etc...
Now each time i create my charts i am having to keep do the formatting again and again and if i create a new spreadsheet with charts i have to do it all over again
Is there any way i can save this formatting colour so i can quickly apply to my chart series etc?
I'd like to put a spinner button on a chart that will change the x-axis date range. The data is in day/month/year format, but ideally I'd like to have the spinner button move in 1 year increments. The most recent date should always be present and changes should just impact how far back in history to go. I have basically no experience with vba coding, so please be as thorough and simple with your responses/solutions.
View 2 Replies View Relatedi want to create dynamic Excel Charts in one sheet ,The sheeet contain dynamic columns and rows and i want to create for each 4 columns a chart in a new sheet ,i started to try it in VBA and didnt founs the way to select 4 columns as a range, i succeseed to do it with msgboox that the user enter the range but i want to do it in a loop
View 9 Replies View Relatedi am having a problem of updating my graphs in the application i devloped, my application is like this, go to sheet 1 click showuserform button and suer form contains combo and list box select the sheetname in the combo and select two values in the list and click plot.it plots the graphs acording to the selected values but my problem is it makes a newchart every time when we select a new week range it is getting stacked...it is not getting updated with the most recently plotted graph ??? i am facing this problem when i am opening my application in other computer...u can see my problem when u open my application. i am attaching my workbook and this is the code i used for making the chart
Sub MakeChart()
Dim rng1 As Range, rng2 As Range
Dim gs As String
gs = ComboBox1.Value
Application. ScreenUpdating = True
'First selected
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Set rng1 = Columns(1). Find(ListBox1.List(i))
Exit For
End If
Next
'Last selected
For i = ListBox1.ListCount - 1 To 1 Step -1..............................
I have a quite big database as a result of a study in Excel 2010. There is a special block of data for every participant in the study, including 16 data series in 16 lines. I have to make a unique chart for every line.
One block is bulid up in a way, that every row is the input for a chart, with the exception of one "spacer" row, which is empty.
Name
1 2 3 4 ...
A
B
C
D
E
F
For example in the sample above, there should be 6 line-charts (A, B, C, D, E, F) next to the block.
Can a macro be written, so I can automatically create the chart-set by selecting the block's upper left cell and running the macro? Or is there any easier method?