My formula =IF(OR( FIND("Bugs",E2),FIND("Daffy",E2)),"/wkly",0). Always returns #VALUE! error. I can understand if neither value occurs in the string, what confounds me is if one of the two does occur in the string If I shorten to
=IF(FIND("Bugs",E2),"/wkly",0)
Then the expected output is returned
I have created a calculated column in PowerPivot and inserted a formula that worked in a normal excel spreadsheet however, this formula does not work in PP anymore.
=find("green",Table1[Name],1)
Name column:
NAME: Bag green
it should find "green" in the name and return the position. Hoever it alway returns the error, that the find function could not find the string.I have attached an example spreadsheet.
I have the following
Private Sub Workbook_Open()
Worksheets("Sheet1"). Range("L5").Value = Date
End Sub
When i try and run the code it returns an error saying: "Compile Error, Cant Find Project Or Library" and it highlights the word "Date"
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I have this code that only returns a #NAME? error. I'd love it if someone could take a look and let me know what you think might be going on. If it factors into it, D1:D4 each contain one of the following: USD, AUD, GBP, MX and E1:E4 contain the corresponding exchange rate. Also, J7:AJ41 contain a function that returns a number. Let me know if you need more information. I just can't figure why this is not working.
Sub newtabs()
Dim x As Range
Dim Curr As String
Dim Rate As Double
For Each x In Sheets("Cash Flow Detail - WkCount").Range("D1:D4")
Curr = x.Value
Rate = x.Offset(0, 1).Value
Sheets("Cash Flow Detail - WkCount").Select
Sheets("Cash Flow Detail - WkCount").Copy After:=Sheets(2)
Sheets("Cash Flow Detail - WkCount (2)").Name = Curr
Cells(7, 10).Select
ActiveCell.FormulaR1C1 = "=IF(activecell.offset(RC7)=" & Curr & ",('Cash Flow Detail - WkCount'!RC*" & Rate & "),0)"
Selection.Copy
ActiveCell.Range("a1:aa35").Select
ActiveSheet.Paste
Next
End Sub
I have a sheet that uses vlookup when the lookup returns #na error how can i conditional format these cells to so text is same as background
View 9 Replies View RelatedI have sumproduct formula that returns #N/A error and I'm not sure why
=SUMPRODUCT((YTD!$A$2:$A$2599=B$10)*(YTD!$P$2:$P$2599=$Q19)*(YTD!$E$2:$E$2599))
If I narrow the range from $939 to $1093 then the formula works.
But that is only evaluating 1 unit of 10
As I add worksheets to the workbook and update the unit numbers in B10, the entire range needs to be evaluated for matching criteria.
workbook is way too large to upload.
Is there a way in which you can create an if function or similar to give a reason for why the cell of data has an error.
Basically i have some data in a table, and when a #num! error pops up id like to in the cell next to it give a sentence stating why this has happened.
E.G.
Cell A1 Cell B1
#Num! Number error due to 'x' being too small
Not sure how i can get this to work, i have tried a basic if function but that hasnt worked.
I'm not sure what I'm doing wrong with this formula in Excel 2003. I figured out how to do it in Excel 2007 using the SUMIFS statement, but alas Excel 2003 won't take a SUMIFS statement.
I have data in a column, J of Worksheet B that I need to add based on 2 conditions. The first conditions are in cells AJ6 to 8 on Worksheet A. The second condition must be matched from Column A of Worksheet B to the same condition in Column F on Worksheet A.
if Statement A: =IF(M4>$B$17,0,LOOKUP(M4,$AA$20:$AB$29)*$B$15/12) works very well
if Statement B: =IF(M4>$B$17,0,LOOKUP(M4,$AA$36:$AB$44)*$B$15/12) works well too
now I am trying to say
=IF('Input Page'!B36="yes",(IF(M4>$B$17,0,LOOKUP(M4,$AA$36:$AB$44)*$B$15/12),(IF(M4>$B$17,0,LOOKUP(M4,$AA$20:$AB$29)*$B$15/12))))
Basically, if B36 is yes, use If Statement B, otherwise IF Statement A. If I write Yes, I get a "Value" error, if I change the yes to 1, I get a FALSE error.
I'm using the formula below to count the distinct number of values in the range, many values are repeated so this formula only counts each one once, it works sometimes. For some reason I get a #Div/0 error once in a while.
The entire range might not always have a value, when I get the error if I add text to the remaining cells of the range the error is cleared.
I do not want to use a If (iserr(myformula),0,myformula)) because there are still values that need to be counted even if the range is not totally populated.
=SUMPRODUCT((B16:B1500"")/COUNTIF(B16:B1500,B16:B1500&""))
I am using MS Query to join 3 worksheets together.
I need to calculate one field but it keeps returning an error.
the 2 fields are part of the same table calle "Trades"
The fields are : Quantity and USD Price
USD Price is calculated initially in the worksheet using the current exchange rate.
I can display each fields separately (no problem) but when I try to multply the 2: I get a syntax error.
Things i have tried:
I tried to multiply Quantity by 2 and it works. when I try to multiply USD Price by 2 I get the syntax error.
I also tried to copy the values (not the formula) into a new column called USD Price2 and:
- Quantity*USD Price2 : syntax error
- USD Price2*2: Syntax error
Private Sub Workbook_Open()
'Application. ScreenUpdating = False ' Turns off screen updating
Dim Filename As String
Dim nFam As Integer
Dim cRow As Integer
Dim i As Integer
ws As Worksheet
Filename = "O:BVRMusers_sharedJCIntraday ToolFamily List.xls"
Workbooks.Open Filename 'Opens Family List
'Count the number of rows (Families) in Filename (Family List)
Set ws = ActiveSheet
With ws
. Cells(1, 1).Select
cRow = .UsedRange.Rows.Count
End With
End Sub
i cannot get ws to be set as the ActiveSheet. i receive a run time error 91 each time and cannot figure out what is wrong. i've used this code before under subform functions, but this is the first time i've placed it in the "ThisWorkbook" area.
I'm trying to sum cells in a column that meet the following criteria: If the first character of the cell = "V" then sum the last two characters in the same cell. I.e., if A4 = "V12" and A6 = "V10", then I need the formula to return the total 22. All other cells, whether blank or starting with a different letter need to be ignored. My attempts have failed, including this last one that returns a #VALUE error.
=SUMPRODUCT(LEFT(A1:A10,1)="V")*(MID(A1:A10,2,2))
Having a problem with a simple udf to calculate values across multiple sheets using the calling cell as a reference point. It works great until you open another workbook and then switch back. At that point all cells calling this udf return a #value! error. If I type anywhere on the sheet all of the values will recalculate but this is less than ideal for the end users here. below is example of concept
Function TestMe()
Application.Volatile (True)
Dim strCallAddress As String
Dim intSubTotal As Integer
strCallAddress = Application.Caller.Address
intSubTotal = Worksheets(2).Range(strCallAddress).Value
intSubTotal = intSubTotal + Worksheets(3).Range(strCallAddress).Value
TestMe = intSubtotal
End Function
I have three different values in three different columns and I want to find the maximum amongst the 3 values.
A1 B1 C1
100 50 40
in D1 I have written a formula
=MAX(A1, B1, C1)
But it is returning an error saying formula you typed contains an error
I've managed to set up a gradebook.
However, is there a way that I can get Vlookup not to return N/A when I have a blank cell on the report (not in the data table). I want to be able to calculate the progressive grade average of a stduent as the year progrsses.
whats happening to my workbook. On a worksheet called Data, i'm pulling in a lot og data from a database. Then on another worksheet i'm looking for certain things on the data sheet and counting how many there are.
This all goes along find until i close the workbook, and then re-open.I click the automatically update button that appearsand once it finishs updating all my values are replaced with #Value.
This is the formula i'm using.
= SUMPRODUCT(--(Data!$X$1:$X$49871="4")+(Data!$X$1:$X$49871=4),--(Data!$M$1:$M$49873="FTS-Unix"))
I have a file created in Excel 2003 that uses a vlookup to reference another file, also created in Excel 2003. The function returns VALUE when the referenced file is closed. I get the VALUE error whether or not I update links upon opening the file. If I open the source file, the function calculates properly.
Here is the formula when the referenced file is open:
=VLOOKUP(A38,'[June Scorecard confirmation.xls]VPSC Summary'!$C$10:$F$22,4,FALSE)
The full & correct path appears in the formula when the referenced file is closed.
Columns C, D & E in the referenced range contain text, column F contains a Sum. This file is linked to another file, also using lookups. The linked area in this file works without problem, but it is a precedent to the calculation in column F.
Both these files reside on a network in subdirectories of the same logical drive. I am using a laptop that briefly had Excel 2007 installed on it, but was wiped clean before it was issued to me.
My .find function is only finding the 2nd value in a cell example If i look up the term " HOSE"
5/8 hose water Will return
5/8 heater hose will not
Basically what I am making is something I am calling a "what" button. It queries my database and returns all the part numbers with the term i put in and i thought it was working fine till i was testing it, did i do something to select this? I've tried xlpart and xl whole. Neither is working.
Here is the code below
Sub COPYPARTNUMBERINFO()Dim parttofind As String
Dim partcell As Range
Dim partref As Range
ActiveSheet.Unprotect Password:="*******"
Range("t6:T10000:u6:U10000").Select
Selection.ClearContents
parttofind = InputBox("What is the number or discription")
[code].....
This code (partial) was written to interrogate a database. Cell "A2" of another tab contains a Part Number to be sought.
Target = Range("A2")
Sheets("Kanban Data").Select
Dim Nrow As Range
Dim N As Integer
On Error Resume Next
With Sheets("Kanban Data")
Set Nrow = .Columns(2).Find(What:=Target, After:=.Cells(3, 2), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If the Find expression (as pulled down from the Edit menu) is empty, then inserting a Part Number in A2 and executing the macro finds the correct record and returns the its correct row number (N, as defined afterwards in the code). Then, changing the Part Number in cell A2 and executing a new search returns the SAME row as the previous search, despite Target being equal to the new sought after value.
I have 2 worksheets, one called Summary and the other called LNB. Column A in both worksheets contain account numbers.
I am trying to tell excell to look at the LNB worksheet and find the account number that is on the summary worksheet. If the account number is found, then tell me the row, on the Summary worksheet, that the account was found.
The function returns "Empty" even though I know the accounts do exist on both sheets and I dont know why.
Here is the code ....
I have a list of skills listed as codes in column A. I would like to write in a separate column, B, the name of the skill based on the fact that A contains a specific text. Only the first condition return a correct value, the others condition returns #VALUE! as if they are not satisfied.
View 5 Replies View RelatedFor some reason, it seems that this following line returns the value its supposed to be searching for instead of the range where it finds the value.
View 5 Replies View RelatedIs it possible to see why this code returns a date that is almost correct? Searching for 01.01.2005 I get 01.11.2005 as result. I would like the code to only show the correct date, and if it doesn't exist it should end up showing "nothing".
Code:
Set fstdate = wsOBX.Range("B1:B" & lr1).Find(what:=startdate, _
LookIn:=xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
I'm working with a document that has several cells which require double carriage returns within cells. The location of each space is marked with an @ sign. A few weeks ago, I found an article online that explained how to do a find replace where the find value was '@' and the replace value was a series of numbers/characters that created a carriage return. When I doubled the carriage return code I got my two spaces. If I remember correctly, the string that I entered still appeared in the cell, so I needed to clean up afterwards by replacing that string with ' ', but it still left the line break.
View 9 Replies View RelatedI am working with financial data and am exposed to a problem that excel formula cannot solve. I am very new to VBA and would like some assistance please. I have in one excel column the list of maturities in dates eg. EN02, EN03, EN04 etc (EN=January). I have in another column the corresponding prices for these maturities. What I would like to do is compute the returns on similar maturities and paste the returns in the returns column. I can use a formula for this when the consecutive maturities are the same. The problem arises when I want excel to find the previous similar maturity, which may be 5 cells or so before. How do I write a macro to find the similar maturity, compute the returns and place that returns value in the returns column.
View 10 Replies View RelatedFind statement returns empty when i search in a range with XLWhole option enabled
Code:
Private Sub worksheet_change(ByVal Tar As Range) On Error Resume Next
If Tar.Column = 1 Then
Set f = Sheets("Sheet2").Range(Cells(1, 1), Cells(5000, 100)).Find(Tar(1, 1), LookAt:=xlWhole)
[Code]..
but when i use Cells instead of Range ,Find command returns perfect result
Code:
Private Sub worksheet_change(ByVal Tar As Range) On Error Resume Next
If Tar.Column = 1 Then
Set f = Sheets("Sheet2").Cells.Find(Tar(1, 1), LookAt:=xlWhole)
If f.Column = 11 Then Sheets("Sheet1").Cells(Tar.Row, Tar.Column + 1).Value _
= Sheets("Sheet2").Cells(f.Row, f.Column + 1).Value
End Sub
I have written a Excel (2003) that searches a worksheet for a string in any cell. If the string is not found, it uses the 'On Error GoTo' command to jump to a given label. It works fine on the first string not found. When it searches for the next non-existent string, it fails with:
'Run-time error '91':
Object variable or With block variable not set'
Do I have to clear a buffer after each cells.find search?
My
The following code returns an error saying "expected an array" even though I define the array at the start of the code? Why is it doing this?
Sub copydays()
arrDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
sglArrPos = 0
For sglLoop = 1 To 5
Sheets(arrDays(0)).Select
Cells.Select
Selection.Copy
sglArrPos = sglArrPos + 1
Sheets(arrDays(sglArrPos)).Select
Cells.Select
ActiveSheet.Paste
Next sglLoop
End Sub
