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]

Maybe (untested)

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
End With
For i = 6 To lr1
    If Range("N" & i).Text = "#N/A" Then .Value = "-"
Next i

Peter,

I clicked into this thread by accident, but it looks like you need to put something in front of the dot for the part I highlighted in red.

 

[VoG]

Oops! Thanks Rick

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
End With
For i = 6 To lr1
    If Range("N" & i).Text = "#N/A" Then Range("N" & i).Value = "-"
Next i

 

[Rick Rothstein]

Maybe (untested)

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
End With
For i = 6 To lr1
    If Range("N" & i).Text = "#N/A" Then .Value = "-"
Next i

Peter,

I clicked into this thread by accident, but it looks like you need to put something in front of the dot for the part I highlighted in red.

Actually, in looking at what you posted, and what I think is being attempted, couldn't this work?

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
    .SpecialCells(xlConstants, xlErrors).Value = "-"
End With

 

[VoG]

Actually, in looking at what you posted, and what I think is being attempted, couldn't this work?

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
    .SpecialCells(xlConstants, xlErrors).Value = "-"
End With

Much better

 

[shiva_reshs]

Nicely done.

You guys are the best!!!

*Just for my learning. If I want to color the cell can it be done?

Thanks!!

 

[Rick Rothstein]

*Just for my learning. If I want to color the cell can it be done?

Which "cell" (singular?), the one that was #N/A and is now a dash? If so, try this...

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
    With .SpecialCells(xlConstants, xlErrors)
        .Value = "-"
        .Interior.ColorIndex = 6    'change the ColorIndex value as desired
    End With
End With

 

[shiva_reshs]

Thanks!! Works..

 

[shiva_reshs]

Hi Rick,

when no #N/A is found, i get run time error 1004 on below line.

[/COLOR][/B][B][COLOR=#b22222].SpecialCells(xlConstants, xlErrors).Value = "-"[/COLOR][/B][B][COLOR=#b22222]

 

[Rick Rothstein]

Hi Rick,

when no #N/A is found, i get run time error 1004 on below line.

[B][COLOR=#008000]On Error Resume Next[/COLOR][/B]
[B][COLOR=#b22222].SpecialCells(xlConstants, xlErrors).Value = "-"
[/COLOR][COLOR=#008000]On Error GoTo 0[/COLOR][COLOR=#b22222]
[/COLOR][/B]

Sorry, I forgot to cover that possibility. Add the lines around the SpecialCells statement that I show in green above and all will be well.

 

[shiva_reshs]

Hi Rick,

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 ??

Thanks