CODEWITHSUNDEEP
vbaexcel
GaryWellock
GaryWellock

Reputation: 121

Moving cells to another sheet based on a text string using VB (excel)

Looking for any guidance to help write a VB script

In Excel we have a column containing filenames in Column A

"Y:\master-documentation\allo\dp\abo\tab\100mg\4.packaging\allo-tab-100mg-dp-9-pp-moa-[abo-pr-anl-0685-ver4.0].pdf" "Y:\master-documentation\allo\dp\abo\tab\100mg\4.packaging\zz-archive\test-form-00mg-dp-xxx-test-[test-file]-01.01.1900.docx"

sheetname=Files

Want to copy to sheetname=Current if string "zz-archive" is not contained in the cell, otherwise move to Sheetname=Archive.

We have used formulas but it will leave empty rows.

Any help greatly appreciated Thanks gary

Upvotes: 0

Views: 1035

Answers (2)

user4039065
user4039065

Reputation:

Here is a VBA solution that copies filtered data to each of the Current and Archive worksheets.

Sub categorize_filesnames()
    Dim ws As Worksheet, wsa As Worksheet, wsc As Worksheet
    Set ws = Sheets("Files")
    Set wsc = Sheets("Current")
    Set wsa = Sheets("Archive")

    wsa.Cells(1, 1).CurrentRegion.Offset(1, 0).ClearContents
    wsc.Cells(1, 1).CurrentRegion.Offset(1, 0).ClearContents

    With ws.Cells(1, 1).CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="=*zz-archive*"
        .Offset(1, 0).Copy Destination:=wsa.Cells(2, 1)
        .AutoFilter Field:=1, Criteria1:="<>*zz-archive*"
        .Offset(1, 0).Copy Destination:=wsc.Cells(2, 1)
        .AutoFilter
    End With

    Set wsc = Nothing
    Set wsa = Nothing
    Set ws = Nothing
End Sub

Upvotes: 1

user4039065
user4039065

Reputation:

If a formula based solution is still a possibility, then this array formula in Current!A2 should work.

=IFERROR(INDEX(Files!$A$2:$A$99,MATCH(0, IF(LEN(Files!$A$2:$A$99),IF(ISERROR(SEARCH("zz-archive",Files!$A$2:$A$99)),COUNTIF(A$1:A1,Files!$A$2:$A$99),1),1),0)),"")

It needs to be in A2 as a non-involved cell needs to be above it to avoid circular references. As an array formula it requires Ctrl+Shift+Enter rather than simply enter. Once entered correctly it can be filled down as necessary to catch all possible returns.

Addendum: To use the same formula in Archive!A2, change the ISERROR to ISNUMBER.

Upvotes: 1

Related Questions