VLOOKUP remove N/A help

[Armster]

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

 

[Lewiy]

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]

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]

Ah, just noticed what it might be. You need to put double quotes around “not allowed”:

[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]

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]

Ok this is what I've got so far.....

[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