Referring To Array And Adding / Removing One Preset Item
Apr 19, 2014
I'm trying to automatically add and remove one criterion from an autofilter without disturbing the previous existing criteria The autofilter code sounds like this:
How would I go about referring to this array and adding another item called "Hard" to any pre-existing elements in this array?
I plan on attaching this code to a button and use it as a filter, so when the button is pressed, the item will be added to the list of criteria, and if it is "un-pressed" the item will be removed.
First it should Insert a Column to the left of A. Then it should go to column B and search for the number 99 and delete all rows after it. In column C I want it to do the following formula =vlookup(b2,descriptions!$b$2:$c$250,2,false) and have it copied all the way down to the end of data.
Next I need labels added to the tops of the rows Next I need the description columns in C to be copied to column I Finally, beginning at J2, I would like the formula (e2 * g2)/100 entered to the end of data. Attached is sample data.
Is it at all possible to refer to a array that may change in a formula?
For example I need to use a Vlookup formula, however the table array will change depending on the value of another cell.
I need the user to be able to select the column heading that the lookup should work off from a drop down list. So if the user selects column heading C, the array should start from column C though it will always end at column Z. If the user selects column heading Y the array would be Y:Z.
I am trying to use the LinEst function in a program. There are some good examples on the web but they all refer to a range in the format “A1:A45”. Is there a way to refer to a column in an existing array instead?
I have about 10,000 part numbers all with descriptions. These descriptions do not follow a uniform logic;
============= Part 1: 5x10 Red Cotton candy Part 2: Yellow 6/18 x7 TTC x11 Picture Frame =============
My dilemma is that I need to figure out a way to remove everything aside from the "5x10" and "6/18 x7 TTC x11" from these descriptions. I just need the the item sizing.
What I've come up with so far is sorting items by description and working with batches of similar descriptions, then doing replace "Red*" with "blank". It works but it still takes me too much time and it's not perfect.
My question is there something else I can do that is easier and more accurate?
I would like to add one more item to match. I need to match column H in my current sheet, to that of column E in sheet 3. Just not sure how to go about it.
I have a column of data showing trade results - in the most basic way I can explain, as my account value GROWS by a preset amount (say 20-30%) how can I ADD to the number of contracts I am trading. For example I start with $10,000 and the account grows to $12,000 or 20% - according to my own trading rules, this allows me to ADD an additional contract to trade (I start with 1 contract). As the account continues to grow progressively 20% from the previous 20% jump, I add ANOTHER contract.
and to top this off, I do the same in reverse, If I lose or go into a drawdown .. how do I reduce my number of contracts traded?? so lets say I am up to trading 4 contracts and I lose say 10% of the account value - I want to slow down or "ease up" the number of contracts until I get the account back to its previous high. Anyone know how this can be programmed??
One last final caveat - when you go on a winning streak, I add for every 20% INCREASE in the account value and when I lose I reduce contracts at every 10% pullback to preserve winnings as much as possible.but I am ALWAYS allowed to trade a MINIMUM of 1 contract
I have some old code I use to use that still works but instead of adding to the top menu, the new command button is added to the add-ins menu list and then burried with other items.
Public Sub test() Set myMenuBar = CommandBars.ActiveMenuBar Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, temporary:=True) newMenu.Caption = "Blotter"
I have an excel column that includes many numbers. I want to add them to the listbox. But there are same numbers in the list. What I want to do is to add each number to the listbox only once. Example:
This is what I have__________________This is what I want 622768____________________________622768 622768____________________________631215 622768____________________________635054 631215____________________________647461 631215____________________________681216 635054
I want to use 4 toggle buttons to be able to add and remove 4 different series to a chart. When the toggle button is clicked, a specific series is added to a chart, and when it is unclicked then the series would be removed.
The problem I am running into is that unless I add and remove each series in a specific order, I will get an error because I may be trying to remove a series whose series number is not on the series collection in the chart.
for example: if I remove series number 2 with the toggle button for that specific series, then the toggle button for series number 3 will remove the old series 4 (which is now 3), and the toggle button for series 4 won't work because series 4 will now have become series 3.
Therefore, would there be a way to remove a series based on the name of the series, rather than the series collection number?
I have a named range on a hidden sheet which populates data validation lists on 'sheet 1, cells b5-b55. My question is this...Is it possible to, upon entering a new item in b5 (for example), to have that item automatically added to the named range? (preferably through a button on a pop up form that opens w/ the question 'would you like to add to the list?, similar to an access form)...I've seen one example in the past which I found with a link from this site, but couldn't make it work. If the answer to my question is yes, could you also suggest a location of an example if you know of one?
Is there a vb code to remove single item from an array by specifying the index. for example, MyArray ("A", "B", "C", "D"). If I want to remove "C" from the array, is it possible to somehow remove it by refering to it by its index (2). Does 'RemoveItem' command only work for a ListBox?
I have added items to my "Cell" context menu but was wondering if there is a way to move one of the items to the Filter submenu. Below is the code I am using to customize the context menuI want to move the "Filter Status" item to the filter submenu.
Sub AddToRightClick()
Dim ContextMenu As CommandBar Dim ContextMenu2 As CommandBar
'This is for the normal view right click menu Set ContextMenu = Application.CommandBars("cell")
I have problems with dynamically adding and removing checkboxes. I have a form and there is a button to add more rows to the table. Every row includes a checkbox too.
So first i add a row, and then add a checkbox to a specified cell in that row.There is a button for removing rows as well (witch should removes checkboxes as well).
My problem is when i ad the checkbox the linked cell property only works for the first one.When i add the second row the linked cell of the firstly added checkbox changes to the one in the new row and the new checkbox has no linked cell. I am adding the stuffs as follows:
VB: Private Sub addBtn_Click() Dim y As Integer y = findFunc("end") // Y define where To insert the New row Cells(y, 11).EntireRow.Insert Cells(8, 11).Copy
I am working with a friend on their spreadhseet which uses several vlookups in order to pull information cells in one worksheet to another, and also to pull object types from a list with values saved as an array. However, one item in the array consistently does not appear.
I noticed that his array has 4 columns, unsorted, where I would have used 2 (one with the object type, and the second with the value corresponding to that object type) and then sorted them.
The error he gets is "A value is not available to the formula or function", even though the value is in the array.
I have already eliminated formatting, spelling errors, bad formula (it works for the other types), and all I can come back to is the array.
The file is too large to attach, so I am attaching some description of the formulas and the structure of the array.
I am looking to Set an object as a Array, but i keep getting an error, and the book i have does not really go into Set Functions that much, so not sure if it can be done or not?
For d = 1 To 31 ' days in the month
Set Rng(d) = Range(sRng & ":" & eRng)
Next d
sRng andn eRng are already set by date, and I have Dim Rng() As Range at the top.
I have this spreadsheet that I am creating schedules for my employees since our scheduling software at work is horrible to work with. It calculates total hours for each employee at the far right of the rows, and calculates total hours for each day in the columns. The formulas that calculate the hours for each day is set up to automatically deduct 30 minutes from a shift, for lunch, if they're scheduled for more than 5 hours (the gray shaded employee rows are for managers and that only deducts 30 mins if they're shift exceeds 8 hours).
I'm familiar enough to put somewhat complex formulas together, but I don't understand it enough to always make them work correctly. What I'm wanting to be able to do is add or subtract entire rows (add new employees or remove old), and have my formulas for calculating the total hours to still work. Right now if I delete a row the cell with the formula in it says #REF!. I can fix this by going through the formula and manually deleting everything in that contains those other cells, but the formula is huge, and it's pretty tedious going through the whole thing when it feels like there should be a simpler way of doing it.
TLDR: I want to be able to add/remove a row and have my cells currently on row 36 update the formula correctly.
I am trying to find an item within an array and then VBA could execute code.
Here is an example:
[Code]....
LookupItems =("text", "value", "book") or should I acutally be using Split("text,value,book",",")
For Each sht in ThisWorkbook If sht.name = array(LookupItems) Then.....execute code
[Code] ....
So basically in this example I want to loop through all the sheet names in the workbook and if any of the names in the arrary are found it will execute the code for those particular sheet names.
I know alternatives are the select case or write an if statement for each value I am looking up or even use an OR for each value to lookup; but I just wanted to see if this method was even possible as it would be less coding.
I was thinking something like... but this doesnt get rid of the blanks... (this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.
------ A ------------------- B John123@gmail.com--------Blue Bill323@gmail.com ---------Red Sue223@gmail.com -------Green Sue223@gmail.com -------Yellow Bill323@gmail.com ---------Red Bill323@gmail.com --------Yellow John123@gmail.com ------Yellow Sue223@gmail.com --------Blue
- C --------------- D --- John ------------Blue, Yellow Bill --------------Red, Yellow Sue------------Green, Yellow, Blue
I am using Excel 2013 on Windows 7. In the above example columns A & B is the given list to process, and Columns C & D contain the result I am trying to achieve. The major part of this that I am having trouble on combining, separating them with commas in another cell, and ignoring a duplicate value. You can see bill has two red values, but I only need it displayed once in column D.
I started changing it and then realized that I can keep going by adding new elseif's but i think an array would be a better option(i think). But i am really a monkey see monkey do kind of vba user. i can backwards engineer but cant come up with it.
here is code
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("$z:$z")) Is Nothing Then If Target.Value = "NO CONTACT" Then Target.Offset(0, 1) = Date + 1 ElseIf Target.Value = "NO CNTC FCL" Then Target.Offset(0, 1) = Date + 1 ElseIf Target.Value = "NO RES" Then Target.Offset(0, 1) = Date + 1
you see i keep adding "results" and assigning lift dates. what i would idealy want to do is take my list (that i use in a drop down box in $z) and next to it assign the number of days i want to set the next call date(the offset 1 cell) to be. then have the code just read cells a1:a10(the results) and assign the corresponding b1:b2(the amount of days to wait to call). I can keep adding elseif's but then if i decide to add new results codes or change the days from 1 to 5 i have to go back to the code.
I have two large spreadsheets that are arrays. Column A contains 40 items in both files. Columns B through X contain unique names. The arrays are filled with dollar amounts. Is there some formula or function in Excel whereby I can easily combine both spreadsheets, essentially adding the dollar amounts for each corresponding cell?
I have a spreadsheet with time values incremented in column A.
In column K, there are numerical values that correspond to the time in column A.
Elsewhere, a specific time is chosen from which to begin the summing of the values in column K.
I need to be able to sum the values in column K until preset values are reached. Then I need to know at which corresponding time value the summing loop stops.
For example, a time of 11:00 AM is chosen as the "start time". I want to sum the values in column K until they are >= 2.000.
At that point, I need to know what the cell value was in colum A that is in the same row as the last cell value in column K.
I can't seem to do it with simple Excel functions without falling into a self referencing loop. So what I need is for a VBA loop to lookup the appropriate values in column K, sum the values, check the total, and continue the loop until the preset total is reached. Then look up the value in column A in the last row of summed values, and return that value.
I have a pivot table where i would like to filter the values according to top 10, bottom 10 etc. I know that it works with using the filters in the pivot tables (Value Filters - Top 10 ...), but for making it easier and faster to use these filters, I would like to have some preset filter options as buttons or a drop down menu that immediately display them.
So that you don't need to choose the filter values yourself. I need to be able to either filter the e.g. Top2 Business Areas or within one Business Area filter the top 3 Business Groups. I thought about maybe making a drop down menu where you can choose the business areas and then another drop down where you can choose the Filter such as Top10, Bottom 10, Top 3 etc.
