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

detriez

detriez

Board Regular
Joined
Sep 13, 2011
Messages
193
Office Version
  1. 365
Platform
  1. Windows
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

Code: 
=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

Code: 
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try...


Code: 
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
 
Upvote 0
Hi Mark.. This worked like a champ.. Thanks!
I added copy & paste (values)

Code: 
                Range("AJ2:AJ" & lr).Copy
        Range("AJ2:AJ" & lr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
Upvote 0
For Values just use....

Code: 
    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
 
Upvote 0
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.
 
Upvote 0
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")
Click to expand...
with
Run-time error '1004': AutoFill method of Range class failed
Click to expand...
This is not failing because of the .value=Value.. It failed only after I added the lr code


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

Code: 
    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
 
Upvote 0
Code: 
    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
 
Last edited:
Upvote 0
Oh.. I wasn't even close but this worked. I appreciate your help today.
I hope you have a great rest of your evening
 
Upvote 0
detriez said:
Oh.. I wasn't even close but this worked. I appreciate your help today.
I hope you have a great rest of your evening
Click to expand...

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

Code: 
    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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

detriez
  • Solved
Convert vba to formula with Named Ranges
2
Replies
15
Views
1K
Fluff
Fluff
detriez
  • Solved
Do not fire code on header row when using End(xlUp).Row
Replies
2
Views
1K
detriez
detriez
P
  • Solved
Finding Letter through Formula
Replies
2
Views
446
Pestomania
P
Martin_H
  • Solved
Simplify macro [VBA]
Replies
4
Views
445
Fluff
Fluff
BigPaws
  • Question Question
VBA to create new worksheets from a template, populate the new worksheet by copying data from another sheet to specific locations.
Replies
0
Views
196
BigPaws
BigPaws

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top