Lookup Nth Occurence In Range & Limit Rows To Search Based On Cell Value
Jan 14, 2009
I have a UDF for a lookup_occurrence formula (thanks to Dave), and I was wondering if it was possible to tweak the range it looks at with if statements.
I have a formula like this =Lookup_Occurence(B13, INDIRECT($N$5), 1,1,5) where N5 says exit card 1.
I need it to only look at a certain rows within exit card 1 depending on the value entered in I5.
If value I5 says Period_1 it will only look in rows 12:46.
If value I5 says Period_2 it will only look in rows 48:82 etc.
I believe I need to modify the xl look part of the code with if statements.
Below is the code. I am wondering if I can replace the xlLook line with nested if statements. If I5="Period_1 then rows 12:46" etc.
Function Lookup_Occurence(To_find, Table_array As Range, _
Look_in_col As Long, Offset_col, Occurrence As Long, _
Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
Dim lLoop As Long
Dim rFound As Range
Dim xlLook As XlLookAt
Dim lOcCheck As Long
I am currently using the simple code block below to jump to a cell on another sheet based on a user-inputted value to a cell on the current sheet. Although jump is too strong a word at the moment...walk slowly might be more appropriate.
The problem is that the sheet that contains the target cell hosts a very sizable used range and the FIND function can sometimes take almost a minute before finding and focussing on the required cell.
While I do not believe that a search range below that of sheet level for the FIND() function is possible, the cell that I am looking to jump to resides in a one-column, sorted, dynamic named range (=Bookings!JobID) on the target sheet, so I am sure that it must be possible to find and go the target cell in a much quicker timeframe than that being delivered at the moment. Just can't find something suitable at the moment.
How can I find the 2nd occurence of a value in a list and lookup a value in the same row?
My data looks similar to this: Name.............Order No Alan...............1234 Bob................4567 Steve.............7890 Alan...............6543 Steve.............0985
I have a sheet with a hierarchy of operating units in a column, with DirectX checkboxes next to each (used a VBA script to auto-create the checkboxes ).
The checkboxes go from D5:D147, so 142 checkboxes. They are named checkbox_D5, checkbox_D6 all the way to 147, so checkbox_[Column]&[Row].
Based on a True/False value in another column to the right (BA or column#53), I am hiding specific rows. Just to give a sense, if a checkbox for a Level 2 hierarchy is clicked, I have some formulas to determine which Level 3 rows will be shown by putting a TRUE in column BA. For sake a speed, I created these to only run for 30 rows per click (that's the most that would ever change on a checkbox click).
I did not know how to do it any cleaner, so I created 142 of these:
Code: Private Sub CheckBox_D5_Change() Dim i As Long For i = 5 To i + 30
[Code]....
It works OK up to checkbox_D23 or 24, but excel seems to reduce the number of rows it goes through for each checkbox below that, with checkbox_D31 down doing nothing. I read somewhere about a 32 form item limit, wonder if I'm hitting that.. Cleaner way to do this vs. 142 instances of checkbox_XX_change() events?
I need the array portion of a lookup formula to change based on a cell value.
On a worksheet(named "groupings")that groups students according to ability level, I have data validation lists where teachers select the criteria for the group. In cell I5 they choose the period, and in cell L5 they select which assessment to look at.
Cell B8 will either say high low or medium. Then in cell C8 I have this formula: Lookup_Occurence($B$8, ??????, 1,1,2)
Again the array will depend on what is entered in cell I5 and cell L5.
I have attached one of the worksheets the lookup_occurence will be referencing. In this exit card worksheet if cell B10 (the name of the assessment) matches what the teacher chose in cell L5 on the groupings worksheet, then the array formula will be 'Exit Card'!Card1. If cell G10 matches what the teacher chose in cell L5, then the array would be 'Exit Card'!Card2. Where A9:C339 is named Card1 and cell F9:H339 is card2 etc.....
However, it gets even more complicated then that because within Card1 there are 8 periods, and if a teacher chose period 1 in cell I8 on the groupings sheet, then I only need the array to look at the period one data in the card1 array.
Essentially what I am trying to accomplish is create a page where teachers can group students by ability level according to any assessment they choose. On the exit card page the formulas currently equaling # Div/0 will say either high, med, or low depending on how a student performs on that assessment.
I want to be able to send out one “master” workbook with the fourteen ranges, and use a pull-down list (or anything else) so when the user selects a state, the proper lookup range will be used. In the attached abbreviated example, I want the lookups in Column B to refer to the state designated in B2. In this case, the VLOOKUP works correctly, since Colorado zip codes are in the ColZip range.
The people in Idaho or Oregon using this will only be working with their respective zip codes in ranges IdaZip or OreZip, so if they enter their state in B2, I would like the VLOOKUP to look in a different range. My whole idea is to only have one master workbook so I don’t have to create one for each district office. Maybe I’m thinking in a wrong direction altogether.
I want to count if cells in a specified range =yes then divide the range to get a total percentage of yes cells. I have a data validation list set up with the values: yes, no, n/a. I don't want the total percentage to change when using n/a.
Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".
What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.
Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.
I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.
I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;
1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively 2)define range to be searched by how many contiguous values there are in column D 3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on 4)define new range by next set of contiguous values in column C 5) step 3 6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter 7)finish when all combinations are searched for
Sub CountComboOccurence() Dim ListCounter As String Dim ProgCounter As String Dim Counter As Integer Dim ComboValue1 As String Dim ComboValue2 As String Dim Rng As Range Application. ScreenUpdating = False ListCounter = 0 ProgCounter = 0 Counter = 0 ComboValue1 = 0.......................
I've been trying to put together some code to hide a range of rows on one page based on cell values on a different page. So basically Sheet1 cell A1 value is 10, A2 value is 20. When macro is run via button rows on Sheet2 from 10-20 would be hidden. The issue is the values in the cells on Sheet1 will vary. I thought something like the following would work but it doesn't.
I can't seem to make SumIF or Vlookup do what I want here.
I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column.
If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3)
a macro button on my excel sheet that should do the following.
i have a range (Ex: E3:E46) which is being continuously filled, i need to transfer the information to another sheet depending on column "E", (Ex: if "E3" = "A" paste row to sheet2 if = "B" paste row to sheet3) and so on each row could have another string in column "E".
after the paste is done it should clear the range making place for new entries, that should also be copied finding the next empty cell (it shouldn't delete the old entries)
1. Where there is data in column B 2. Goto first row of data 2. If column J = "Closed" 3. then select Cells from column B to J in that row and colour these cells in red 4. If column J= "Open" 5. the select Celss from column B to J in that row and colour these cells in grey 4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
=============================================================== Sub Colourclosed() Sheets("Risks").Select Dim LastRow As Long Dim i As Long LastRow = Range("B" & Rows.Count).End(xlUp).Row For i = 8 To LastRow If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select Selection.Interior.ColorIndex = 3
[code].....
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
I am working on a time management sheet for my company. I need to be able to click a button and have the file search 3 columns for a persons initials and then hide all the rows where the initials are not in at least one of the three columns. I seem to have no problem getting it to work for one column at a time, but as soon as I try to search more than one it all falls apart.
I will also need to create an unhide all button to reset the sheet after the macro has been run.
I have attached a sample of the sheet below. Each project needs to have 3 rows to show the schedule and budget broken down by each team member.
I have a table in excel with a group of headings. (Serial Number, Model, Description, Repsonsible and a few others) What I would like to do is be able to search every cell within the range of that table. When the user clicks search an inputbox is displayed and you can enter any search term you like. if there is a match within the cell range, i want the entire row (and the heading row always at the top) printed into a new sheet to display results. There may be a match in multiple rows, and id like evrery row displayed.
I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change.
I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell.
For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.
I'm trying to sum a range of data consisting of 12 cells (each range is in a unique row going across adjacent columns). The range of data to be summed is based on a lookup value which is a column header - I want the range to start with this lookup value. In column B of my spreadsheet, I have a 'First Activity number code' going down...In Row 4, I have the same First Activity Codes going across the row (the column headers). In Column U, going down the column I am looking for my formula.
So For example, in row 5 of my spreadsheet, I am looking at 73 as my base code (the value in column B), and I want a range of data summed that lies in the same row where the column headers are 73 - 84 (12 rows) - always starting summing the range with the same number that is in column B. So in row 18, 78 is my base number; so I would want the sum of the numbers in the same row where the column headers are 78-89 ( 12 columns).
I'm having difficulty finding a way to ask excel to lookup a value based on an array spanning multiple rows and columns. There's a mock example in the attached.
I have used the below formula which works in a standard cell, however I would like to have this is a VBA code using the Target.Offset option, however I can not get this to work. There may also be a better way of doing this.
The code is looking to see if the 1st cell (A118) is empty or not, if it is empty do nothing otherwise it then looks up the value in the 2nd cell (B118) and assigs the appropriate name from the range values.
I have a spreadsheet and I’m trying to create several different staffing scenarios Basically what I want is to populate column E with the amount of staffing based on the number of classes in column B. The range that I want to apply is in the green box in the sample. I’m trying to have excel give me the staffing value if the current number in cell B5 is a certain value – so in the example 1 through 4 = .2, 5 through 8 = .4 and 9 through 12 = .6. this site gave me some ideas so I tried V lookup and Index but I seem to be missing something and wonder if it is not combinations of nested functions?
I'm trying to create a formula that will search for a specific text in "sheet1" within a date range and then SUM the total on "sheet2". I've manage to use the SUMPRODUCT (below) formula but want to expand on this so the SUM can be shown on another sheet(summary page). =SUMPRODUCT((ISNUMBER(FIND({"nmlclex06"},A1:A10)))*(B1:B10)) In this example I search for the text "nmlclex06" and SUM the size, but don't have a date range and don't know how to SUM the data on another sheet.
Basically I have three sheets. MAIN, Sheet 1 and Sheet 2
Sheet 1 and 2 are in the same format
A3 down is a list of country names and then B3:I71 contains the data im interested in.
I've been trying to create a function that looks at B3:i71 to see if any cell in that range contains a value greater then $0.00. If it does then the row that contains the cell with a value greater then $0.00 (between col A to K) should be copied to sheet MAIN from cells B3 down. This should ultimatley produce a list of data for any row containing a value greater then $0.00. This process should then be repeated on Sheet 2 and should join the list below sheet 1.
I have an excel sheet where I would like a user to enter a start date and end date(say Feb 1, 2013 to Feb 28,2013). And these dates will be able to change to whatever month the user wishes to look up in a specific date range(column A has 365+ dates). This post is closely related to [URL]... but I cannot figure out how to get it to work from my case.
So working off the same worksheet, I have the beginning date in AC35 and the end date in AC36 in which the user enters. I would like the dates that fall in this entire month, including the beginning and ending dates to be referenced when looking for the specific day that contains a value I have in S35.
In other words, I have data in columns A through N. The dates are in column A and S35 is a value obtained from using Max(L185:L526) where L185:L185 is manually selected each time I want a different month. I don't want this to be a manual process of scrolling down the spreadsheet to get the next month.
I have a user form that writes data from textboxes 1, 2 and 3 into the next empty row in a speadsheet but what i want to do is write the data into rows 1 to 100 then start again at row 1 overwriting the data. The reason is that i have the rows linked to a graph and want to continually show the last 100 results and never have to manually delete old data.
I have a macro that selects a range of about 20 columns and fills down. It works fine on my smaller set of data, but when I use one that has about 40,000 rows I get the error:
FillDown Method of Range Class Failed
It fills down about 7,000 rows, then fillsdown the first cell of the next row, but leaves the others blank. The entire range (including the nonfilled cells) is still selected.
