Sub Region1()
ActiveWorkbook. Names.Add Name:="Region_Name", RefersToR1C1:= _
"=Scroll.xls!Name_1"
ActiveWorkbook.Names.Add Name:="Region_Values", RefersToR1C1:= _
"=Scroll.xls!Region_1"
End Sub
Sub Region2()
ActiveWorkbook.Names.Add Name:="Region_Name", RefersToR1C1:= _
"=Scroll.xls!Name_2"
ActiveWorkbook.Names.Add Name:="Region_Values", RefersToR1C1:= _
"=Scroll.xls!Region_2"
End Sub...............
In the chart Region_Name is the name of the series Region_Values is the values of the series. I have a button for each routine. I want to use only one button instead. How?
What exactly is a Class Module? I've tried doing some searching on Google and haven't really found a straight answer as to what a class module is and what it is used for. How does it differ from a Normal Module?
I update a daily incident report that is sorted by priority of the request as well as the date/time. In the past, I've recorded and run macro's to add a number to a priority so that it can sort numerically. Lately I've been tasked with building similar functionality into more reports. I am wondering whether I can tell Excel what order to sort items into (ie "Sort by HIGH, then MEDIUM-HIGH, then MEDIUM, then LOW)?
With regards to auto-fill lists (for want of the proper name), I report on multiple IT systems that fall into six "system" categories. Is there a way I can set up the "System" column to automatically update the correct data once the IT service is entered, rather than manually look up systems category then click the relevant system in the drop down box?
I want to be able to disable the save query definition option for a worksheet using VBA. Right now I have to right click a cell and then go to Data Range Properties and then uncheck the save query definition box. If there is a way to automate this that would be great.
In my attachment, I have a worksheet where I need to pick a rating from a combo box or list (Expert, Leading, Applying, Learning, Under Performing). I will be picking this rating 10 different times on this worksheet and the selections will not be the same in all cases so the definition I need won't always be the same.
I would like to return the definition (definitions are detailed on another worksheet, Data Elements) to the cell to the right.
I've tried IF statements but when I make one selection from the combo box, all the combo boxes select the same thing. I've also tried vlookup but can't figure it out and can't find an explanation in layman's terms.
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
I have recorded the code below that does what I need, problem is I will use the code on files with various amounts of rows. What can I change in it so it will work on all different size files.
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry() 'TURNS OFF FILTER IF FINDS ONE ON Dim wks As Worksheet
I'll attach an example.. I just can't seem to figure out how to update the range if I have already named it something using the Name Box to the left of the Formula bar.
1) I would like to sum (or average) the top X numbers from a range.
2) I realize that this can be done with the Large function and an array list, and I have done that, however, I would like more flexibility than that.
3) Ideally, I would like to input a value for x in a cell and then the formula would refer to the value and select that many from the range.
i.e. I have rows 5-20, each with 10 values. I would like to average the top 5 values of each row. I can enter 5 in cell C1 and it will return the average of the top 5 numbers for each row (think grade book with 10 assignments). So each student has an average grade based upon their best performance. Then, without changing any of the formulas, I just enter the number 7 in cell C1 and the averages now use the top 7 values instead of top 5 and I do not have to make any changes to the averaging formula at the end of each row.
Through VBA is it possible to give a range of cells that should be converted to array formulas? As part of my macro I am copying a sheet from one book to another. This sheet contains a lot of formulas and so as not to keep the reference of the old book I find and replace "=" with "#" and then swap them back after the move. This works fine for all normal formulas but not the Array formulas.
If someone could tell me how to give and range and convert them all to arrays or can think of a better method of moving a sheet from one book to another without the formulas keeping the references to old book.
This code is coulnm specific, can this code be adapted to look through a range for a match i write this code up to 25 time changing the column it looks at each time, the row always stays the same the location it paste to also always stays the same if it could be made to look through a range in reducing the amount of code to write.
the range it needs to look through would be, row 6 from column B to column Z.
the line i am hoping to adjust for this is below it reads: "WST.Cells(6, 2)Then"
HTML Code: If WS1.Cells(8, 2) = WST.Cells(6, 2) Then WST.Cells(7, 2).Resize(30, 1).Copy Worksheets("torr1").Cells(9, 5).PasteSpecial Paste:=xlPasteValues nextrow = nextrow + 1 End If
I have a worksheet ("Pivot Tables") pivot table ("Breakfast"), and the Field ("Date") in the Row Labels. The date ranges from 1/20/2014 - 2/6/2014. My issue is figuring out a macro to automatically change the date ranges to what I want.
Is there a simpler way to write the code so I do not have to change the dates I want visible each time. But instead just write the start and ending date.
I want to call a subroutine if the user changes a value in any of 14 cells. I see excel's help on this:
Syntax
Private Sub Worksheet_Change(ByVal Target As Range)
Target The changed range. Can be more than one cell.
But I do not know where to put the code. I don't think I put my range right in the Target definition of the subroutine, but rather call the subroutine from some other part of the code with a range as arguement?
Is there a way to make the following code only work when values in a specific column changes...in my case column n, from n5 to n400.
Right now, anytime I alter the value of any cell using some other vba stuff, this code runs and slows everything down. I'd like to eliminate that.
All the code is supposed to do is search column N, and if the column matches a specific value I have placed on another sheet (a string), it changes the color of the entire row to the colors I specify.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range Dim Rng1 As Range Dim var1 As String Dim var2 As String Dim var3 As String Dim var4 As String Dim var5 As String Dim var6 As String
From A1:A500, I have data with different numerical values. I want to be able to use a corresponding column and rows (example: B1:B500) where I can enter formula where maximum values where be reported. This I would like to change with a simple change of a single cell value (example: C1). So, If C1 has a value of 10, it will be used from every b cell to create a maximum value that would use a range going back only 10 cells for A.
Is there a way to enter a number in a cell that determines the range size of a sum formula? An example would be if A1 = 5. The formula in B1 =sum (C1:G1) etc
Function BG(InRange As Range) Range("InRange").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End Function
That so far but not quite sure why that isnt working. I want to change the fill color with a UDF that all they do is select a Range and it changes those fill colors to whatever the Colorindex may be. I didnt find anything while searching the forums with this already.
I have a financial sensitivity sheet setup by year, where for example below each year is a cell you can change the exchange rate in and it effects the outcome of cash flow as these cells are linked to part of the finances. Then I have a "reset" button setup that is assigned to a macro. When you click the reset button all cells in the exchange rate row will be changed to a value that is entered in a "base case" cell. That way various years can be changed but also everything can be set back to the default or base case. My macro for that case is this:
I am wanting vba to loop though a range basically till the last row of the column however within the loop the row gets inserted and the lr which is pre defined does not change. I want to be able to change my lr within the loop, maybe reset it or something...or may be alternative..
I tried using a method where it will loop through a each cell in col however that takes too much times....because it is going through each column...
Code: lr = Cells(Rows.Count, Code: iCol).End(xlUp).Row For y = 1 To lr Cells(y, iCol).Select If ActiveCell.value "" Then
6000 - Starting Balance (changeable) 5890 5800 5950 6080 6100 6180 6250 6300 an so on... (1,000 rows)
Based upon the number "20" that I placed in A1 on Tab 1, I want a formula that looks at the first 20 rows in column A within Tab 1, and then calculate the percentage change between the extreme "peak to trough" of the highest number to the lowest number within the 20 rows...
Below should make it really simple:
6000 - Starting Balance (freely changeable) 5890 -1.83% 5800 -3.33% - This was the lowest percentage change from the starting balance of 6,000 5950 -0.83% 6080 +1.33% 6100 +1.67% 6180 +3.00% 6250 +4.17% 6300 +5.00%
