Jul 14, 2009

I have recorded the below code, is it possible to adjust this so that instead of using "USD" the macro will reference whatever currency the user inputs into say sheet1 cell A1?

View 2 Replies


Using The =IF(ISNUMBER(SEARCH Function To Search Multiple Cells)

Aug 21, 2009

I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.

I can get it to work to search one cell (as below):

=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)

(In this case the respone is No Change as Cell G10 contains "Same")

However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:

=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)

Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.

View 9 Replies View Related

CountIfs And Search (ISNumber) Function Together

Aug 11, 2014

I'm trying to develop a formula that can incorporate the search function in amongst a countifs formula. I have a column that contans the string "2.3 Manage Project Delivery" in a single cell. However, a single cell could also contain this text string in amongst other text and be in there multiple times - E.G; "2.1 Manage Customer Support, 2.3 Manage Project Delivery, 2.4 Close Program, 2.3 Manage Project Delivery" etc

My formula currently looks like this: =COUNTIFS('RDC Register'!$AW:$AW,"Not Overdue",'RDC Register'!$C:$C,"2.3 Manage Project Delivery") but it's not counting the cells that have 2.3 Manage Project Delivery in it more than once.

So basically I need to modify my formula to search for this text string in the cell and add all occurrences to the final count.

View 3 Replies View Related

Nesting Isnumber Search Functions

May 25, 2007

I have this search function that works well: =IF(ISNUMBER(SEARCH("which is < ",E9)),"test ok", "not ok") my E9 cell contains the phase: "I have a number which is < 9, and which is positive..." E9 has "which is < ", then i get back "test ok"

My question is.... how can I do this multiple times within the same function/cell. i.e. if I wanted to search "which is < " and/or "positive" in the same cell, then i get back "test ok", "positive number") I keep trying all combinations to no avail, like: =IF(ISNUMBER(SEARCH("which is < ",E9)),"test ok", "not ok"), =IF(ISNUMBER(SEARCH("positive",E9)),"positive number", "no good")

Keep in mind I am using a numeric example, although I am using this function to check if part of a cell matches specific text...

View 6 Replies View Related

Nesting Isnumber Search Formulas

Jun 8, 2007

I am using the ISNUMBER formula to verify if specific text is found in a string of text.

This formula worked when I only needed to find 1 particular brand name:


Now I need to have 2 different brand names ("BrandB" and "J & B") that need to return a "B" and all others should return a "C".

I found a thread regarding this same topic, but I could not get their solution to work for me.
They recommended using:

=IF(ISNUMBER(SEARCH("BrandB",RC[6])),"B","C")&", "&IF(ISNUMBER(SEARCH("J & B",RC[6])),"B","C")

The fact that my second brand name is written with spaces ("J & B") could be adding to the problem, I don't know. I thought replacing the "C" with another "IF(ISNUMBER" function should work, but I have been unsuccessful at writing a formula that doesn't cause run-time errors.

View 9 Replies View Related

ISNUMBER Function ..

Jan 8, 2009

I have this formula, I have 2 cells that i eneter times in, I would like to have the formula do this..

I would like to have it only display if there is a value in the cell. If value is in both cells then it would display both cells

=TEXT(Inbound!AP25,"h:mm AM/PM") &" / "&TEXT(Inbound!AP40,"h:mm AM/PM")

View 9 Replies View Related

Search Function (locate Data) Search All Sheets Within The Workbook

Sep 14, 2009

Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.

Public Sub FindText()
'Run from standard module, like: Module1.

Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer

myText = InputBox("Enter the text that you want to search for:", "Start Search!")

If myText = "" Then Exit Sub...................

View 9 Replies View Related

VBA: Search Function: Which Shows An Input Box Where You Can Enter A Word To Search For

Sep 9, 2003

I'm from Bavaria, Germany. Right now, I am doing an internship for my studies. my problem: I need a search procedure which shows an Input Box where you can enter a word to search for. It should work like the original Excel search (Ctrl-F), but with a simpler design, like with my own Text "Enter your Query" and a Button "Submit Query" / "Quit search". Is there the possibility to Highlight the Search Target? The problem hereby is that this highlighting should not be permanent. That means the user sees the target for which he searched for, the cell highlighted in a different color, etc. But as soon as the user clicks onto another cell, etc., the highlighting vanishes. If there is no fitting match, there should be a MessageBox like "Sorry the Target xyz cannot be found"

View 9 Replies View Related

How To Search 2 Criteria Using Search Function

May 16, 2014

I have an 'Update' Module that search the value of the Userform1.txt_sc.Value in Column B, but I want to search to 2 criteria, how can i amend the below code to search for 2 criteria ( userform1.txt_sc.value & userform1.txt_linenum.value )

View 2 Replies View Related


Nov 12, 2008

Im using ISNUMBER in a spreadsheet to automatically add a ref number to an entry in the spreadsheet, this is based on the value of a set column. To give you an idea the values are bus services, i.g. 6, 123, 56 etc, when the column has a bus service number entered it auto produces a ref number.

Now the problem is now some service numbers have letters in e.g. X14 X12 36A etc, now these are not recognised and therefore no ref number is given, is there a way of changing my formula to recognise this.

The formula at the moment is as follows

In the ref column (which is locked) =IF(V4=1,COUNTIF($V$3:V4,V4),"")

In column V (which is locked and hiden) =IF(ISNUMBER(D4),1,"""")

View 9 Replies View Related


Jul 24, 2009

The following formula:


is returning XXX when copied down, where I expected it to return XXX as well as XX, X and " ".
Has anyone got a clue what I'm missing (might be a friday afternoon thing...).

View 9 Replies View Related


Apr 21, 2006

I am faced with the daunting assignment of adjusting a payroll report using infrequently exercised logical expressions. I've been careful to try and build my formula adjustments in intermediate steps, so I could test the results, but I can't seem to get past a dual test. I need to create a commission calculation if there are no values in the two columns immediately left of my active cell,col N79, i.e., if L79 OR M79 contain a value, then do nothing, otherwise, multiply contents of G79 * the commission rate contained in named cell "Split3".

This formula works on a single column test:
=IF(ISNUMBER(M79)," ",G79*Split3)

But I can't seem to construct a formula to test both column cells. I have tried this unsuccessfully:
=IF(OR(ISNUMBER(L79),ISNUMBER(M79)," "),G79*Split3)

The above formula is accepted, but I get: #VALUE, with the explanation:
"A value used in the formula is the wrong data type"

View 2 Replies View Related

Can I Use Sumproduct And ISNUMBER Together

Sep 27, 2007

I need to create a formula (Sumproduct) that will look at two criteria then total a third column IF the value in the cell of the third column is a number.

The sumproduct will look at one range A1:A100 and see if it matches initials in cell D1, and it will look at the year in column B1:B100 and see if it matches the year in D2. Then it should total the amount in column C1:C100. But the column C to be totaled includes both dollar values and “N/A”. So If I use something like:


I get the #NAME? I believe because some of the values are N/A instead of numbers. So can I say something like:


(I know this probably isn’t even close, but I hope you can see what I want to do)

I know I can create a separate column to show only the dollar values from column C and use the sumproduct to total that column, but is there a why to skip that step?

View 9 Replies View Related

Isnumber Query

Nov 28, 2007

Can someone tell me how to have 2 options using isnumber.

I'm using ISNUMBER(SEARCH("SUBMITTED",N156)) but I'd like to have "SUPPLY" & "SUBMITTED"

View 9 Replies View Related

Vlookup And Isnumber

Oct 17, 2008

Here is some of my code. It does not seem to work though. can you fix it?

Dim Madrid As Integer
Dim Paris As Integer

For n = 7 to 17

Madrid = Application.WorksheetFunction.IsNumber(Sheets(3).Value("P" & n + 1).Value)

Paris = Application.WorksheetFunction.VLookup(Sheets("Sales").Value("$E:$I"), 3, False)

View 9 Replies View Related

IsNumber Is Not Working

May 28, 2006

Can anyone identify the error in my looping? My IsNumber(f) is not working. If I enter a string, an error message will pop up, but if I enter a number (e.g 4 or -4), the same error message pops up too...what can I do?

I cant really set the inputbox to Type:=1, because I want this inputbox to be optional to the user, i.e If user does not enter anything, it will return a 2.

Sub frequency2()

'Get the frequency of coupon payments per year

frequency = Application.InputBox("Please enter the frequency of the coupon payments", _
"Frequency of the coupon payments", , , , , 1)

'If c = 0 Then 'If it is a zero-coupon bond, set the frequency of coupon payments to 0
'frequency1 = 0
'Debug.Print frequency1
'ElseIf frequency = False Then
'frequency1 = 0
frequency1 = determinefrequency(frequency)
Debug.Print frequency1
'End If
End Sub

' This function automatically sets the value 2 to the Frequency of Coupon Payment of the Bond if the user
' did not enter anything when prompted.
' It also validates if the user entered negative numbers
Function determinefrequency(Optional ByVal f As Variant) As Variant

Dim testt As Boolean

' If values have been entered into the inputbox and
' the userinput is a number
If Len(f) > 0 And WorksheetFunction.IsNumber(f) Then

View 3 Replies View Related

Using IsNumber Formula For Range?

Dec 20, 2011

formula to check a range for any text entires by error.

Example: I have a range of numbers on column A1:A1000, at the end of this range on A1001, I have been trying to use the following formula...


if like on A7 it had "525a" instead of "525".

View 4 Replies View Related

ISNUMBER Giving Me Two Different Results

Jan 24, 2007

I have a problem with isnumber giving me two different results. One time it will evaluate the following as true:


and in the same sheet it will evaluate the same as false.

I need ISNUMBER to NOT recognise "0" as a number all the time.

View 9 Replies View Related

Isnumber Or Istext Formula

Feb 21, 2010

I've been trying to use excel's Isnumber or Istext function to evaluate a range of cells, but have been unsuccesful so far. Is it even possible? or is the formula just restricted to evaluating only single cells?

For example, if I do, Isnumber(A1:A10), is there any way to make that work?

View 9 Replies View Related


Jan 13, 2005

I am trying to create code that deletes lines in a spreadsheet when the first column is either blank or has text in it. However, when writing the VBA code, ISNUMBER and ISTEXT do not work. Amazingly, ISBLANK does work.

View 4 Replies View Related

Conditionally Format IF, ISNUMBER, MATCH

Jan 14, 2009

the attached document. I wish to try and conditionally format Sheet1!D:D depending on if Sheet2!E:E displays a particular letter, when; Matching Sheet1!A:A to Sheet2!A:A Matching Sheet1!B:B if <11 to Sheet2!B:B where cell equals text "Small (7-10)" if Sheet1!B:B is >10 then where Sheet2!B:B equals text "Mid - Large (11-16)"
Matching Sheet1!C:C if cell contains "Hcap" to Sheet2!C:C where cell equals "Handicaps". If Sheet1!C:C doesnt contain "Hcap" match to Sheet2!C:C where cell equals "All Races".

To use an example, if E:E in the cell adjacent to all the matches above equals N then the cell in Sheet1!D:D turns red. Cell D52 would become red. D88, D91, D92, D93 and D96 would also become red. Here was my attempt at the formula, no idea if I was close to the answer.

View 2 Replies View Related

Average If Isnumber Multiple Values

Jul 23, 2008

If A1= 5 ; C1= #N/A ,F5= 7 and E7= 8

Any formula which can average if isnumber A1,C1,F5,E7 ?

it shall ignore errors in this case.

View 9 Replies View Related

Using Search Function In VBA?

Oct 25, 2011

I would like to use the search function in vba to look for a 5 digit number in sheet1(at a specific cell) from a list in sheet2- ie the range. Am I using the range correctly? This code is in a command button in sheet1.

Dim Z As Integer
On Error Resume Next
Z = Application.WorksheetFunction.Search(111, Worksheets("Sheet2").Range("a1:a100").Value, 1)


View 1 Replies View Related

Function Search Value

Dec 4, 2008

I need to create a function that searchs one definitive word or phrase inside of a text in the Excel and creates a vector with the address of the cells that contains it.

Inside of this function, to create the option to change the color of the source or the color of deep, or to erase the indicated cells

Function SearchValue(SearchedValue As String, Interval As Range)
Dim Célula As Range
For Each Célula In Interval
If InStr(1, UCase(Célula.Value), UCase(SearchedValue)) 0 Then
If IsEmpty(SearchValue) Then
SearchValue = Célula.Address
SearchValue= SearchValue & ";" & Célula.Address
End If
End If
Next Célula
End Function

View 9 Replies View Related

Search Function In VB

Jul 14, 2006

Can you use the Search function in VB? If so what's the code? Do you have to call it first ( Call Search .....)

View 2 Replies View Related

IFERROR Or ISNUMBER - Checking Whether Or Not Two Different Cells Contain A Number

Jan 26, 2014

I am checking whether or not two different cells contain a number or the error #NA.

If one of the cells contains a number then the result will be that number.

If both cells contain #NA then the result should be “99”

Formula in Column C
The values are in columns A and B.
If the value in cell A is a number the result is A.
If the value in cell B is a number the result is B.
If the value in cell A and B equal #NA the result is 99

View 6 Replies View Related

IF(ISNumber): Returning A Value For The Alpha-numberics That Begin With DMR

Jan 28, 2010

See attached file...I'm having trouble returning a value for the Alpha-numberics that begin with DMR, which should return 'Dishwasher'. All other values are correct, I'm sure it has to do with the character range but I'm a little stumped.

View 4 Replies View Related

Using IS Number And Search Function

Jul 9, 2014

I am trying to workout a formula to look-up and categorize data from a bank statement, without having to complete manually monthly.

So far I have a formula that returns the amount spent, but would need an extra column and separate table for each category.

IF(OR(ISNUMBER(SEARCH(" "&$G$3:$G$11&" "," "&A2&" "))),B2,0) where

Instead of returning B2, I would like to return a value from the table in column H3:H11 that corresponds with G2:G11, as shown below.

ColumnG ColumnH
Aldi Food
woolworths Food
Coles Food
saint Nicholas School
Blooms Medicines, toiletries, hairdressing, personal items
vodafone telephone

So for example if Aldi is in the text in A2, then I would like to return food ( from a lookup table similar to above) into the column where my formulae is ( say F2).

View 9 Replies View Related

Programming A Search Function

Jul 30, 2008

how to type a name into one cell and have all the places in a particular range (a1:H12) that displays that name to highlight a color.

View 14 Replies View Related

Search Function In Excel

May 20, 2014

How I could create a search function with this document attached. I want to be able to pull the kids name, location & information over to a search engine when looking for them because I will have over 40 different worksheets with numerous names on each of them.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved