I have 2 tables, both with 3 columns, in the same worksheet. what i need to do is to check if the values in A1:C1 is contained anywhere in a row in the second table and so on. i need it to return a match for all 3 values.
View 2 RepliesI need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could
use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false;
I don't need to know where the nine is or any other info. I just need to know if it is there.
In a database, i need to search and select the cell that contain a given string. If found, from that cell range, i want to grab some info using the offset command.
I'm trying to use the Range.Find function. I got an error on my formula
what: "IP_"
From cell ("T1)
Look in Whole cell
Look by Row
XlNext
[CODESub GetInfosFromData()
Dim st As Range
st = WorksheetFunction.Range.Find("IP_", Range("t1"), , xlWhole, xlByRows, xlNext, True)
If SG IsNothing then ' A match occured
' do the process
End Sub][/CODE]
how do i tell the subroutine to look for any subsequent match (Next)
I'd like an if function to display "Yes" when a postcode is found within a range of postcodes.
For example if I have BT23 4RE in cell b1 and a range of postcodes from say cell a1:a76 which has BT23 4RE in it, it will display yes.
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.
I want to use the find function on a range and search for a "," character. If I get a "," I want to return a True otherwise false.
I tried just recording a macro using the find function but no code shows up.
how to use the find feature in a macro, can this be done?
excel 2010
I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
[Code] ........
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
I have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies View RelatedI have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
i have spent 40 hours, and still didn't find a solution. Please help is need it!!
example:
i have to find all articles with same code (222). first one has Q =100 second one Q=250.
soled Q=150
(i am talking about 5000 rows, 400 different or same articles per month- 12 months)
columnA-----columnB---columnC
222 ----------100---------0
111-----------50
333-----------70
222----------200---------200
333---------- and so on
first i have to deduct from the first one it finds (max. till 0 ...it can not be negative) ....after finding another one it deducts the rest---it means 50
Is there any kind of formula with this possibility.
if it is poorly writen please let me know for more info.
i am not an expert in excell, but i have tried variations of sumif, vlookup functions, but i always get stuck deducting the whole Quantity from all of the same (222) articles .
I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula:
{=SUMIFS(range to sum, range to evaluate, evaluation criterion)}
The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999.
So my formula looks like this:
{=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)}
This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
So I have 2 worksheets. One has a list of Player Names. The other has a list of Plays that any of these players could have been involved in (or they may not have been involved in).
I am trying to use the find function to find anyone of those player names from sheet 1 in each of the plays. My functions currently stands at
=FIND(('Dinas Roster'!$A$2:$A$21),I6,1)
Where Dinas Roster is the list of players and I6 is a particular play and would go to I7, I8....which are the plays,
This isn't working as I am getting all #Value. If I just do it for 1 Player (i.e. Dinas Roster'!$A$2) it works and returns a number on each line that player shows up in...
I need a find anywhere function.
Example
text to find is in ZZ1
area to find the text is A1:BB500
It could be anywhere in that area.
I don't want to do the find command 1000+ times for all the data i need to search for.
Vlookup is just column A, i need column A:BB
I have no idea what column or row it would be in.
basically, look for text from sheet2A1 anywhere in sheet1
I'm looking for something simple that will let me type in a 5 digit numeric value and then when it finds the associated value, will add +1 to the associated col/row.
Such as, type 11111 in the box, click the button...When it finds the data on say row 8, col A it will then add +1 to row 8, col G.
I'm not sure of the best way to do this...I'm not exactly new to excel, but it has been a VERY long time (10 years!) since I've used it in this aspect...Just trying to help a friend out.
I have this source data that has different types of currencies in it. It will be processed by a marco. But Before i do the processing, i would like excel to check if all currencies in the source data have had their rates determined by the user in another worksheet. This is the code i am using. However, when the marco trys to find rates that has not been determined (cannot be found in remarks sheets), it will just skip the msgbox code.
Find = Cells.Find(What:=local_currency, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
If Find = True And ActiveCell.Next.Next.Value <> "" Then
Find = Empty
Else
MsgBox "The currency " & local_currency & " does not exist in the currency list"
Sheets("Remarks").Select
End If
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
View 9 Replies View RelatedI want the create the following summation function (probably with the use of FIND).
You have 3 columns:
- Column A containing values; a, b, c, d, e
- Column B containing values: 1, 2, 3
- Column C containing ad randum values f.e. lengths
I want the achieve a summation of the lenghts with the following conditions 'a' in column A AND '1'in column B.
Apart of this a summation of the lengths with the conditions 'a' in A AND '2' in B.
[Code]....
In Sheet 2 i have a 1000 of data contains the birth date of following customers
The result i want in Sheet 1 is
particular on todays (Say on 27-11-2008) date how many customer are having birthday, supoose there 10, or 8 wahtever should show me the list.
I tried this formula
=INDEX(Sheet2!$B:B,MATCH($B$3,Sheet2!$H:$H,0))
but by this formula it only show one customers birthdate what i want if there 10 differenrt customer those same birthdate it should display all the 10 date and name in diff rows
Attached is a sample in which Column"B" contains Total IDs and Column"D" contains worked labour IDs. Now I want the IDs of labour who have not worked in Column"F".
Need a function that returns the days from the last week of a month?
View 3 Replies View RelatedI use a lot the function “Find” in Excel but the problem is that it takes a lot of time, so I’m searching for another function or code that can be faster than that, the Worksheets that I use in Excel contain thousands of sheets so it takes hours to execute the Macro.
View 10 Replies View RelatedI have a column of values (text) to choose from. I need to randomly assign them to names.
There are more values then names.
Each name has to have a value.
Each value has to be used only one time.
Not every value has to be used.
Column B contains names, column Q contains values.
Now. So far I've come up with below:
Code:
Sub randomize()
Dim Random As Integer[code].....
"Random" returns exactly the amount of values in column Q, so this works ok.
"i" counts iterations correctly, meaning as many times as names in column B.
I tried useing Match to rule out already used value but this code returns "unable to get Match property of the WorksheetFunction class" error.
I have a worksheet with dates on and have user form to display dates within a range. I have created some code, but the find function errors and says it cannot find this value on the sheet, but it is definatly there. This is my code: I have added an asterix to where it errors and says it cannot find the value
Code:
Private Sub SearchButton_Click()
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Dim FoundFrom As Range
Dim FoundTo As Range
With Worksheets("Calender")
[code]....
I have two spreadsheets, which have over 10,000 entries and I am trying to compare them both for duplicates. I would like to know whether or not there is any VBA coding which will allow me to copy a cell from one spreadsheet and look for it in the other spreadsheet.
The major problem which I am facing is the information which I am trying to look for is constantly changing and therefore is more or less unknown to me. I am hoping for some VBA coding which will allow me to copy whatever is in cell A1 and find it in the other spreadsheet which I have, and then do the same for A2,A3 and so forth.
how to make the << and >> buttons as well as the find button work on this form. Here is the code for the >> (next Record) button. I think if I can understand how one of them is supposed to work I can do the rest. I have been trying for days to figure it out, and even with RoyUK's help I still can not get it to work....
View 9 Replies View RelatedI am having trouble with the following code. When I put in a value in C8 that is on the list being searched (A2:A27), the Answer is still coming up as false (ie, the find function isn't finding the variable in the list, though it is there). I'm guessing I'm using incorrect syntax somewhere.
Dim Answer As Boolean
Private Sub CalcBi_Click()
Dim Pledge, Edate, PR, PPA, EEA As Double
Dim Due, Chdate As Date
If Range("C6").Value = "" Or Range("C8").Value = "" Then
Exit Sub
Else
Pledge = Range("C6").Value
Edate = DateValue(Range("C8").Value)
Set rngschedule = Worksheets("Bi Weekly Schedule").Range("A2").Offset(Application.WorksheetFunction.Match(Range("C8"), Worksheets("Bi Weekly Schedule").Range("A2:A27"), 1), 0).....................