VBA To Create Charts/Graphs From Non Continuous Ranges
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Create Dymanic Charts Or Graphs
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 Replies!
View Related
Create Multiple Graphs/Charts From Data Groups
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..........................
View Replies!
View Related
VBA Macro To Create New Worksheet, Charts, Etc.
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.
View Replies!
View Related
VBA Macro Code To Create Multiple Charts
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).
View Replies!
View Related
Copy Same Range To X Non-Continuous Ranges
I want to select a range of cell relative to A2 then copy and paste that range 12 times. See my coding below. I have been getting a Warning box titled "Microsoft Visual Basic" (X) 400. Sub mycopytry() Dim check As Integer For check = 1 To 12 Range(ActiveCell.Offset(29, 0)).Select Selection.Copy ActiveCell.Offset(1, 0).Select ActiveSheet.Paste check = check + 1 Next End Sub
View Replies!
View Related
Printing Of Non Continuous Ranges Of Data
I am trying to print non continuous ranges of data in the same worksheet using the print area - by keeping the ctrl button pressed etc... it works fine but the outcome is that the text in the pages becomes very smaller when compared to when you print page by page and fit every page to one.
View Replies!
View Related
Sum Multiple Non Continuous Ranges By 1 Criteria
I am trying to write an array using =sumproduct. I cannot get the second array to work. Whenever I split up the arrays they work fine separately, but not together. This is what I am putting. Any help is greatly appreciated. I am not sure if I am even using the correct formula. =SUMPRODUCT((B37:B51="Math")+(F37:F51)+(I37:I51="Math")+(M37:M51)),[(B18:B31="Math")+(F18:F31)+(I18:I31="Math")+(M18:M31)] So basically, I am creating a transcipt. So in column B if the subject is Math, I want column F to be added and so on and soforth. See there are more arrays, because it is broken down to grades 9, 10th, 11th, 12th. And what is being added is their credit (.5 or 1) for the class for each year.
View Replies!
View Related
Clear Contents Multiple Non-Continuous Ranges
what is described in the post clear "contents of rows based on cell contents in column" clear contents of rows based on cell contents in column What is different is that for rows that I wish to leave untouched, I have in column A the text "Active". If that text is not present (""), then I need to clear contents for only certain cells in each row because others have formulas. For example, if A32 does not say "Active" I want to clear contents of cells D:32, G:32, etc while leaving the others in row 32 in tact. I need this to loop through about 400 rows. If it would make the macro easier, I can have the data in column "A" say "Inactive"/"Active" instead of blank/"Active"
View Replies!
View Related
Create A Single Range From Multiple Ranges For VBA Input
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.
View Replies!
View Related
Non-Continuous Range In Event Code: Show A UserForm When A Cell In 1 Of 31 Named Ranges Is Selected
I am using the following Selection_Change Event to show a UserForm when a cell in 1 of 31 named ranges is selected. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub Dim i As Long For i = 1 To 31 If Not Intersect(Target, Range("StatPost" & i)) Is Nothing And Target.Value = "" Then If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then....................
View Replies!
View Related
Macro To Create Multiple Graphs
I would like to create 2 graphs for each set of data in my worksheet. the first set of data that I would like to create a 2D line chart for is in cells AQ3:AS19 (not sure if that is the correct syntax??) and the second is AW3:AY3. I would like to position the first chart (top right corner) at BA3 and re-size it so that it is 17 cells tall and 7 cells wide. the second chart should be positioned at BI3 and the same dimensions. I would like to delete the chart legend for both graphs then I need to repeat about 1000 times. Each new set of data starts 20 cells lower (ie. AQ3, AQ22, AQ41..). Or every time "Band" is found in column AP.
View Replies!
View Related
Create & Copy Multiple Graphs
I'm pretty much a novice at Excel so obviously my knowledge is fairly limited. Basically I have created 2 sheets called 'Week 1 - By Day' and 'Week 1 - By Campaign'. The first sheet (By Day) contains data which supplies graphs to not only that sheet but also to sheet 2 (By Campaign). Sheet 2 (By Campaign) only contains graphs sourced from data within sheet 1. So, I need to copy both of these 52 times (for every week of the year). That’s 52 x sheet 1 and 52 x sheet 2. I have been able to copy sheet 1 easily enough by holding down the Ctrl button and dragging the sheet. This works fine and the formula and graphs within the sheet change automatically. However, the major problem at the moment is sheet 2(sheet 2 only contains Graphs which is related to data from sheet 1). When I do the same thing with sheet 2 (hold down Ctrl and drag) the graphs within the new sheet (sheet 4) are still sourcing the data from sheet 1 instead of sourcing the data from Sheet 3 'Week 2 - By Day'.
View Replies!
View Related
Macro To Create Multiple Graphs From X Sheets
I am trying to use VB to create 50 charts each with 3 lines using data from 3 different excel spreadsheets in the same workbook. I am able to create one chart with 3 lines using data from the three spreadsheets, this is good. However I exprience problems when i try to insert a loop to create 50 charts that correspond the the rows in each spreadsheet. I am new to VB and am very inexperienced with VB. In each worksheet my data is arranged in rows. row 1 of each worksheet has the header information and rows 2 through row 50 have my corresponding data. for example A2 lists the name and F2 through T2 lists the data. I have tried a bunch of different things but nothing seems to alllow me to be able to create multiple charts which correspond to each row of data? Sub Chart2PPT() Dim arow As Integer Dim acol As Integer Dim StartPoint As Integer Dim EndPoint As Integer Dim rStartPoint As String Dim rEndPoint As String StartPoint = 2 EndPoint = 4 For arow = 2 To 5 rStartPoint = "f" & StartPoint rEndPoint = "t" & EndPoint Charts.Add ActiveChart.ChartArea.Select ActiveChart.ChartType = xlLine 'Type of graph........................
View Replies!
View Related
VBA Macro To Create Multiple Data Validation Lists From Variables & Named Ranges
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.
View Replies!
View Related
Using Named Ranges To Update Charts
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 Replies!
View Related
Dynamic Ranges For Error Bars In Charts
I 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 Replies!
View Related
Macro To Create Dynamic Charts
i 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 Replies!
View Related
Create Charts/Based On ListBox Value
i 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..............................
View Replies!
View Related
Compile Error After Editing VBA So Graphs Are Independent Of The Worksheet
From every sheet I want to create the same type of graph, so a macro could be very useful. First I recorded a macro. The worked fine, but it could only be used by only one worksheet. Every time I used the macro it returned to the original worksheet where I created the macro. With some searching I found out how to change the code so that the macro could be used for the active sheet. But now I get stuck with the following error: "compile error: invalid qualification" (translated from Dutch). With error tracking I get stuck at the first line "Sub grafiek()"
View Replies!
View Related
XYScatter Charts In VBA
I am having lots of issues with generating a scatter chart in vba. I tried recording a macro but the only thing that recorded was my different selection clicks on the chart. No formatting was recorded. I have the following ....
View Replies!
View Related
Creating Charts With VBA ..
I've got a table which has additives in Column A and products on Row 1. There are 13 additives and 10 products. Each combination of product and additive has a price. So I have 130 different prices in my table. The prices change monthly and the new prices are added to the sheet so that last months prices will move to left so that the newest price data table is A1:J13 and previous month is K1:T13 and so on. I need to make an individual chart to every combination which contains the history of the price changes. I have two cells (A20 and B20) where the user can pick a product and an additive (created a menu by using Data|Validation...). These selections are combined in a cell C20 (=A20&B20). Then I have a macro that is linked to the cell C20 and is basically Case Is = 1st combination --> Call Macro1, Case Is = 2nd combination --> Call Macro2 etc. That macro will work out just fine, BUT is there any way I could make all the 130 charts easier? Now I've recorded a macro that creates the first chart and then I've just copy-pasted it, changed the attributes (cell numbers, names and so on) that define the data to the next charts. This will take ages... Every combination will have at least 13 changes (12 monthly price cells and the name of the cart) so in total this will mean about 1500 manual changes. In my case this will mean a high danger of typos. I know, it would be SO much easier if I could give you the Excel file to look at...hopefully you understood what I mean. I'll only follow the changes within a year so prices older than a year will not be shown in the charts.
View Replies!
View Related
VBA And Charts In 2007...
I have a piece of code that arranges a chart to fit the data it should show. The code used to work perfectly in the old Excel version, but after an "upgrade" to Excel 2007 I get "Automation Error" from the line where the scale type is set.
View Replies!
View Related
Create Dynamic Ranges
I have just upgaded to Excel 2007 and previously used a Dynamic Range Wizard addin in Excel 2003 but it does not work in 2007. I think it originally come from Robert Bruce but can not find it on the web to get an update.
View Replies!
View Related
Creating Pie Charts With VBA
Now I need to create multiple pie charts based on data in tables on many sheets. I need to create the same set of charts, for each sheet separately. I recorded a macro for one sheet, which created following ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlPie ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$B$2" ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$D$6:$D$8" ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$A$6:$A$8" Range("A11").Select ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlPie ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$6" ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$6:$C$6" ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5" ActiveWindow.SmallScroll Down:=3 Range("A11").Select ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlPie ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$7" ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$7:$C$7" ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5" However I cannot automate. I tried different methods for creating the charts, as found on the site [prefer .chartobjects.add(x,y,z,a) since I can immediately position the chart] , but get stuck at seriescollection everytime. It looks like when the chart is created by XL (with suggested data in it) I cannot address the series data. I got it right to generate a chartobject that is empty, then added the series data to it, but when opening the new chartobject, it is again populated with suggested data. How do I generate an empty chartobject everytime, so I can insert seriescollection info with code? Any other way to generate charts?
View Replies!
View Related
Creating Multiple Charts Via VBA
am trying to create multiple pie charts now but I'm having problems my errors are highlighted in red. What I'm trying to achieve is to create a pie chart for the first table then another for the next table and so on but its not working out.
View Replies!
View Related
Use Macro To Create Dynamic Ranges
I am looking for a macro that will allow me to create a dynamic range based on the settings I give it. I would like the dynamic range to be called tracking_number and it to have a reference to =Offset(MainReport!$A$2,0,0,COUNTA(MainReport!$A:$A),1) If this is possible I would really appreciate it. The reason I need this is because I have 2 workbooks. They are the one with the MainReport in it and then the Master workbook. Every day the workbook containing the MainReport is overwritten with more data, so I can not store a dynamic range and reference it each day.
View Replies!
View Related
Create A Set Of Named Ranges
I'm trying to make my worksheet generate a bunch of named ranges whenever it is activated. However, I'm getting a "Not a valid name" error on the line in red. The value of r is a string, so I really don't see what the issue is. Another time, I got a object defined error, but I think I've declared everyhing too. That line is very similar to the line above, and tends to work once (the first time around the loop). After that, I get the errors I mentioned.
View Replies!
View Related
Create Charts From Same Data But Each Based Ob Data Criteria
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View Replies!
View Related
Create Unique Lists From Multiple Ranges
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?
View Replies!
View Related
Create Dynamic Named Ranges With Code
I am employing code to label dynamic ranges that takes the form of, ActiveWorkbook.Names.Add Name:="dms", RefersToR1C1:="=OFFSET(DMS!R10C5,0,0, COUNTA(DMS!C5),COUNTA(DMS!R10))" 'dms_j ActiveWorkbook.Names.Add Name:="dms_j", RefersToR1C1:="=OFFSET(DMS!R11C10,0,0,MATCH("" * "",DMS!C10,-1),1)" 'dms_p ActiveWorkbook.Names.Add Name:="dms_p", RefersToR1C1:="=OFFSET(DMS!R11C16,0,0,MATCH("" * "",DMS!C16,-1),1)" 'dms_r ActiveWorkbook.Names.Add Name:="dms_r", RefersToR1C1:="=OFFSET(DMS!R11C18,0,0,MATCH("" * "",DMS!C18,-1),1)" 'dms_t ActiveWorkbook.Names.Add Name:="dms_t", RefersToR1C1:="=OFFSET(DMS!R11C20,0,0,MATCH("" * "",DMS!C20,-1),1)" The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.
View Replies!
View Related
Dynamically Create Named Ranges Based On Cell
I am looking for a solution to dynamically create named ranges according to the contents of cells in a particular column. The following code works for 1 word names, but in many cases the title cell contains numerous words separated by spaces. Is there a way I can adapt this code so that it will name the ranges with the spaces removed? For example, where cell C2 contains the narrative 'Sales Ledger Control', I would want the range name 'SalesLedgerControl'. Range("R2:Z2").Name = Range("C2") The named ranges are referred to in numerous other worksheets, where selecting a particular narrative from a listbox creates a dependant drop-down in the adjacent cell (eg if Sales Ledger Control is selected, the dependant list contains names of customers). FYI, the data validation in the other worksheets ignores the spaces in the range names, ie: =INDIRECT(SUBSTITUTE(G2," ","")) It would not take me long to name the ranges manually, but a macro is preferable because the narratives in the title cells will often change and the range names will obvioulsy also therefore change.
View Replies!
View Related
Macro To Create Named Ranges Based On A Header Row.
I need a macro that can set up some named ranges using the text in the header row and the sheet name. The header row will always be in row one, but the number of columns will be dynamic. The amount of rows in the range will need to be dynamic also. To clarify, every used cell stating in row 2 in a column will be the named range with the sheetname+text in row one of the column the actual name.
View Replies!
View Related
Macro: Create Named Ranges. Unknown Columns/Rows
I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers. I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information What i've tried: Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code). The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it. modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc) For i = 1 To modelcount Redim Preserve Models(0 To i) Models(i) = Cells(1, i + 7) Range1 = Cells(2, i + 7).Address(xlA1) lastRow = Cells(rows.Count, i + 7).End(xlUp).Row Range2 = Cells(lastRow, i + 7).Address(xlA1) Reference = Cells(2, i + 7).Address(xlA1) ThisWorkbook.Names.Add Name:=Models(i), _ RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True Next i This gets me the range i need, but doesnt create the named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate to the data in any columns. It shows the variable names rather than the cell range the variable represents.
View Replies!
View Related
Create User Settable Print Area With Dynamic Ranges
Once a user of my workbook has input various price levels on the Input Page, the data is sorted on the output page. As the data on the Output Page is quite vast and not all of it is relevant on a particular day, the user is interested in just printing a certain range of data that are in 5 columns. I am trying to figure out how to set up a macro that will allow the user to enter into an Input Box the range to print (ie. Cells C25:G60). I found one example on this site listed below that allows one to do that but I would like to make it more user friendly. I do not want to make the user have to write out Excel cooridinates. Specifically by allowing the user to input in the Input Box a High price level in Column C and a Low Price level in Column C ; The macro will look in Column C for the highest instance of this High Price and begin the range down to the last instance of the Low Price (there can be several instances of the exact price level.) The macro would then create the print preview of the cells Cxx:Gyy. Sub SelectPrintArea() Dim PrintThis As Range ActiveSheet.PageSetup.PrintArea = "" Set PrintThis = Application.InputBox _ (Prompt:="Select the Print Range", Title:="Select", Type:=8) PrintThis.Select Selection.Name = "NewPrint" ActiveSheet.PageSetup.PrintArea = "NewPrint" ActiveSheet.PrintPreview End Sub
View Replies!
View Related
OFFSET-COUNT-MATCH Method To Create Dynamic Named Ranges
I use the standard OFFSET-COUNT-MATCH method to create dynamic named ranges in my Excel projects. Needless to say, this method won't work on a spreadsheet with formulas extending beyond the current range. The count function counts the cells containing formulas, even though they may contain no data. Does anyone know how to construct a formula that will IGNORE the "formula only" cells??
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
Resource Error Message "cannot Complete This Task With Available Resources" When Changing Charts Thru VBA
I have the following code for a sheet in my workbook that has 3 charts: Private Sub Worksheet_Change(ByVal Target As Range) Application.Calculation = xlCalculationManual ActiveSheet.ChartObjects("RdteObs").Chart.SetSourceData ThisWorkbook.Names("GSumRdteObs").RefersToRange ActiveSheet.ChartObjects("RdteWip").Chart.SetSourceData ThisWorkbook.Names("GSumRdteWip").RefersToRange ActiveSheet.ChartObjects("RdteExp").Chart.SetSourceData ThisWorkbook.Names("GSumRdteExp").RefersToRange Application.Calculation = xlCalculationAutomatic End Sub but whenever the sub runs, I get this error message: "Excel cannot complete this task with available resources. Choose less data or close other applications." Does anyone have an idea what's going on?
View Replies!
View Related
Name Ranges In VBA
I am trying to locate a named range cell in my VBA macro, but find that VBA will only allow me to specify the actual cell eg. Range("E2"). The piece of code I am using below is Sheets("sheetname").Select Range("E2").End(xlDown).Offset(1, 0).Select I would like to use Range("namerange").End(xlDown).Offset(1, 0).Select Do i need to Dim or specify the name range in any particular way.
View Replies!
View Related
Non-Continuous For Loop
Here is what I want to do: Sub test() Dim num As Integer For num = 10 To 20 And num = 30 To 40 And num = 50 To 60 msgbox num Next End Sub Rigth now I have to do this: Sub test2() Dim num As Integer For num = 10 To 20 msgbox num Next For num = 30 To 40 msgbox num Next For num = 40 To 50 msgbox num Next End Sub
View Replies!
View Related
Named Ranges From VBA
I have some named ranges that refer to 5 pieces of data organized into a row. For example, the name MyNamedRange might refer to $C$5:$C$10. I am trying to loop through each column and get the values in MyNamedRange, then change corresponding values in a different named range. However, when I try to use Offset to access the subsequent columns of MyNamedRange, it doesn't work. It only gets the value of the first column right, the rest return <EMPTY>. Sample .......
View Replies!
View Related
LinEst In Vba - Not Using Ranges
I am calculating variables in Vba and want to find their co=effiicients in the for y=a +bx +cx^2. This works great if I output my variables to excel and then pick up this range however as I am generating the variables in vba and have an array of variables I would rather not have to keep spitting them out to excel then picking them up again. The three important lines in my code are: c(nFix - j) = WorksheetFunction.Index(WorksheetFunction.LinEst(inpv, Application.Power(inps, Array(1, 2))), 1) b(nFix - j) = WorksheetFunction.Index(WorksheetFunction.LinEst(Range(Vc), Application.Power(Range(SPd), Array(1, 2))), 1, 2) a(nFix - j) = WorksheetFunction.Index(WorksheetFunction.LinEst(Range(Vc), Application.Power(Range(SPd), Array(1, 2))), 1, 3) As you can see for c I am referring to inpv and inps these are arrays. I have tried using inpv(), etc but no luck b and a work great, here I am reffering to ranges Vc and SPd. As I am going through this a lot of times it seems rather inelegant not to be able to do the whole thing in vba - i would rather not use the dump to spreadsheet then pick up again approach!
View Replies!
View Related
VBA Subtracting Ranges ...
I have two Ranges, which I view as Arrays or Matrices, I want to subtract the two (which do not overlap or union in any way) to get a new third range of the differene. This new range should be the same size, and can be considered a difference of each cell in the range. Note both ranges are already identical in size. Any help solving this, searched up and down for hours. Note; Im trying to avoid looping, as this might become incredibly inefficient if the ranges grow to big, looking for alternative that is faster, someone said maybe excel.evaluate
View Replies!
View Related
VBA - Arrays From Ranges
I am trying to loop through the values of an array that was populated from a range. Essentially I'm trying to manipulate a pivottable depending upon what a person selected in a control form. Below is my Dim userWeekArray As Variant userWeekArray = Sheets("Valid Values").Range("A20:A27") For w = 1 To 8 If .PivotItems(w).Name = userWeekArray(w) Then .PivotItems(w).Visible = True Else .PivotItems(w).Visible = False End If Next I get an error message stating that Subscript is Out of Range. It seems Excel is assuming that userWeekArray is a Range object when I want it to be an Array. How can I populate an array with elements from a range in a worksheet so that I can then easily loop through the array to examine the elements?
View Replies!
View Related
Selecting Ranges Using VBA
I have a macro that has automated 95% of a task but one thing is still evading me! If I use the keys then this is what I do: Select cell C192 Select Shift and R-Arrow so that C192 and D192 are selected Select Ctrl, Shift and Up Arrow so that C1:D192 are selected Select Shift and Down Arrow so that C2:D192 are selected
View Replies!
View Related
Continuous Cells Without Breaks
I have attached an example Excel file that shows what I am looking for. I have one sheet with a list of parts in inventory. Each part, we'll call them A-F, has a reorder point and will automatically detect whether or not the B column should say order or not. What I want is a second sheet that will list all the parts that need to be ordered continuosly, without the breaks where parts dont need to be ordered.
View Replies!
View Related
|
TRACKER
