Help with Indexing in VBA

[shiva_reshs]

Hello,

I am not able to figure out, what is wrong in this Indexing code.

<code>Range("R6").Select ActiveCell.FormulaR1C1 = "=IF($Q6="","",INDEX('Worker-Exempt'!B:B,MATCH($Q6,'Worker-Exempt'!A:A,0)))"</code></pre>

I get application 1004 error msg.

Cross Posting

http://www.excelforum.com/excel-programming-vba-macros/960412-vlookup-to-index-matching-in-vba.html

 

[Rick Rothstein]

Need your help again..

Your code helps to change cell value to "-" for every #n/a, but is it possible to highlight the cell to color which do not have #n/a ??

Hard to know exactly what you want given there are two different code responses to your two questions. I'll assume the original (corrected) response by me is the one you want modified. Give this a try...

With Range("N6:N" & lr1)
    .Formula = "=IF($L6="""","""",INDEX('US Exempt Worker- US Exempt Wor'!C:C,MATCH($L6,'US Exempt Worker- US Exempt Wor'!B:B,0)))"
    .Value = .Value
    .Interior.ColorIndex = 5
    On Error Resume Next
    With .SpecialCells(xlConstants, xlErrors)
      .Value = "-"
      .Interior.ColorIndex = xlColorIndexNone
    End With
    On Error GoTo 0
End With

 

[shiva_reshs]

Thanks Rick. This is what I wanted..

But I am not able to replicate your code on below VBA

With Range("S6:S" & Range("R" & Rows.Count).End(xlUp).Row)
   .FormulaR1C1 = "=IF(Round(RC[-1]-RC[-3],2)=0,""Perfect Match"",RC[-1]-RC[-3])"
   .Value = .Value
   .FormatConditions.Delete
   .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=""Perfect Match"""
End With

Above VBA simply does minus formula, and when there is return value of "Zero" it display a cell value as "Perfect Match"

I am trying to replicate your code, so whatever it is not perfect match I need a color highlighted in those cell.

Thanks.

 

[shiva_reshs]

Hey Rick,

Is it not possible on this function of VBA?

Thanks