excell formula

[dollygg]

this original code works fine for column A to CX:

Range("E8").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-7]C,[Sales.xls]SummaryReport!R8C1:R650C11,4,0)/1000"
Range("E8").Select
Selection.AutoFill Destination:=Range("E8:CX8"), Type:=xlFillDefault



However, once I change CX to CZ, the formula return #N/A for cell CY8 and CZ8. Other cells works fine. Don't know what's the reason causing it.

 

[jmthompson]

Try

Range("E8:CZ8").FormulaR1C1 = "=VLOOKUP(R[-7]C,[Sales.xls]SummaryReport!R8C1:R650C11,4,0)/1000"

This will populate the formula to look for the value in row 1 of the same column on your Worksheet "SummaryReport" in Column A and return the value in column D, into all cells between E8 and CZ8.

 

[dollygg]

didn't work. IT's weird. I already check the cell format for different fund numbers to see why some of them works.

 

[jmthompson]

Try this quick test

in a blank cell enter the formula

=CY8=pick the cell on your SummaryReport sheet that looks like a match

If you get False, something about those cells is different. Are you comparing numbers to numbers?

 

[dollygg]

The solution is found. The problem is not compaing number to number. I use ISNUMBER() and ISTEXT() to find out.

Thanks lot!