VBA Code to Get the Column Letter for the First non-Zero

[OilEconomist]

Thanks in advance for any suggestions for which I will provide feedback.

Why am I getting the error: "Run-time error '438': Object doesn't support this property or method" on the following line:

Cells(4, i).Formula "= MATCH(TRUE,INDEX(E" & i & ":" & LastColumnLtr & i & "<>0,),0)"

The following is the entire code where I am trying to obtain the column letter of the first non-zero or first non-blank value (cannot be zero).

Sub Non()


'Dimensioning
    Dim i As Long
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim LastColumnLtr As String
    

Worksheets("Sheet1").Activate 


'Find the last row      
        LastRow = Cells.Find(What:="*", After:=Range("A1"), _
        LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False).Row
      
'Find the last column & turn into letter
    'Find the last column
        LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
        
    'Turn into letter
        LastColumnLtr = Split(Cells(1, LastColumn).Address, "$")(1)




'Find the first non-zero or non-blank cells


    For i = 4 To LastRow
        
        
        Cells(4, i).Formula "= MATCH(TRUE,INDEX(E" & i & ":" & LastColumnLtr & i & "<>0,),0)"




    Next i


End Sub

 

[Fluff]

Try adding an equals sign after the word formula & before the "

 

[mumps]

Try:

Cells(4, i).Formula  = "= MATCH(TRUE,INDEX(E" & i & ":" & LastColumnLtr & i & "<>0,),0)"

 

[OilEconomist]

Thanks Fluff as that worked perfect. Now another question, where the LastRow is row 6, it only executes the first row. I did add a line after the formula for a message box to appear indicating the formula and it works, but nothing appears in cells (4, 5) or (4, 6). Just (4, 4).

 

[OilEconomist]

Thanks mumps as that worked perfect. Now another question, where the LastRow is row 6, it only executes the first row. I did add a line after the formula for a message box to appear indicating the formula and it works, but nothing appears in cells (4, 5) or (4, 6). Just (4, 4)

 

[Fluff]

Do you want the formula in rows4,5,6 of column D, or columns D,E,F on row 4?

 

[OilEconomist]

Thanks so much Fluff!

I wanted it column D and realized it should be Cells(i, 4) not Cells(4, i).

 

[Fluff]

Glad you figured it out & thanks for the feedback