Apply formula to Column AJ as long as column A is populated

[detriez]

I am trying to apply this formula to column AJ where column A is populated
My sheet has headers and this formula will result in some blank AJ cells. Column A is always populated

=IF(AK2<>"",CONCATENATE(($AK$1&": "),AK2)&CHAR(10),"")&IF(AL2<>"",CONCATENATE(($AL$1&": "),AL2)&CHAR(10),"")&IF(AM2<>"",CONCATENATE(($AM$1&": "),AM2)&CHAR(10),"")&IF(AN2<>"",CONCATENATE(($AN$1&": "),AN2)&CHAR(10),"")&IF(AO2<>"",CONCATENATE(($AO$1&": "),AO2)&CHAR(10),"")&IF(AP2<>"",CONCATENATE(($AP$1&": "),AP2)&CHAR(10),"")&IF(AQ2<>"",CONCATENATE(($AQ$1&": "),AQ2)&CHAR(10),"")&IF(AR2<>"",CONCATENATE(($AR$1&": "),AR2)&CHAR(10),"")&IF(AS2<>"",CONCATENATE(($AS$1&": "),AS2)&CHAR(10),"")&IF(AT2<>"",CONCATENATE(($AT$1&": "),AT2),"")

This code places the formula into the selected cell rather than column AJ

Dim lr As Longlr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
     With ActiveSheet
        ActiveCell.FormulaR1C1 = _
        "=IF(RC[1]<>"""",CONCATENATE((R1C37&"": ""),RC[1])&CHAR(10),"""")&IF(RC[2]<>"""",CONCATENATE((R1C38&"": ""),RC[2])&CHAR(10),"""")&IF(RC[3]<>"""",CONCATENATE((R1C39&"": ""),RC[3])&CHAR(10),"""")&IF(RC[4]<>"""",CONCATENATE((R1C40&"": ""),RC[4])&CHAR(10),"""")&IF(RC[5]<>"""",CONCATENATE((R1C41&"": ""),RC[5])&CHAR(10),"""")&IF(RC[6]<>"""",CONCATENATE((R1C42&"": ""),RC[6]" & _
        ")&CHAR(10),"""")&IF(RC[7]<>"""",CONCATENATE((R1C43&"": ""),RC[7])&CHAR(10),"""")&IF(RC[8]<>"""",CONCATENATE((R1C44&"": ""),RC[8])&CHAR(10),"""")&IF(RC[9]<>"""",CONCATENATE((R1C45&"": ""),RC[9])&CHAR(10),"""")&IF(RC[10]<>"""",CONCATENATE((R1C46&"": ""),RC[10]),"""")" & _
        ""


    End With
Application.ScreenUpdating = True

 

[MARK858]

Try...

Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False
Range("AJ2:AJ" & lr).FormulaR1C1 = _
        "=IF(RC[1]<>"""",CONCATENATE((R1C37&"": ""),RC[1])&CHAR(10),"""")&IF(RC[2]<>"""",CONCATENATE((R1C38&"": ""),RC[2])&CHAR(10),"""")&IF(RC[3]<>"""",CONCATENATE((R1C39&"": ""),RC[3])&CHAR(10),"""")&IF(RC[4]<>"""",CONCATENATE((R1C40&"": ""),RC[4])&CHAR(10),"""")&IF(RC[5]<>"""",CONCATENATE((R1C41&"": ""),RC[5])&CHAR(10),"""")&IF(RC[6]<>"""",CONCATENATE((R1C42&"": ""),RC[6]" & _
        ")&CHAR(10),"""")&IF(RC[7]<>"""",CONCATENATE((R1C43&"": ""),RC[7])&CHAR(10),"""")&IF(RC[8]<>"""",CONCATENATE((R1C44&"": ""),RC[8])&CHAR(10),"""")&IF(RC[9]<>"""",CONCATENATE((R1C45&"": ""),RC[9])&CHAR(10),"""")&IF(RC[10]<>"""",CONCATENATE((R1C46&"": ""),RC[10]),"""")" & _
        ""
Application.ScreenUpdating = True

 

[detriez]

Hi Mark.. This worked like a champ.. Thanks!
I added copy & paste (values)

                Range("AJ2:AJ" & lr).Copy
        Range("AJ2:AJ" & lr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[MARK858]

For Values just use....

    Dim lr As Long
    lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False
    With Range("AJ2:AJ" & lr)
        .FormulaR1C1 = "=IF(RC[1]<>"""",CONCATENATE((R1C37&"": ""),RC[1])&CHAR(10),"""")&IF(RC[2]<>"""",CONCATENATE((R1C38&"": ""),RC[2])&CHAR(10),"""")&IF(RC[3]<>"""",CONCATENATE((R1C39&"": ""),RC[3])&CHAR(10),"""")&IF(RC[4]<>"""",CONCATENATE((R1C40&"": ""),RC[4])&CHAR(10),"""")&IF(RC[5]<>"""",CONCATENATE((R1C41&"": ""),RC[5])&CHAR(10),"""")&IF(RC[6]<>"""",CONCATENATE((R1C42&"": ""),RC[6]" & _
                       ")&CHAR(10),"""")&IF(RC[7]<>"""",CONCATENATE((R1C43&"": ""),RC[7])&CHAR(10),"""")&IF(RC[8]<>"""",CONCATENATE((R1C44&"": ""),RC[8])&CHAR(10),"""")&IF(RC[9]<>"""",CONCATENATE((R1C45&"": ""),RC[9])&CHAR(10),"""")&IF(RC[10]<>"""",CONCATENATE((R1C46&"": ""),RC[10]),"""")" & _
                       ""
        .Value = .Value
    End With
    Application.ScreenUpdating = True

 

[detriez]

Oh yeah.. This is way more elegant.. Thanks!

 

[MARK858]

You're welcome, btw the elegance isn't really the reason I posted the code. The main reason I posted it is .Value = .Value is more efficient than Copy/PasteSpecial.

 

[detriez]

oh.. I get it.
I tried to incorporate this same logic (run if "A" is populated) on another function but it fails at

Selection.AutoFill Destination:=Range("AV2:AW2")

with

Run-time error '1004': AutoFill method of Range class failed

This is not failing because of the .value=Value.. It failed only after I added the lr code

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
    With ActiveSheet
    Range("AV2" & lr).Select
    ActiveCell.FormulaR1C1 = "=MidWd(RC[-1],1,1)"
    Range("AW2" & lr).Select
    ActiveCell.FormulaR1C1 = "=MidWd(RC[-2],2)"
    Range("AV2:AW2").Select
    Selection.AutoFill Destination:=Range("AV2:AW2")
   .Value=Value

    End With </code>[COLOR=#333333]Application.ScreenUpdating = True
[/COLOR]

This was the original code before I rolled in the lr code. You can see it only ran to row 47

    Range("AV2").Select
    ActiveCell.FormulaR1C1 = "=MidWd(RC[-1],1,1)"
    Range("AW2").Select
    ActiveCell.FormulaR1C1 = "=MidWd(RC[-2],2)"
    Range("AV2:AW2").Select
    Selection.AutoFill Destination:=Range("AV2:AW47")
    Range("AV2:AW47").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

 

[MARK858]

    Dim lr As Long
    lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    
    Range("AV2:AV" & lr).FormulaR1C1 = "=MidWd(RC[-1],1,1)"
    Range("AW2:AW" & lr).FormulaR1C1 = "=MidWd(RC[-2],2)"
    
    With Range("AV2:AW" & lr)
        .Value = .Value
    End With
    
    Application.ScreenUpdating = True

 

[detriez]

Oh.. I wasn't even close but this worked. I appreciate your help today.
I hope you have a great rest of your evening

 

[MARK858]

Oh.. I wasn't even close but this worked. I appreciate your help today.
I hope you have a great rest of your evening

You weren't far off, I just used a slightly different method. The code below would be similar to the way you tried it.

    Dim lr As Long
    lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    
    Range("AV2").FormulaR1C1 = "=MidWd(RC[-1],1,1)"
    Range("AW2").FormulaR1C1 = "=MidWd(RC[-2],2)"
    Range("AV2:AW2").AutoFill Destination:=Range("AV2:AW" & lr)
    Range("AV2:AW" & lr).Copy
    Range("AV2:AW" & lr).PasteSpecial Paste:=xlPasteValues
    
    Application.ScreenUpdating = True