I have been trying to work this out all morning...

I am using Vlookup,

=VLOOKUP('Google Traffic UK'!$C5017,'Google Traffic UK'!$C$7:$Q$3107,2,FALSE)

Sometimes the value is false & displays N/A.

I would like to replace the N/A with 0 but can't seem to get it working.

Any help appreciated.

Thanks

I am using Vlookup,

=VLOOKUP('Google Traffic UK'!$C5017,'Google Traffic UK'!$C$7:$Q$3107,2,FALSE)

Sometimes the value is false & displays N/A.

I would like to replace the N/A with 0 but can't seem to get it working.

Any help appreciated.

Thanks

- Replacing #N/A values with zero values
- Find and Replace VBA overlooking certain values
- Replace VLOOKUP #N/A error with blank or zero
- Change #n/a value to 0 on iferror & indexing formula
- Change #n/a value to 0 when using INDEX and MATCH fonction
- Have Vlookup return a Value of 0 instead of #N/A
- Have Vlookup return a Value of 0 instead of #N/A
- Hopefully a simple Macro - Replacing #N/A
- Change "N/A" Cell Values to 0
- Correcting a value error in VLookup
- Vlookup Returning #N/A!
- Excluding #N/A Values in Tables
- Edit Macro to replace formula #N/A
- TREND function won't accept #N/A values, workaround?
- Vlookup returning a #N/A value
- Replace number from a range with single value
- Vlookup returning a #N/A value
- Conditional Formatting - Replace #N/A with text
- Replace #n/a error with "*"
- Excel returns N/A values through vlookup
- Display a 0 in place of a #N/A value
- N/A value on lookups
- Improve code to delete cells with a value of 0
- Remove #N/A in vlookup result

I have a cell that is looking up values in another sheet using HLOOKUP....

when it doesn't find a value it returns a #N/A value....

I have tried using an IF statement with an imbedded ISNA or ISERROR or

ERROR.TYPE function to have it return a zero in the event of #N/A and the

actual value if it is able to find what it is looking up....

I need to convert the #N/As to zeros so I can use the sum function by merely

highlighting a range with my mouse...

Thanks.

Within the VBA code I have written I am trying to do a Find "00/01/1900" and Replace "ONGOING" but it seems to be overlooking the request without returning any error messages. I need to change "00/01/1900" so I dont get large negative values in columns C and D.

I have other find and replace requests and they work fine.

Any help on this matter would be greatly appreciated.

VB:Sub MainMacro2() ' ' MainMacro2 Macro ' This one does everything! ' 'This looksup Property reference and brings back data in relevant cell Range("S3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Sheltered!C[-18],1,0)" Range("T3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],'Stocklist at 010410'!C[-19],1,0)" Range("U3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],'Minor Works'!C[-20],1,0)" Range("V3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],'Minor Works'!C[-21]:C[-17],5,0)" Range("W3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],'Minor Works'!C[-22]:C[1],24,0)" Range("X3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-18],'Major Works'!C[-23],1,0)" Range("Y3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP('Major Works'!C[-24]:C[-20],5,0)" Range("Y3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-19],'Major Works'!C[-24]:C[-20],5,0)" Range("Z3").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-20],'Major Works'!C[-25]:C[-2],24,0)" 'Copy and Paste Special Values on Selection Range("S3:Z3").Select Selection.AutoFill Destination:=Range("S3:Z150"), Type:=xlFillDefault Range("S3:Z150").Select ActiveWindow.SmallScroll Down:=-126 Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False 'Find and Replace all 00/01/1900 dates with ONGOING Range("U3:Z150").Select [COLOR=red]Selection.Replace What:="00/01/1900*", Replacement:="ONGOING", LookAt:= _[/COLOR] [COLOR=red] xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _[/COLOR] [COLOR=red] ReplaceFormat:=False[/COLOR] 'Add's formulas to Columns B,C,D to calculate Void Duration Range("B3").Select ActiveCell.FormulaR1C1 = "=R1C6-RC[6]" Range("C3").Select ActiveCell.FormulaR1C1 = _ "=IF(AND(RC[3]=RC[18],RC[20]=""ONGOING""),R1C6-RC[19],RC[20]-RC[19])" Range("D3").Select ActiveCell.FormulaR1C1 = _ "=IF(AND(RC[2]=RC[20],RC[22]=""ONGOING""),R1C6-RC[21],RC[22]-RC[21])" 'Sets conditions for Columns B,C,D Range("B3").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(B3>28,B3)" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(AND(B3=14),B3)" Selection.FormatConditions(2).Interior.ColorIndex = 44 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(AND(B3>=0,B3=0,C314,C3)" Selection.FormatConditions(3).Interior.ColorIndex = 3 Range("D3").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(AND(D3=21),D3)" Selection.FormatConditions(1).Interior.ColorIndex = 44 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=IF(AND(D3>=0,D328,D3)" Selection.FormatConditions(3).Interior.ColorIndex = 3 Range("B3:D3").Select Selection.AutoFill Destination:=Range("B3:D97"), Type:=xlFillDefault 'Copy and Paste Special Values in Columns B,C,D - CHANGE RANGE to match number of rows containing data Range("B3:D97").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False 'Replaces all #N/A values with "" Columns("B:Z").Select Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("A1").Select End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

WilliamO

thanks to Donkeyote I fixed one problem but I forgot to ask about getting rid of #N/A on the same formula.

My formula belows works, but if nothing is there to be found it returns #N/A.

How do I add onto my formula to make it return a value of 0 if it can't locate anything?

=IFERROR(INDEX(ST,MATCH($H665,STT,0),5),INDEX(ST,MATCH($I665,STT,0),5))

Thanks in Advance.

I have been trying to figure out how to get rid of this evil #n/a value, in my formula,

Here is the formula I am using : =INDEX(Automate_Pricing!C4:G13,MATCH(PLAN.ECO!R10,Automate_Pricing!B4:B13,0),MATCH(PLAN.ECO!V10,Auto mate_Pricing!C3:G3,0))

to be a little bit more specific

R10 is a model

V10 is a code number going from 1 to 5

So let's say I have 3 models :

Model A

Model B

Model C

And also 3 different codes :

Code 1

Code 2

Code 3

This formula is use to determine the price of a model depending on it's code .

Model A - Code 1 = 10$

Model A - Code 2 = 20$

Etc...

This formula is used for a rental project , my problem is that we offer several different models although only part of them are up for rental,

so when you choose a model that is available on our inventory , but not available for rent , I get the error #n/a and then it won't let me SUM up .

Can someone help me !

Thank you very much

problem is that if a value of #N/A is returned (when it can't find what I'm

looking up), all calculations attached to that cell give me #N/A's as well.

I want it to return a zero if it would normally return an #N/A. I can

usually achieve this with a SUMIF(A1,">0") in another cell that points to my

vlookup, however because of the math in this specific circumstance, my

calculations need to be able to point directly to the vlookup, and not the

sumif. How can I get Vlookup to report a zero instead of an #N/A. I've

failed with different IF attempts, and it won't let me successfully place the

vlookup function inside of the SUMIF.

Thanks for your help!

problem is that if a value of #N/A is returned (when it can't find what I'm

looking up), all calculations attached to that cell give me #N/A's as well.

I want it to return a zero if it would normally return an #N/A. I can

usually achieve this with a SUMIF(A1,">0") in another cell that points to my

vlookup, however because of the math in this specific circumstance, my

calculations need to be able to point directly to the vlookup, and not the

sumif. How can I get Vlookup to report a zero instead of an #N/A. I've

failed with different IF attempts, and it won't let me successfully place the

vlookup function inside of the SUMIF.

Thanks for your help!

I usually then Copy and Paste Special - Values and then select the range. I would like to put together a macro that then runs at this point after I have done the prior steps.

Is there an easy way to have some sort of prompt appear that asks "Which Value would you like to replace the #N/A with?" I would then enter the value and then the macro would find and replace the #N/A's with the entered value I have selected?

(Hope this is clear) Using Excel 2000

'Replace N/A's with 0 Range("H:H").Copy Range("H:H").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("H2").Select For Each x In Range("R:C") R = Selection.Row C = Selection.Column If Cells(R, 1) <> " " Then Cells(R, C).Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Selection.Offset(1, 0).Select End If Next xIf anyone can help me out I would appreciate it greatly.Thank you in advance.

Problem I have is when the look up encounters a cell without a value in it I end up with a #n/a error code (example if the vlookup is looking for BV and it is not found the table displays the #n/a error code). when consolidating the data this error prevents sheet from caluclating the totals.

I need to replace the #n/a code with a 0 and not finding a way to do that with a vlookup function in place.

Any suggestions?

I am dealing with an Excel workbook which was created 3-4 years ago (on an older version of Excel) and also a new Excel workbook, which is created on Office version 2003.

I have noticed a discrepancy with the return value of the vlookup function which I would need further advice on.

In the older version of excel, wherever a "lookup_value" takes the value of a "-" (minus sign), vlookup returns a value of 0.

However, in this new version of Excel, where I am trying to do the same, by putting "-" into cells, vlookup returns a "#N/A".

Is there anyway to let vlookup to do the same as before (recognizing "-" as being an empty cell and returns a 0 )

Thank you

Han

The below table is a sample. I have about a zillion rows. I want to update the "orig price" column with numbers from the "new price" column. But i dont Excel to pick up the #N/As in the "new price" column. If there's a #N/A then i want Excel to keep the existing value that is in the "orig price" column.

So for product 1 the $1.613 will be replaced by the $1.67 this is fine. But say in product 3 i want to keep the $38.65 price as there is a #N/A in the "new price" column.

Hope this makes sense.

Item Orig. Price New Price

Product 1 1.613 1.67

Product 2 0.4096 0.41

Product 3 38.65 #N/A

Product 4 9.95 #N/A

Product 5 0.7711 0.82

Thanks for any help

Here is the For part of the code that cycles through all the cells that will contain the formula.

Code:

For Each cell In Rng R = cell.Row Range("Line") = Cells(R, 1) With query On Error Resume Next .Range("A1").QueryTable.Refresh BackgroundQuery:=False End With With main Cells(R, 3).FormulaR1C1 = "=VLOOKUP(RC2&"""",Query!C2:C3,2,FALSE)" Cells(R, 3).Value = Cells(R, 3).Value End With Next cell

Jan 06 100

Feb 06 101

Mar 06 104

Apr 06 99

May 06 106

Jun 06 95

Jul 06 99

Aug 06 101

Sep 06 106

Oct 06 104

Nov 06 89

Dec 06 99

Jan 07 67

Feb 07 105

Mar 07 110

Apr 07 123

May 07 99

Jun 07 #N/A

Jul 07 #N/A

Aug 07 #N/A

Sep 07 #N/A

Oct 07 #N/A

Nov 07 #N/A

Dec 07 #N/A

I wish to show 2006 as one data series on my graph, and 2007 (Year to date) overlaid as a second data series on the graph. Because of this, I don't think there is any way to add the trendline for 2006 and 2007 combined directly to the graph.

I tried the trend function, but the problem pops up that it does NOT ignore the #N/A values in the calculation, it gives the #VALUE error instead. If I replace #N/A with zero's, etc. then the trend function will factor the 0 values into the trend.

This is a monthly report with a LOT of graphs like this, so I don't really want to re-do the range for the trend function every month, I'd like it to be done automatically. I think I can figure out how to do this using the IF function, but it'd involve doing a seperate table for every single month, which again would be a major pain.

Anyone have any idea how to get the TREND function to accept (but ignore when calculating) #N/A values, or any decent workaround? Thanks for the help!

I have an inventory of some 4,000 items

The data I'm looking for is in the target worksheet, but vlookup returns

#N/A for some of the results.

I'm wondering if it has to do with the format of item numbers (which are a

combination of numbers & letters).

Would it make a difference if some was number & some text??

anybody know if I can replace numbers within a range with one value i.e. all

numbers between 3.1 and 3.5 to become 3B all numbers between 2.0 and 2.5 to

become 2B and numbers between 4.0 and 4.5 to become 4B, the list would then

become 3B, 3B, 2B, 4B. Is it possible to do this without going to individual

cells, similar to the find and replace function?

I have an inventory of some 4,000 items

The data I'm looking for is in the target worksheet, but vlookup returns

#N/A for some of the results.

I'm wondering if it has to do with the format of item numbers (which are a

combination of numbers & letters).

Would it make a difference if some was number & some text??

Having some trouble using conditional formatting.

I have a workbook of about 4000 rows of data.

I am using lookups to pull relevant data onto one sheet to display it in an easier to use manor.

Some of the values are coming up as #N/A, but this was expected.

I am basically trying to use conditional formatting, or something of that kind to replace the "#N/A" error with the word "Solid".

Any help would be greatly appreciated.

Cheers,

Nick

I have a large worksheet with thousands of rows, columns and formulaes.

A lot of the cells have an #N/A error in them. It would take too long to rewrite the formulae with isna functions in them so I was wondering if there is a quicker way to replace every #N/A with a "*".

I'm sure someone has posted this before as a piece of VBA code but I can't find it anywhere.

Any help would be greatly appreciated.

Regards,

Dixon

i have linked a few pages through vlookup but sometimes when i open my working s/sheet it brings n/a values instead of actual data. It happened to me once before i think i managed to re-activate the sheet by opening all relevant link sheets, but this is a timely exersice, can some one explain me what i am doing wrong? as now it doesnt seem to be working (re-opening linked sheets i mean)

thank you very much

At C11 my calculation is counting C1 till C10, but this is not working because C1 is displaying the #N/A value. I need a formula in C1 where this cell is display a 0 in place of a #N/A value.

for example:

0

0

0

0

5

0

0

3

0

0

0

1

would be

5

3

1

I can not delete the entire row, because the next column may not have a 0 value in the same row..

Obviously, the process I have below takes FOREVER. Is there a faster way to do this? Again, it is Column AN through Column BG and rows 6-2884 . I am going through it backwards so that I do not miss any 0's.

Dim ANcell As Range Dim ANCount As Long For ANCount = 2884 To 6 Step -1 'Start from bottom of range, so we don't skip any rows When we've deleted one If Trim(Range("AN" & ANCount).Value) = "0" Then Range("AN" & ANCount).Delete Shift:=xlUp Else End If Next ANCount

reference number is not available I would like to replace the #N/A returns

with the wording "No Response". Is this possible?

Thanks

No luck finding an answer? You could always try Google.