VBA to store Vlookup Function in a cell

[mrexcelhelp123]

Hi There!

I have a workbook where I need a cell to be populated in an array of worksheets I specfiy, with below function:

=IF(ISNA(VLOOKUP($C$3&" * ",'Y:\ttd\January 2010\[Jan_costs.xlsx]revdata'!$A$11:$AA$1239,5,0))=TRUE,"",VLOOKUP($C$3&" * ",'Y:\ttd\January 2010\[Jan_costs.xlsx]revdata'!$A$11:$AA$1239,5,0))"

This function performs a vlookup in another workbook.

This is what I thought I could do but I keep getting a data mismatch error. I'm a novice VBA user so I'm sure that I'm doing something obviously wrong. Thanks.


Sheets(Array("NAT", "MCHS")).Select

Range("C2").Select
ActiveCell.Formula = "=IF(ISNA(VLOOKUP($C$3&" * ",'Y:\ttd\January 2010\[Jan_costs.xlsx]revdata'!$A$11:$AA$1239,5,0))=TRUE,"",VLOOKUP($C$3&" * ",'Y:\ttd\January 2010\[Jan_projcosts.xlsx]revdata'!$A$11:$AA$1239,5,0))"

 

[VoG]

Try

Dim ws As Worksheet
For Each ws In Sheets(Array("NAT", "MCHS"))
    ws.Range("C2").Formula = "=IF(ISNA(VLOOKUP($C$3&"" * "",'Y:\ttd\January 2010\[Jan_costs.xlsx]revdata'!$A$11:$AA$1239,5,0))=TRUE,"""",VLOOKUP($C$3&"" * "",'Y:\ttd\January 2010\[Jan_projcosts.xlsx]revdata'!$A$11:$AA$1239,5,0))"
Next ws

 

[AlphaFrog]

Sheets(Array("NAT", "MCHS")).Select

Range("C2").Select
ActiveCell.Formula = "=IF(ISNA(VLOOKUP($C$3&" * ",'Y:\ttd\January 2010\[Jan_costs.xlsx]revdata'!$A$11:$AA$1239,5,0))=TRUE,"",VLOOKUP($C$3&" * ",'Y:\ttd\January 2010\[Jan_projcosts.xlsx]revdata'!$A$11:$AA$1239,5,0))"

I think the quotes within the formula are messing you up. To have a literal quote within a text string you have to use two quotes e.g.
$C$3&"" * "",'Y:

 

[mrexcelhelp123]

Thanks! Works great except is there anyway to bypass the pop up window that requests for file location of Jan_cost.xlsx? Right now, I can just hit Cancel twice but it would be great if I didn't have to. Otherwise, is the only solution to this is to have the file open?