For each cell (Match Row) Then....Multiple Ranges

Question

I'm a beginner when it comes to Excel VBA and I'm completely lost.

I want the macro to partial match either "Buick", "Chevrolet", or "Pontiac". The matches in Column D "SheetJS" should be copied to Column AA in "Sheet1". The matches in Column E "SheetJS" should be copied to Column AH in "Sheet1". The matches in Column F ("SheetJS") should be copied to Column AL in "Sheet1".

I have other ranges but once I get this code working, I should be able to get code the rest.

Sub Extract_Data_Buick()
    For Each cell In Sheets("SheetJS").Range("D1:D200")
        matchrow = cell.Row

        If (cell.Value = "*Buick*" Or cell.Value = "*Chevrolet*" Or cell.Value = "*Pontiac*") Then
            Sheets("Sheet1").Range("AA" & matchrow).Value = cell.Value

        End If
    Next

    For Each cell In Sheets("SheetJS").Range("E1:E200")
        matchrow = cell.Row

        If (cell.Value = "*Buick*" Or cell.Value = "*Chevrolet*" Or cell.Value = "*Pontiac*") Then
            Sheets("Sheet1").Range("AH" & matchrow).Value = cell.Value

        End If
    Next

    For Each cell In Sheets("SheetJS").Range("F1:F200")
        matchrow = cell.Row

        If (cell.Value = "*Buick*" Or cell.Value = "*Chevrolet*" Or cell.Value = "*Pontiac*") Then
            Sheets("Sheet1").Range("AL" & matchrow).Value = cell.Value

        End If
    Next
End Sub

Any tips will be greatly appreciated!

Update 01.11.20

I have also tried but I still can't get the code to work.

Sub Extract_Data_Buick()
    For Each cell In Sheets("SheetJS").Range("D1:D200")
        matchrow = cell.Row

        if (cell.value like "*Buick*") Or (cell.Value = "*Chevrolet*") Or (cell.Value = "*Pontiac*") Then
            Sheets("Sheet1").Range("AA" & matchrow).Value = cell.Value

        End If
    Next

    For Each cell In Sheets("SheetJS").Range("E1:E200")
        matchrow = cell.Row

        if (cell.value like "*Buick*") Or (cell.Value = "*Chevrolet*") Or (cell.Value = "*Pontiac*") Then
            Sheets("Sheet1").Range("AH" & matchrow).Value = cell.Value

        End If
    Next

    For Each cell In Sheets("SheetJS").Range("F1:F200")
        matchrow = cell.Row

        if (cell.value like "*Buick*") Or (cell.Value = "*Chevrolet*") Or (cell.Value = "*Pontiac*") Then
            Sheets("Sheet1").Range("AL" & matchrow).Value = cell.Value

        End If
    Next
End Sub

Do you really want to put these values in their respective rows on the other sheet or do you actually need to put them in a list on that sheet? Maybe, to really help you get some fast coding going it is best to include a screenshot with what you have got now and one with your desired results. — JvdV, Jan 11 '20 at 08:13

score 0 · Answer 1 · answered Jan 11 '20 at 06:01

0

don't use

cell.Value = "*Buick*"

but please use

if instr ("Buick", cell.value)> 0 then ...

or

if (cell.value like "*Buick*") then ....

refer:https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator

answered Jan 11 '20 at 06:01

Dang D. Khanh

1,440
6
13

I had tried If (cell.Value Like "*Buick*") Or (cell.Value Like "*Chevrolet*") Or (cell.Value Like "*Pontiac*") Then but it didn't work – Janet Delgado Jan 11 '20 at 06:03
You are trying to match part of the string,so you must use * in the expression as I wrote – Dang D. Khanh Jan 11 '20 at 06:26
I tried If (cell.Value Like "*Buick*") Or (cell.Value Like "*Chevrolet*") Or (cell.Value Like "*Pontiac*") But it only works when I use 2 not 3. Such as "*Buick*" or "*Chevrolet*". For some reason the * aren't showing up. – Janet Delgado Jan 11 '20 at 23:02

For each cell (Match Row) Then....Multiple Ranges

1 Answers1