VBA code to fill formula

[sdfchu]

Hi,
I am working on a macro where I want column A to drag and drop a formula when i run the macro, but when I run code below i get this error "Run-time '1004': Method 'Range' of object'_Global' failed. I am learning VBA as i go so i will really appreciate help with code below.

Range("A1").Select
Range("a2:a" & LastRowColumnA).Formula = "=IF(RC[16]=""not in Smart List"",""not in Smart List"",IF(ISNUMBER(SEARCH(""dead"",RC[14])),""Failed to scan"",IF(RC[7]=""NULL"",""NULL"",IF(ISNUMBER(SEARCH("" log into"",RC[13])),""OK"",IF(ISNUMBER(SEARCH(""was able to log"",RC[17])),""OK"",IF(ISNUMBER(SEARCH("" log into"",RC[10])),""OK"",IF(ISNUMBER(SEARCH("" checks : no"",RC[11])" & _
        "entication Failed"",""OK"")))))))"

Thank You,

 

[steve the fish]

Hi. First things first you cant use R1C1 references with .Formula, you have to use .FormulaR1C1 = etc etc. Try that.

 

[Fluff]

Hi & welcome to the board
Another thing, what is the value of "LastRowColumnA"?

 

[steve the fish]

Also some of the formula is chopped off. It was a bug in VBA for years and years with long formulas. Paste the formula that you have in A2.

 

[BarryL]

I think you are also missing a bracket in the below where i have highlighted a bracket in red


IF(ISNUMBER(SEARCH("" checks : no"",RC[11]))" & _

 

[steve the fish]

I think you are also missing a bracket in the below where i have highlighted a bracket in red


IF(ISNUMBER(SEARCH("" checks : no"",RC[11]))" & _

Thats the bug i spoke about barry. It chops at the line feed when recording a long formula. It doesnt do it with my 365 2016 version anymore.

 

[sdfchu]

Also some of the formula is chopped off. It was a bug in VBA for years and years with long formulas. Paste the formula that you have in A2.

formula in A2 is as below

=IF(Q1314="not in Smart List","not in Smart List",IF(ISNUMBER(SEARCH("dead",O1314)),"Failed to scan",IF(H1314="NULL","NULL",IF(ISNUMBER(SEARCH("log into",N1314)),"OK",IF(ISNUMBER(SEARCH("was able to log",R1314)),"OK",IF(ISNUMBER(SEARCH("log into",K1314)),"OK",IF(ISNUMBER(SEARCH("checks : no",L1314)),"Authentication Failed","OK")))))))

 

[sdfchu]

Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row

 

[sdfchu]

Hi. First things first you cant use R1C1 references with .Formula, you have to use .FormulaR1C1 = etc etc. Try that.

adding R1C1 did not make any difference.

 

[sdfchu]

I think you are also missing a bracket in the below where i have highlighted a bracket in red


IF(ISNUMBER(SEARCH("" checks : no"",RC[11]))" & _

adding extra bracket did not make any difference either.