VBA Question of adding 2 Columns at the end of data set and filling Columns down with formulas

[jgome21]

Hi All - I am a newbie at writing VBA. I have a set of data ranging thru columns A thru O. I am trying to add to new columns at the end titled GCP(Column P) and PLCC (Column Q).

Then I want to fill the two columns with these formulas.
ColumnP:=IF(ISERR(SEARCH("GCP",$D2,1))," ","GCP")
ColumnQ:=F2&P2

So far I have the coding below but isn't working properly. Can someone please assist? Thank you.

Sub CCFormat()


Columns("P:Q").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Range("P2") = "=IF(ISERR(SEARCH("GCP",$D2,1))," ","GCP")": Range("P2:P" & LastRow).FillDown
Range("Q2") = "=F2&P2": Range("q2:P" & LastRow).FillDown
End Sub

 

[Trebor76]

Hi jgome21,

Welcome to MrExcel!!

Try this while on the sheet in question:

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    On Error Resume Next 'In case there's no data on the tab
        lngLastRow = Range("A:O").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If lngLastRow >= 2 Then
            Range("P2:P" & lngLastRow).Formula = "=IF(ISERR(SEARCH(""GCP"",$D2,1)),"" "",""GCP"")"
            Range("Q2:Q" & lngLastRow).Formula = "=F2&P2"
        End If
    On Error GoTo 0
    
    Application.ScreenUpdating = True
        
End Sub

Regards,

Robert

 

[jgome21]

Hi jgome21,

Welcome to MrExcel!!

Try this while on the sheet in question:

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    On Error Resume Next 'In case there's no data on the tab
        lngLastRow = Range("A:O").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If lngLastRow >= 2 Then
            Range("P2:P" & lngLastRow).Formula = "=IF(ISERR(SEARCH(""GCP"",$D2,1)),"" "",""GCP"")"
            Range("Q2:Q" & lngLastRow).Formula = "=F2&P2"
        End If
    On Error GoTo 0
    
    Application.ScreenUpdating = True
        
End Sub

Regards,

Robert

Thank you very much. It worked.

 

[Trebor76]

Glad to hear it and you're welcome