I'm having trouble with an iserror statement in part of a procedure I've been writing. This macro uses vlookup often, and will occasionally have errors so I have been trying to use the iserror function to trap them. The code is as follows: ....
I am wondering if I have nested too many functions for this to work properly. In the case where one of the first two if statements are true (an error would be produced), I am receiving a "0" as specified. However, in the case where the statement VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE) results in an error then I should have the return of VLOOKUP(C75&" - "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE), but am getting a #REF! error instead.
I have 2 columns of data: Column A is basically postcodes, but just the 1st part and Column B is a #2. If somewhere else on the sheet I type in the postcode IE. SW19 it will show besides it in my lookup the number 2. However if I type in a postcode that is not listed in Column A, I'd like to to return the value 1. I cannot figure this out. My code is:
I'm trying to create a rec report, ideally on one page, for multiple criteria. The situation is we get a report on our trade positions (investment stuff) from an outside service we use. We also have a report internally from what we've booked. We're looking to tie out are basic things like price, original par, revised par, trade date, counter-party banks, etc. I've consolidated data from both sources on one page (DATA) where all data points are lined up (say EXTERNAL is from rows 1-500, and INTERNAL is 510-1010). Now I need to create a front page showing the result of each tie-out. The lookup value will be a combination of the fund name + trade ticket number which would almost guarantee a unique ID (fund name XYZ, ticket number 12345 = XYZ12345)
Current tabs on spreadsheet: DATA, EXTERNAL, INTERNAL. I need to add a REC page
Question: What formula can I use to check on each and return TRUE/FALSE? The goal is to have about 8 columns on the REC, each for one of the tie-outs (let's use price as an example.). I know the basic VLOOKUP to return one value, but it appears in this case I will need VLOOKUP to check two matching values based on unique ID XZY12345, compare the price, and return TRUE if they match, FALSE if they don't
Here's something I saw from one of our spreadsheets that I think does the same to confirm counter-party, though I have no idea how to read this lengthy formula.
This formula works perfectly except when worksheet 2 has a code that is not on worksheet 1, then I get the error #N/A. I understand why I get the error, but not how to fix it. Obviously some sort of nesting - but what and how?
What I want on worksheet 2, column I - if there is a code on worksheet 2 but not on worksheet 1 - a return of "N/A"...
DETERMINING ROW NUMBER FOR DATA FOUND WITH VLOOKUP IN VBA
I have a user form with a combobox (called STKCODE) that calls up a vlookup routine to search for this item in a named range on a worksheet and retrieve related data to display on the userform. All this works fine.
What I would like to know is how to easily determine the row number on the worksheet, representing the row the data is stored in, if thats at all possible....
I have an excel sheet with multiple columns. In one column I have X's and in another I have a limit. I need to look down the stuff column, find every X, match each X to its corresponding limit and then compare all the limits and return only the smallest limit found. I.e if I was looking down the stuff column I would find 4,6,8,8,3 and thus I would return 3 as my value. I am very new to working with excel formulas,
When I use VLOOKUP formula within a VBA routine ex: For LoopCounter = 1 to LastRow With Worksheets("0MATERIAL") .Cells(RowCounter, 5).FormulaR1C1 = "VLOOKUP(RC[-2],'0MAT_UNIT'!R1C1:R65520C12,4,FALSE)" End With RowCounter = RowCounter + 1 Next LoopCounter
How can I find out what row or cell address that the VLOOKUP found the data? The above code works perfectly but the problem is I need data from that same row in columns 4-11. I replicated the .Cells line above and made each one a VLOOKUP (for column 4, 5, 6, etc...) but this kills the performance. I have around 32,000 rows in sheet 0MATERIAL and around 50,000 rows within sheet 0MAT_UNIT
I am trying to Sum lines of info with "True or False" and "Yes and No". I would like to assign 1 to True and Yes and 0 to False and No when I total the rows. Never tried this in Excel, on Lotus and the formula does not work. I can find and replace, but I would like to be able to use a formula.
I currently use the following formula using IF and ISERROR i.e.
=IF(ISERROR(IF(O12-(O12/100*80)<S12,"",IF(O12="","","Warning 20% or less Cover Remains!")))," ",(IF(O12-(O12/100*80)<S12,"",IF(O12="","","Warning 20% or less Cover Remains!"))))
What I’m after is to add an addition condition e.g. using “IF(O12-(O12/100*80)<S12” and “IF(O12-(O12/100*90)<S12” which would display either Warning 20% or less Cover Remains! or Warning 10% or less Cover Remains! incorporating ISERROR to avoid #N/A
Anyone have a way to create a macro that will convert a formula that returns an error into and IF(ISERROR) formula?
I have inherited a financial model in which the programmer did not consider that hundreds of the formula results would be #DIV/0 unless there are entries in the source cells. I would like to create a custom macro that would convert the formula to an IF statement, so that if the formula in that cell is currently creating an error, return 0, if not run the formula.