Need Macro to Input a Formula

[donnydorko]

In the included file i have what i start off with and what it ends up with,

basically i need a macro to insert the formula i have in Column M row 3, and insert it all the way down to the last letter that is contained in column k.

So in this example The Last Letter in Column K is AQ, ( i dont physically mean last i mean last in alphabetical order in excel , such as ( W X Y Z, AB, AC , AD ) AD would be last.


So since AQ is last it would insert the formula 43 times, which is AQ.

THe AFter tab should clear any confusion up because this is how it looks finished.

I use various sheets like this so the last letter changes, so having this macro be dynamic and not attached to a sheet name would be ideal.
Thanks ,

[rylo]

Hi

Try this

Sub aaa()
  holder = 1
  For Each ce In Range("K3:K" & Cells(Rows.Count, "K").End(xlUp).Row)
    If ce <> "" Then
      If Range(ce.Value & 1).Column > Cells(1, holder).Column Then holder = Range(ce.Value & 1).Column
    End If
  Next ce
  Range("M3").Resize(holder, 1).Formula = "=$G$1&$I$1 & SUBSTITUTE(ADDRESS(1, ROW() - 2, 4), ""1"", """")"
End Sub

rylo

[donnydorko]

works perfectly, thanks