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)
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,"""")
The following formula:
=IF(ISNUMBER(F35)="TRUE","",IF(ISNUMBER(E35)="TRUE","X",IF(ISNUMBER(D35)="TRUE","XX","XXX")))
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...).
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"
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:
=SUMPRODUCT((A1:A100=D1)*(B1:B100=D2)*(C1:C27))
I get the #NAME? I believe because some of the values are N/A instead of numbers. So can I say something like:
=SUMPRODUCT((A1:A100=D1)*(B1:B100=HD2)*(ISNUMBER(C1:C100)*(C1:C2100)))
(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?
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"
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")
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
'Else
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
Do
' If values have been entered into the inputbox and
' the userinput is a number
If Len(f) > 0 And WorksheetFunction.IsNumber(f) Then
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 View Relatedformula 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...
=isNumber(A1:A1000)
if like on A7 it had "525a" instead of "525".
I have a problem with isnumber giving me two different results. One time it will evaluate the following as true:
ISNUMBER(0)
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.
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?
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 RelatedI'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.
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.
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.
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...
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:
=IF(ISNUMBER(SEARCH(""BrandB"",RC[6])),""B"",""C"")
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.
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â€
Example:
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
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 RelatedI 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
=IF(ISBLANK($B10),$A10,$B10)
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.
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
View 5 Replies View RelatedI'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies View RelatedI’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
I have data in a pivottable which I want to compare with another table. The lastcolumn+1 of the pivottable needs to get data from another table. I have this for an example:
View 5 Replies View RelatedI have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.
This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.
I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:
I am receiving a run-time error with following code. The error message is "unable to get the VLookup property of the WorksheetFunction class". I only receive the message when the lookup value is not found in the table.
I thought adding the "False" command at the end would return an "N/A" but it didn't. Is there anything I can add to avoid this error?