VLOOKUP remove N/A help

Armster

Board Regular
Joined
Jul 26, 2007
Messages
63
Hello,

I've got a script that imports some cost codes from a central list on the network. if there are no matching costcodes in this list it returns a n/a I need to remove this from happening. I've been trying the ISNA functions but can't seem to get it working. Can you have a look and point out whats wrong? VB script editor keeps saying debug problem eexception missing....

The original:

[C5].Formula = "=VLOOKUP(RC[-2],'\\networkpath\[Costcode Table.xls]Costcodes'!R8C2:R500C3,2,FALSE)"
If IsEmpty(ActiveCell) Then Exit Sub


With the ISNA()

[C5].Activate
[C5].Formula = "=IF(ISNA(VLOOKUP(RC[-2],'\\networkpath\[Costcode Table.xls]Costcodes'!R8C2:R500C3,2,FALSE)),"not allowed",VLOOKUP(RC[-2],'\\networkpath\[Costcode Table.xls]Costcodes'!R8C2:R500C3,2,FALSE))"
If IsEmpty(ActiveCell) Then Exit Sub




Thanks in Advance

Dave
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
You don’t appear to have addressed what the actual problem is, however, I notice that you are testing the cell into which you have just entered the formula with “IsEmpty”. This will never return True. IsEmpty will return false if there is anything at all in the cell (other than an error I think) even if it is a formula returning a blank result. Don’t know if that’s your problem, but I thought I’d point it out.
 

Armster

Board Regular
Joined
Jul 26, 2007
Messages
63
The problem is the new script doesn't run it just goes into a debuggin mode.

when the orginial script is run with relevant data it produces the cost code and fills down the relevant information where something occurs. when there is no relevant costcode to return it produces a n/a and freezes up excel until its filled all 56665 cells in the column or whatever the maximum is :)
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Ah, just noticed what it might be. You need to put double quotes around “not allowed”:
Code:
[C5].Formula = "=IF(ISNA(VLOOKUP(RC[-2],'\\networkpath\[Costcode Table.xls]Costcodes'!R8C2:R500C3,2,FALSE)),""not allowed"",VLOOKUP(RC[-2],'\\networkpath\[Costcode Table.xls]Costcodes'!R8C2:R500C3,2,FALSE))"

Otherwise it thinks that the string has ended.
 

Armster

Board Regular
Joined
Jul 26, 2007
Messages
63
thanks that seems to be running now, even though its still taking ages to resolve and fill the squares, but at least now I can play with it and maybe sort it with some IF statement to end the loops on a certain phrase ? :) thanks again
 

Armster

Board Regular
Joined
Jul 26, 2007
Messages
63
Ok this is what I've got so far.....


Code:
[C5].Activate
[C5].Formula = "=IF(ISNA(VLOOKUP(RC[-2],'\\serverpath\[Costcode Table.xls]Costcodes'!R8C2:R500C3,2,FALSE)),""Fail"",VLOOKUP(RC[-2],'\\serverpath\[Costcode Table.xls]Costcodes'!R8C2:R500C3,2,FALSE))"
If IsEmpty(ActiveCell) Then Exit Sub
 'fill down column "M"
Range(ActiveCell, ActiveCell.Offset(0, -2).End(xlDown).Offset(0, 2)).FillDown
    'Selection.AutoFill Destination:=Range("C1:C121"), Type:=xlFillDefault
   ' Range("C1:C121").Select
    Range("D2").Select

' conditional format

    .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
     Columns("C:C").EntireColumn.AutoFit
     
     Range("A5:A200").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="900000"
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="750970", Formula2:="750990"
    Selection.FormatConditions(2).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="790100", Formula2:="910000"
    Selection.FormatConditions(3).Interior.ColorIndex = 3
    Range("A2").Select
     
End With

 Unload UserForm1

End Sub

The code runs and instead of putting the originial N/A it now puts Fail. This still fills down like the N/A just replacing it with Fail. I can't see how to make it break the loop if the costcode isn't recognised. I've tried replacing the "Fail" with just "" and this makes it apear empty but clicking on the relevant cells after ther 10 minutes+ of waiting for it to complete, you can see its got the whole formula in the cell.

When it recognises a batch of unmatched codes , its lists these (5?) and then stops the loop and goes into the next point in the script....


Any points would be appreciated
 

Forum statistics

Threads
1,181,055
Messages
5,927,858
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top