Copy and Paste All Cells That Meet Criteria

Question

I'm a beginner when it comes to VBA and Macros; therefore, I'm not sure what the exact verbiage is but I believe I'm looking for help with looping.

My macro currently partially matches cells in each row in "SheetJS" containing either "Mercedes-Benz" or "BMW" and pastes the values to Column D in "Sheet1". However, it only copies the first iteration/cell that partially matches the text.

I want the macro to copy and past all matches. For example the 1st iteration should be copied/pasted to "Sheet1" Column D, 2nd in Column H, 3rd in L, and so on. Each iteration should have 3 cells in between.

I don't even know how to move forward with this.

Any tips would be greatly appreciated.

Thanks!

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

        If (cell.Value Like "*Mercedez-Benz*") Or (cell.Value Like "*BMW*") Then
            Sheets("Sheet1").Range("D" & matchrow).Value = cell.Value
        End If
    Next
End Sub

Edit 01.09.20

I want the macro to get all iterations/partial matches in each row and copy them. The current macro only copies the first match. I don't want to copy the entire row just the individual cells.

For example the first match in "SheetJS" should be copied to Column D in "Sheet1". The second match, (if any) should be copied to Column H, 3rd in column L, 4th in column P, etc. Every match should be placed 4 cells from each other.

SheetJS All matches are highlighted in yellow. The values in each cell should copied over to "Sheet1"

Sheet1 The first match in each row is in Column D, the 2nd( if any) is in Columb H, etc.

@AntiDrondert No. It must be a number. `Range("D" & matchrow)`. — Sfagnum, Jan 09 '20 at 06:48
`matchrow` - must be independent counter as i understand. if you want iteration for each column i think it must like this:```for i=1 to lastcolumncount For Each cell In Sheets("SheetJS").Range(Sheets("SheetJS").Cell(1,i),Sheets("SheetJS").Cell(200,i) 'your code next next``` — Sfagnum, Jan 09 '20 at 07:07
@JanetDelgado adding a picture of your data to your post might help us to understand your problem a bit better. — Dean, Jan 09 '20 at 08:22
Hey guys, thanks for the help. I uploaded more information and pictures of what I mean. — Janet Delgado, Jan 10 '20 at 05:49

FaneDuru · Answer 1 · 2020-01-11T09:28:38.017

1

You asked yesterday something similar. I asked for clarifications and I supplied a solution without receiving any sign from you... Anyhow, maybe this time you will look at the next code and maybe test it. It works very fast, avoiding cells iteration. It works only in memory:

Private Sub Extract_Data_Bis()
  Dim rngArr As Variant, dArr As Variant
  Dim sh As Worksheet, i As Long, j As Long, k As Long
  Dim lngOcc As Long, lngChanges As Long, boolFound As Boolean
  Dim lngSameRow As Long, lngMised As Long

  Set sh = Sheets("Sheet1")
   rngArr = Sheets("SheetJS").Range("A1:ZZ200").Value
   dArr = sh.Range("D1:F200").Value

    For i = 1 To UBound(rngArr, 1)
        boolFound = False: k = 0: lngSameRow = 0
        For j = 1 To UBound(rngArr, 2)
          If InStr(UCase(rngArr(i, j)), UCase("Mercedez-Benz")) > 0 Or _
                              InStr(UCase(rngArr(i, j)), "BMW") > 0 Then
              If Not boolFound Then
                lngSameRow = i
                k = 1
              Else
                If lngSameRow = i Then
                    k = k + 1
                End If
              End If
              lngOcc = lngOcc + 1: boolFound = True
              If k <= 3 Then
                dArr(i, k) = rngArr(i, j)
                lngChanges = lngChanges + 1
              Else
                lngMised = lngMised + 1
              End If

          End If
       Next j
    Next i
    sh.Range("D1:F200").Value = dArr
    MsgBox lngOcc & " occurrences, versus " & lngChanges & " changes done. " & lngMised & " missed..."
End Sub

In case there are more then 3 occurrences on a row, at the end it makes a balance between occurrences, changes done and missed ones...

edited Jan 11 '20 at 09:28

answered Jan 09 '20 at 08:59

FaneDuru

38,298
4
19
27

Thanks for the help but I actually want all occurrences. I don't want them in a row though. I want the first occurence in column D, second in Column H, 3rd in column L, 4th in column P, etc – Janet Delgado Jan 10 '20 at 06:00
Each time you want something different... Did my answer do what you requested? If yes, I do not think you will get ill if you will vote it up... It was your job to take in consideration all occurrences, I think... In my answer to your similar previous post I raised this aspect but you were not moved at all. You even did not say anything. So, in order to help you posting another answer for this new theme, can you estimate the maximum number of necessary columns to be returned (etc. included...)? – FaneDuru Jan 10 '20 at 07:30
@Janet Delgado: Should I understood that you need to build the return columns area from four to four? I mean, the next column will be T, the following one X and so on? – FaneDuru Jan 10 '20 at 08:06
It didn't work. The pop up window stated there were 0 occurrences and didn't copy anything either. I'm a beginner and as such I don't even know what you attempted to do. – Janet Delgado Jan 11 '20 at 05:37
@Janet Delgado: But, in your previous comment, you did not say anything about this aspect. You just said that you need all occurrences, on different rows... Never mind, are you sure that your data to be processed are in sheet "SheetJS"? If yes, are the strings to be found exactly like the ones in your code example? Anyhow, I will change the code to be case insensitive. Please refresh the page and try the updated code. – FaneDuru Jan 11 '20 at 09:22
@Janet Delgado: Adapted the code to be case insensitive. I cannot stop myself asking why didn't you tried to take advantage if posting and you did not clarify my question regarding the columns where the occurrences to be returned. D, H, L, P and what else... – FaneDuru Jan 11 '20 at 09:35
yes, the data is in "SheetJS" but would like it copied to "Sheet1". Copied over to Columns D, H, L, P, T, X, etc. 4 cells in between each match or occurrence. I tried the new code but it worked incorrectly. It only captured the first UCase("Mercedez-Benz")) and not BMW. It also only captured the first 2 occurences and not in the order I wanted them. – Janet Delgado Jan 11 '20 at 22:55
Please post here examples of the cells value which could not be 'captured' by the code. Can you share your workbook on google drive? This will solve the problem without any doubt, I think... – FaneDuru Jan 12 '20 at 10:04
@FaneDuru: to some degree you can help in the way you wish, but the purpose of Stack Overflow is to **help in public**, so future readers can benefit from what is in your answer. I find that private support channels rarely get distilled into good answers, and thus that assistance is lost for future readers. – halfer Jan 13 '20 at 18:15
@halfer: This is I was trying to do, I think... I didn't ask to receive something in private. He looks not being able to describe his data to be processed in a proper way. Or maybe I am not able to understand. I thought/hoped that asking him to post here the strings not found by my previous algorithm, I will find a way to make it working for his data, too. I can live without definitively solve an issue, but usually I do what I can in order to solve it... – FaneDuru Jan 13 '20 at 18:43
1

@FaneDuru, sure, I understand. The trouble with file lockers containing code is that when other people try to see them, they have been deleted ("tidied up" since they are "no longer needed"). Thus, help that was meant to benefit others ends up effectively being private, because the corresponding parties at the time are the only ones who know what code was exchanged. – halfer Jan 13 '20 at 23:06
@halfer: I agree, in principle... I thought that the code is clear for people able to understand it and I just wanted to make it usable for the person raising the problem. If I am more interested than him in doing that, it's a little strange for me, but I can live with that. I must confess that it is very hard for me to understand such people... Sometimes, it's better to abandon all ways to help them, indeed and look to the benefit of people who really cares. – FaneDuru Jan 14 '20 at 10:51

Copy and Paste All Cells That Meet Criteria

1 Answers1