Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
685 views
in Technique[技术] by (71.8m points)

excel - If Cell Contains This or That Paste onto Another Sheet

I have 2 sheets in my workbook SheetJS and Sheet1. I have this code that partially matches cells in each row that contain the phrase "ABC" in SheetJS and copies them to Column D in Sheet1. It them partially matches cells that contain the phrase "123" in SheetJS and copies then to Column G in Sheet1.

How can I change the code to partially match cells in each row in Sheet1 containing either "ABC" or "132" and pastes the values to Column D in Sheet1?

I will write a similar macro to copy values into Column G in Sheet1

Sub Extract_Data_or()

    For Each cell In Sheets("SheetJS").Range("A1:ZZ200")

        matchrow = cell.Row

        If cell.Value Like "*ABC*" Then 

            Sheets("Sheet1").Range("D" & matchrow).Value = cell.Value

        ElseIf cell.Value Like "*123*" Then

            Sheets("Sheet1").Range("G" & matchrow).Value = cell.Value

        End If

    Next

End Sub

Any tips will help thank you!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Use OR logic.

Sub Extract_Data_or()
    For Each cel In Sheets("SheetJS").Range("A1:ZZ200")
        matchrow = cel.Row

        If (cel.Value Like "*ABC*") Or (cel.Value Like "*123*") Then
            Sheets("Sheet1").Range("D" & matchrow).Value = cel.Value
        End If
    Next
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...