Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel



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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Using The =IF(ISNUMBER(SEARCH Function To Search Multiple Cells)
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 Replies!   View Related
Nesting Isnumber Search Formulas
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 Replies!   View Related
Nesting Isnumber Search Functions
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 Replies!   View Related
ISNUMBER Function ..
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 Replies!   View Related
Search Function (locate Data) Search All Sheets Within The Workbook
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 Replies!   View Related
VBA: Search Function: Which Shows An Input Box Where You Can Enter A Word To Search For
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 Replies!   View Related
Function Search Value
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 Replies!   View Related
Search Function In VB
Can you use the Search function in VB? If so what's the code? Do you have to call it first ( Call Search .....)

View Replies!   View Related
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 Replies!   View Related
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 Replies!   View Related
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 Replies!   View Related
Programming A Search Function
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 Replies!   View Related
If Function To Search A Cell For A Value
I would like to search a cell and look for a certain value in it, and if it is, for there to be an X placed in another cell. Column A has the values, I would like to put the function into column B, and the X to appear in column C. I will attach a spreadsheet if it is needed, for this particular function, I will say we are searching for PP in the cell

View Replies!   View Related
Function Search The Specific Record
im trying to do is create a database of autocad drawings based on style of house number of bedrooms sq ft and if theres a garage. what i am wondering is how can i add a search function. for example i want to find a Cape with 2 bedrooms...i have all the information on the spreadsheet. what do i need to do to be able to search among the spread sheet without using the find function this is just the beginning of the data

View Replies!   View Related
Search Function Using Input Textbox
I have a search spreadsheet where I click a button, and an input box appears where I put a car reg in, and it searches a different sheet, returning results on the row the reg belongs in.

I've had a slight change in spec. I now need the same function to work, but instead of an input box.. I want the user to type the reg in a text box that is already on the sheet, and then click a search button to retrieve the results.

Is there anyway to easily amend the following code to get there?

View Replies!   View Related
Search Function Over Entire Workbook
I have a form that searches in an entire workbook for certain keywords, for example I fill in a last name in the textfield txtName and pus the search command button. As a result I get all the rows of the entire workbook containing this name. There are othre search fields in the form that do similar things...

This is the vba

View Replies!   View Related
Keyword Search Function Using SUMPRODUCT
I am trying to set up a keyword search where someone can type a word in D8 and the number of books with that word in its title will be returned.

I have managed to get the following sum to work if I put the exact title in the search however I would like it to be possible for a partial search.

=SUMPRODUCT(--('Books List'!G2:G1166=D8))

View Replies!   View Related
Find/Search Function Within A Sheet
I have a number of worksheets with data in various rows, all rows of data have UID's. I want to create a search function, which returns the location of the of the UID. Or better still the cursor will jump to the cell where the UID is located. It's very much like the way the 'Find & Replace' function works.

E.g. If I type the UID of 1234 into cell A1 I want to be able to press a 'Find' button (which will be located alongside cell A1) that will search the entire workbook for 1234.

View Replies!   View Related
Vlookup: Function That Will Search For The Value Of The Vehicle
I have a lookup table (see attached file) which holds the min & max value for a vehicle, the min & max size of the engine and the premium. I want a function that will search for the value of the vehicle (e.g. 100000 in cell H5) within the min value and max value columns as well as the engine size (e.g. 2000 in cell H6) in the min size & max size columns return the premium from the associated premium (which should be 13,200).

View Replies!   View Related
Vlookup And Isnumber
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 Replies!   View Related
Isnumber Query
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 Replies!   View Related
Can I Use Sumproduct And ISNUMBER Together
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 Replies!   View Related
IsNumber Is Not Working
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 Replies!   View Related
How To Use Cells.Find Function To Search Only In One Column
I want to find a content of ActiveCell only in column "C", beginning from cell C4.What must be in Look in, and Look at?How to accomplish to match whole number,ex. "1" in cell with "1",no to find "1" in "123"

This function searches in whole sheet,but i need only in C column!
Cells.Find(What:=ActiveCell, After:=[c4], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

View Replies!   View Related
Function To Search For 2 Sets Of Criteria As Opposed To Just 1
So here is my little dilema,

I have a worksheet that I need help with using SUMIF function.

Basically how do i use this function to search for 2 sets of criteria as opposed to just 1.

View Replies!   View Related
Create A Macro From This Simple Search Function
I am using excel 2007 for database of a large e-commerce site.
I am creating search-able attributes from keywords found in description, title and meta-data columns.

I am using the following function in "column X" to search 4 columns (see formula) for specific music styles.

View Replies!   View Related
Limit The Search Range In FIND() Function
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.

View Replies!   View Related
Isnumber Or Istext Formula
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 Replies!   View Related
ISNUMBER Giving Me Two Different Results
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 Replies!   View Related
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 Replies!   View Related
Adding Search Function To Delete Matching Records..
I'd like to do is click the delete button and when clicked, it will search for matching records in column A & B and if they match... I'm thinking the code for that is <> but I'm not sure, then delete that record, and shift the cells up. Do this until the search results are empty below the delete button. Like I said, it's probably more understandable to look at the workbook.

View Replies!   View Related
Search And Compare" Function For A Row That Contains Two Specific Values
I'm looking for a function (or combination of functions) that will essentially search for a row that contains two specific values (in two different cells), then grabs a value from another cell in the same row to use in a calculation.

I don't know... that probably doesn't make any sense, and maybe there's no way to get it done. If so, it would save me hours. Couldn't see a way to attch an example file to my post, otherwise I could probably describe it a bit clearer.

View Replies!   View Related
Average If Isnumber Multiple Values
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 Replies!   View Related
Conditionally Format IF, ISNUMBER, MATCH
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 Replies!   View Related
IF(ISNumber): Returning A Value For The Alpha-numberics That Begin With DMR
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 Replies!   View Related
Create A User Defined Function To Search A Column Of Data For A Part Number
I want to create a user defined function to search a column of data for a part number.

If it exists I want to have a the UDf returna "fail" otherwise "pass"

Here is the code I was trying to use

Function firstpass(SN As String) As String
ws = Worksheets("Defects")
c = ""
With ws.Range("a1:a9999")
Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole)
End With
If Not c Is Nothing Then
firstpass = "Pass"
firstpass = "Fail"
End If

End Function

This function only returns a "#value" and I don't quite know how to troubleshoot it.

View Replies!   View Related
I have 4 cols, x rows. I need Column C to check Column B for a numerical value, and if true, return the value in B, and if not true, then return value from A. Column B are functions formatted as general. The reference made by Sheet2!B is numbers formatted as text as to retain leading zeros. Here is what I have so far

View Replies!   View Related
Function To Search Column And Count Next Column
I made a quick little spreadsheet that explains what I am trying to accomplish. Basically I need to search a column for a known number, when it is found, I need to look at another column on that row, and if the cell is a specific item , count it.

View Replies!   View Related
Search Multiple Worksheets Against List Of Non-Exact Search Criteria?
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:

1. The rows containing the matched search criteria in the first sheet are highlighted.

2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.

I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.

View Replies!   View Related
Search Tool: Add A Search Tab
what i have is a spreadsheet with several sheets to it. i have many invoice numbers throughout the spreadsheet. what i would like to do is add a search tab or something so it will take me to the desired sheet that the user defined input "search" said. but i may have same invoice number on 2 or 3 different pages so i would need it to tell me that it is on 3 differents sheets

Example: I type in 658969 and it will automatically go to the sheet with this invoice number or return "No Entry Found" if it doesnt' have a match. or something to this affect and i don't know what do do if the input is found on multiple pages.

View Replies!   View Related
Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function
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 Replies!   View Related
Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.

Is someone aware of a function which will target a relative location instead of an exact cell?

View Replies!   View Related
Nested IF Function Error (entered Too Many Arguments For This Function)
I keep getting the "You've entered too many arguments for this function" error.

Here is the formula:

=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))

View Replies!   View Related
Force Function To Use Row Relative To Cell Housing Function
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).

My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.

My Script:

Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult

Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select

If RightPosition = True Then
Position = vResult
Position = "Position not valid"
End If

End Function

So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).

My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.

View Replies!   View Related
User Defined Function Function Tooltip
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?

View Replies!   View Related
2007 Right Function With Embeded Find Function
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:
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:

View Replies!   View Related
Concatif Function Breaks Beforedoubleclick Function Just By Being There?
I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.

The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more.
I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).

View Replies!   View Related
Show Insert Function Dialog Pre Set To Function
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)

View Replies!   View Related
AVERAGE Function Embedded Within An IF Function
TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.

0- Did not meet expectations
1- Below expectations
2- Met expectations
3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221

View Replies!   View Related
Copyright © 2005-08, All rights reserved