PDA

View Full Version : Solved: VBA Index/Match Help



praetorian
08-25-2011, 02:23 PM
This is my code, it works fine. The only problem is when I get a #N/A. How can I return a blank instead of #N/A?

Public Sub IndexMatch()
Dim lastrow
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("Master").Range("c2:c" & lastrow)
.Formula = "=INDEX(Daily!$A$2:$P$65536,MATCH(A2,Daily!$A$2:$A$65536,),2)"
.Value = .Value
End With

End Sub

Thank you.

Bob Phillips
08-25-2011, 04:01 PM
Public Sub IndexMatch()
Dim lastrow
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("Master").Range("c2:c" & lastrow)
.Formula = "=IF(ISNA(MATCH(A2,Daily!$A$2:$A$65536,)),"",INDEX(Daily!$A$2:$P$65536,MATCH(A2,Daily!$A$2:$A$65536,),2))"
.Value = .Value
End With
End Sub

praetorian
08-26-2011, 09:12 AM
Public Sub IndexMatch()
Dim lastrow
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("Master").Range("c2:c" & lastrow)
.Formula = "=IF(ISNA(MATCH(A2,Daily!$A$2:$A$65536,)),"",INDEX(Daily!$A$2:$P$65536,MATCH(A2,Daily!$A$2:$A$65536,),2))"
.Value = .Value
End With
End Sub

I tried that one and a 1004 error comes up with that formula...

Bob Phillips
08-26-2011, 09:55 AM
Oops, my bad.



Public Sub IndexMatch()
Dim lastrow
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("Master").Range("c2:c" & lastrow)
.Formula = "=IF(ISNA(MATCH(A2,Daily!$A$2:$A$65536,)),"""",INDEX(Daily!$A$2:$P$65536,MATCH(A2,Daily!$A$2:$A$65536,),2))"
.Value = .Value
End With
End Sub

praetorian
08-26-2011, 10:27 AM
Works. Thanks. It took two tries because for some reason excel kept adding an extra " at the end. Thank you.