Stuck on simple code-WHY ISN'T IT WORKING?:(

[Snorky85]

Hey

Wondering if anyone can tellme why this isnt working? I'm trying to update cell"BT14" within a worksheet called"Master Costing"with a lookup formula. The code appears to run but when I open the file, there is nothing in the cell. V annoying:

Sub FormulaCorrection()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
Dim ws As Worksheet
Dim wsUpload As Worksheet
Dim wsComment As Worksheet
Dim wsTemp As Worksheet
Dim wb As Workbook
Dim wbTemp As Workbook
Dim fRange As Range
Set ws = Sheets("Master Costing")
Set wb = ActiveWorkbook
Application.ScreenUpdating = False
ws.Select
   'Insert Lookup
    ws.Range("BT14").Formula = "=vlookup(L14,IF(ISNA(VLOOKUP(L14,'K:\Finance\ManAccts\Business Planning\2013-14\01 Planning Models\03 Pay Modelling\Templates\[Incremental Drift Workings.xlsx]Organisation Detail'!$AF:$AG,2,FALSE)),0,VLOOKUP(L14,'K:\Finance\ManAccts\Business Planning\2013-14\01 Planning Models\03 Pay Modelling\Templates\[Incremental Drift Workings.xlsx]Organisation Detail'!$AF:$AG,2,FALSE))"
    wbTemp.Close False
        
Application.ScreenUpdating = True
End Sub

:banghead: Any brilliant person out there able to tell me why is isn't working?

Much appreciated!

 

[Isskint]

You are missing the column argument of your opening Vlookup(). Simplified your formual reads;

Vlookup(L14,If(ISNA(External Vlookup()),0,External Vlookup()),?,?)

Simple way to check it would be to paste the actual formula directly and see what the formula checker says. There may also be issues with brackets in there.

 

[Snorky85]

Ah just realised i typed formula wrong. It should start =if(isna... However that shouldnt affect it-i go back into the spreadsheet fter the code has run and there is no formula at all in the cell-its an empty cell?!

 

[Isskint]

So the formula should read;

If(ISNA(External Vlookup()),0,External Vlookup())

=IF(ISNA(VLOOKUP(L14,'K:\Finance\ManAccts\Business Planning\2013-14\01 Planning Models\03 Pay Modelling\Templates\[Incremental Drift Workings.xlsx]Organisation Detail'!$AF:$AG,2,FALSE)),0,VLOOKUP(L14,'K:\Finance\ManAccts\Business Planning\2013-14\01 Planning Models\03 Pay Modelling\Templates\[Incremental Drift Workings.xlsx]Organisation Detail'!$AF:$AG,2,FALSE))

This puts the formula in the cell for me. Just check the number of brackets etc AND check that DisplayAlerts is set to TRUE. If not you will not get an error message from the code and it will not put the formula in the cell.

 

[Snorky85]

Yes, thats the right formula. Ah-i thought it would still put the formula in, even if it was a 'dodgy' formula.

I will try your suggestion when i get to work tomorrow. Thanks very much!!