I am fairly new to using vba in excel, but not vba entirely so I know there is a way to accomplish what I need. I need to specify the range for a chart using variables
1. The row variable will be a selection on a combo box
2. The column variables will be mulitple selections on a list box
3. These cells will not be concurrent, for example:
I6, K6, O6
This is all in a vba macro button
I recorded a simple macro to create a chart using VBA. The code for the macro is:
Code:
Sub Add_Chart1()
'
' Add_Chart1 Macro
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
[code]....
The problem is that I need the code to select a variable range from C4 to the last used row in column C for the x values and from G4 to the last used value in column G.
I tried to modify the code to do that by doing the following:
Code:
Sub Add_Chart1()
'
' Add_Chart1 Macro
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
[code]....
When I run the macro, I get an error 91, "Object variable or With block variable not set"; Also the chart is created but not correctly.
I created a sub called "range" (see below) and it correctly selects selects the C or G ranges, however, the argument does work in the chart macro.
Code:
Sub Range()
Sheets("Sheet1").Range("C4:C" & Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row).Select
Sheets("Sheet1").Range("G4:G" & Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row).Select
End Sub
I have data which is imported from a txt file and always starts at the same row (R19) but is variable in length (end of data R??). I want to be able to select the chart xvalues (R19C10:R?C10) and values (R19C4:R?C4) based on the number of cells with data in them for a given column. In addition I believe I'm having troubles with sheet references, when the txt file is opened the sheet name is set to the file name, since I want to use this macro on many different files I need the sheet referencing code to account for this. Anyway here is the section of code which seems to be giving me trouble (the initial sections just create a new column of data which needs to be referenced as the xvalues) ...
View 3 Replies View RelatedI have a vast database where I have linked charts. I send the data from a macro to the database and sometimes there are 1000 inputs and sometimes there are only 20.
How would i set up my charts to only graph the inputs that are present?
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
I'm attempting to create a dynamically built donut chart using VBA, but I'm having trouble inserting a variable to be used as one of the values.
The chart graphs 3 data points:
ASC GP - Located in cell T4
ACC GP - Located in cell AE4
ACT GP - variable called LocSumActGP
The code snippet I assumed would work looked like this:
Code:
.SeriesCollection(1).Values = "='Location Summary'!$T$4,'Location Summary'!$AE$4," & LocSumActGP
But it doesn't seem to like that.
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies View RelatedData set has three columns: dates, cycle times for event and event type. I want to create a XY chart with cycle time ploted against date (filled circles connected by line). I further want the color of each of these symbols to change as a function of event type. I vizualize a chart where the symbols might be red, red, blue, red, yellow where red indicates event type 1, blue event type 2 and yellow event type 3. how I might build this chart.
View 1 Replies View RelatedI am trying to produce a line chart, which will use imported data on a separate sheet (but within the same workbook)
The imported data can be of different sizes ie column widths and length. Some columns may contain only zero and should not be included in the chart, and will be to right of the data.
I have created a named range for the data series called ChartData using =OFFSET(Data!$C$1,0,0,info!$C$7,info!$A$2) - C7 and A2 are calculations to set column and row sizes. I have tried to use this as the chart's data series, but without success.
I want the below code to run from R1C10 (i.e. cell J1) to the end of the data in the row, which will vary in position, rather than having to define where the end of the row will be beforehand (i.e. R1C21).
Is this possible?
I have some code to plot a column chart of data but it isn't working as expecting at the moment. The code is below. The variable binCounter is a count of how many cells in a range that I want to plot on the chart.
However, what I am finding is that the first couple of cells in the range appear as the series name with the rest appearing as the data in the chart. Secondly, the chart appears with the axis labels 1,2,3 etc when I have some custom ones I would prefer to use. How do I go about setting this property, as I can only find options on setting the axis title There is a lot of stuff on XY charts on Google but I can't find much on column charts unfortunately .
VB:
'activate sheet and chart
Worksheets("Home Page").Activate
ActiveSheet.ChartObjects("Histogram").Activate
'set variables for chart
With ActiveChart
[Code] .....
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
I have a Choose function that is used to determine which range I need. e.g. =Choose(Choice,Cell,Cell,...).
In each corresponding "Choice" cell is a typed range. e.g. $B$33:$L$70
I'm looking for a way to be able to use this typed range as the range for some chart data.
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")
I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.
the first two lines of code work fine, the msgbox shows "E100:E105", happy days!
However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.
VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup
MsgBox Range(thisString).Address
Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)
I am using the code below to copy a range and paste it over a variable range.
View 4 Replies View RelatedI have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
I have daily information that I am tracking for the year. From this information I am making 12 monthly charts. When I started the file I was able to format the X axis and choose the minimum and the maximum value it would graph (1st day of month/last day of month). The graphs also show the trendline.
For some reason the February chart is set to March. When I click on the X axis and go to format axis option, I no longer have the option to set the minimum and maximum values. The first thing is lets me adjust is the interval between tick marks.
The vertical axis still lets me choose the min and max values.
I have a range "C1:Cx" where i want x to be the value from a value "overallLastRow". How do I write this?
This is just a simple question but I can't seem to find the answer in any of the hepl files. I want to refer to a range by using a variable but can't get the syntax right.
for example instead of
I am in need of code to sum the above range. But it is different range every day. My company will add the deposit amounts each day from each deposit sheet.
ie:
48000
80000
12000
3000
200
45000
10000
And so on. I have posted an example spreadsheet if needed. (The portion is green is all that needs to be summed but keep in mind it changes amount of rows included changes each day, and the yellow is where the solution goes, but I can easily change that). There are other formulas needed but I have figured those out. I need to run this macro daily just for the last group.
I have a problem of setting a range of row that might expand regularly.
I tried a simple method but...
Code:
' Count the used range row
Dim count As Integer
Dim usedrow As String
count = ActiveSheet.UsedRange.Rows.count
usedrow = "C" & count
' The items are in A1:A105
Set AllCells = Range("C4:usedrow")
I left my Excel books at home, so I can't even look up the answer, and it's incredibly frustrating, since I know it's something small that I'm missing.
I've got a code that I want to cycle thru 12 ranges, named "Retrieve1" thru "Retrieve12". This is the snippet of code that I'm having the issue with is:
For I = 1 To X
NextRange = "Retrieval" & I
Range(NextRange).Select
Application.Run Macro:="EssMenuRetrieve"
Next I
I only made the "NextRange" variable because I originally had "Retrieval" & I in the range select statement and it didn't work.
I am using this coding
LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Range("A" & Row.counts & ":" & "D" & Rows.Count).Copy Destination:=Sheets("Sheet2").Range("A" & LR + 1)
And it keeps giving me the error "Object required"
I am trying to copy from A-D and the row is a variable to which row was checkmarked at that time.
I am trying to create a spreadsheet that will predict the quantity of a product sold in a given month based on the number of new clients that month and the frequency with which the existing clients will need that product to be replaced. E.g. Washing Up Liquid is required by every new client and they will need a new one each month. Toilet Cleaner is required by every new client and they will need a new one every 3 months.
I have 'hard coded' the two examples into the formula on the "Non-Variable" sheet. However the Frequency of Replacement is speculation; it may traspire that a bottle of Toilet Cleaner only lasts 2 months, in which case I would have to go into each cell and change the formula to reflect this. I believe Add or SUM Every nth Cell may hold the answer but I am failing to get it to work with my data due to being a novice and not really understanding the formulae used! The second sheet is my attempt.
As you can see, it counts 3 months forward from the first month, not 3 months BACK from the CURRENT month. It also simply doesn't work: Washing Up Liquid should equal 285. Ideally I would like to keep the format of the 1st sheet as this matches the rest of the the workbook (not included).
still trying to figure it out how the logic in VBA works
'this selects a block of sells and put them in variable rormu. Right?
Set test = ActiveSheet. Range("i65535").End(xlUp).Offset(-1, -3)
Set test2 = ActiveSheet.Range("i65535").End(xlUp)
Set formu = Range(test, test2)
'here i try to create another variable for a range after last used row
Set newg = ActiveSheet.Range("f65535").End(xlUp).Offset(2, 0)
Set newg2 = ActiveSheet.Range("f65535").End(xlUp).Offset(3, 3)
Set pst = Range(newg, newg2)
I have reached a point in my code where I have a variable range selected. I want to autofill the selected range of data to a new range.
My code is:
Range(Cells(7, LastReportColumn - 1), Cells(LastReportRow, LastReportColumn - 1)).Select
Set DestinationRange = Range(Cells(7, ReqMonths + 5), Cells(LastReportRow, ReqMonths + 5))
Selection.AutoFill Destination:=(DestinationRange), Type:=xlFillDefault
The select part works. However on the second line (set DestinationRange) I get a type missmatch.
I searched and I seached but I cant find the answer. I want to paste a set of cells in a blank range that is next to data already in the workbook. I cant get the right wording for the variables in the range I want to paste to. Here is what I got so far, the red text is what I am getting hung up on. (ya I know it sucks)
Dim mycel1 As Range
Dim mycel2 As Range
Set mycel1 = Range("A1").End(xlToRight).End(xlDown).Offset(0, 6)
Set mycel2 = Range("A2").End(xlToRight).Offset(0, 1)
Range("N2").select
Selection.Copy
Range(mycel1, mycel2).Paste
Dim varAAR As Variant
varAAR = Range(Selection, Selection.End(xlToRight)).Select
. Range(Selection, Selection.End(xlDown)).Select
But the way I understand it, varAAR will only equal the first line, I want it to be the range that is selected from both lines.
