Application defined Error - Excel formula using VBA

[GirishDhruva]

Hi Everyone,

I am trying to get the values based on some conditions.

i tried manually with the below formula it worked but when i try with VBA its throwing me error.

Sub sample_check()

Set InputsWS = Sheets("Consolidated")
Basic = Split(InputsWS.Cells.Find(What:="Basic Salary", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Cells.Address(1, 0), "$")(0)
Basic_Arrear = Split(InputsWS.Cells.Find(What:="Basic Salary_Arrear", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
Basic_Rev = Split(InputsWS.Cells.Find(What:="Basic_Reversal", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
SDA = Split(InputsWS.Cells.Find(What:="Special Allowance", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
SDA_Arrear = Split(InputsWS.Cells.Find(What:="Special Allow_Arrear", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
SDA_Rev = Split(InputsWS.Cells.Find(What:="Special Allow_Reversal", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
NR_G = Split(InputsWS.Cells.Find(What:="NJ/Foreign", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)


Set Fnd1 = InputsWS.Rows(1).Find("Basic Salary", , xlValues, xlWhole)
If Not Fnd1 Is Nothing Then
    nxt = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Column
    Hdr = Range(Cells(1, 1), Cells(1, nxt - 1)).Address(, , xlR1C1)
    
    With InputsWS
        .Range(Cells(2, nxt), Cells(Rows.Count, nxt - 1).End(xlUp).Offset(, 1)).Formula = "=IF(NJ_G & ""2"" = ""N"",IF((Basic & ""2""  + Basic_Arrear & ""2"")<=0),0,IF(((Basic & ""2""  + Basic_Arrear & ""2""  + Basic_Rev & ""2"")<15000),IF((Basic & ""2""  + Basic_Arrear & ""2""  + Basic_Rev & ""2""  + SDA & ""2""  + SDA_Arrear & ""2""  + SDA_Rev & ""2""),15000),(Basic & ""2""  + Basic_Arrear & ""2""  + Basic_Rev & ""2"")))"
    End With
    
End If
End Sub

Kindly let me know where i am going wrong.

 

[offthelip]

The easy way to find an error when you are writing formula with VBA is to omit the equals sign so that you just write text to the cell. Then go to the cell manually add the equals sign back in , and excel will highlight where the error is

 

[GirishDhruva]

The easy way to find an error when you are writing formula with VBA is to omit the equals sign so that you just write text to the cell. Then go to the cell manually add the equals sign back in , and excel will highlight where the error is

Yes @offthelip that's a great idea but here in the code i am using variables to store in which column the headers are present.

but that cannot be done manually i think.

 

[offthelip]

that shouldn't make any difference if your error is because you have an error in the equation you have written to the cell. I suggest trying it to see if it helps

 

[GirishDhruva]

that shouldn't make any difference if your error is because you have an error in the equation you have written to the cell. I suggest trying it to see if it helps

Yes it worked and made me to get new thing for today.

Thanks for your suggestion and valuable time