CODEWITHSUNDEEP
excelvba
CleanRider
CleanRider

Reputation: 149

Excel Button Issues

I made a very simple macro that adds a column to the end of table with concatenated values, there is no issue when i click on the vba button.enter image description here

However an issue arises when I assign a macro to the button on a separate workbook, it fails to fill all the rows with the correct values. (See black circle on Image) enter image description here

My code is below

  sub OrganizingResearchTracker()
    
    'Variables
    Dim lr As Long
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    
    Dim tbl As ListObject
    Dim wbNames As Variant, wb As Workbook, w As Workbook, El As Variant, boolFound As Boolean
    
      wbNames = Split("January,February,March,April,May,June,July,August,September,October,November,December", ",")
      For Each w In Workbooks
        For Each El In wbNames
            If w.Name = "Completed Research Docs_Tracker_" & El & " 2020.xlsm" Then
                Set wb = w: boolFound = True: Exit For
            End If
        Next
        If boolFound Then Exit For 'in order to stop iteration if a lot of workbooks are open
         Next
    
    '1. Column AB - Descriptive Field - Client Name - Manager Name - Research Deliverable
    
    wb.Activate
    Set tbl = ActiveSheet.ListObjects("Table_owssvr")
    With tbl
            .ListColumns.Add.Name = "Client Name - Manager Name - Research Deliverable"
    End With
    
        Range("AB2:AB" & lr).FormulaR1C1 = "=CONCATENATE(RC[-22],"" - "",RC[-26],"" - "",RC[-15])"
end sub

Anyone had this issue and know how to amend it? thank you

Upvotes: 0

Views: 64

Answers (1)

BigBen
BigBen

Reputation: 49998

It'd be easier to just write the formula to the .DataBodyRange of the ListColumn you created.

With tbl
    ...
    .ListColumns("Client Name - Manager Name - Research Deliverable").DataBodyRange.FormulaR1C1 = _
        "=CONCATENATE(RC[-22],"" - "",RC[-26],"" - "",RC[-15])"
End With

Upvotes: 1

Related Questions