HarveySpecter
New Member
- Joined
- Jan 4, 2019
- Messages
- 4
Hello All,
I've started playing with VBA some days ago, thus I'm new with Excel coding.
Below is a simplified version of the sheet I want to extrapolate the data from (sheet name "Data"):
<tbody>
</tbody>
What I'd like to do is to filter the columns "Customer" and "Platform" based on the values of the columns A and B of the sheet "First 50 Programs" (I though of creating a loop with the autofilter function) and run an if-statement that checks all the cells among the visible cells of the column "Motor Type" of the sheet "Data" that match multiple criteria as shown below.
At the end of the macro, the result should appear on the sheet "First 50 Programs" as follows:
<tbody>
</tbody>
This is the code I have tried to apply, but it doesn't work
Thanks a lot.
I've started playing with VBA some days ago, thus I'm new with Excel coding.
Below is a simplified version of the sheet I want to extrapolate the data from (sheet name "Data"):
| Customer | Platform | Program | Motor Type |
| A | 1 | pr1 | aa |
| A | 1 | pr2 | aa |
| A | 1 | pr3 | aa |
| A | 1 | pr4 | cc |
| A | 1 | pr5 | ee |
| A | 2 | pr1 | ee |
| A | 2 | pr2 | ee |
| A | 2 | pr3 | ee |
| A | 2 | pr4 | ee |
| A | 2 | pr5 | ee |
| A | 3 | pr1 | aa |
| A | 3 | pr2 | bb |
| A | 3 | pr3 | bb |
| A | 3 | pr4 | bb |
| B | 1 | pr1 | ee |
| B | 1 | pr2 | cc |
| B | 1 | pr3 | cc |
| B | 1 | pr4 | ee |
| B | 2 | pr1 | bb |
| B | 2 | pr2 | cc |
| B | 3 | pr1 | ee |
| B | 3 | pr2 | ee |
| B | 3 | pr3 | ee |
| B | 4 | pr1 | aa |
| C | 1 | pr1 | ee |
| C | 1 | pr2 | ee |
| C | 2 | pr1 | cc |
| C | 3 | pr1 | bb |
| D | 1 | pr1 | dd |
| D | 2 | pr1 | dd |
| D | 3 | pr1 | ee |
| D | 4 | pr1 | bb |
| D | 4 | pr2 | cc |
<tbody>
</tbody>
What I'd like to do is to filter the columns "Customer" and "Platform" based on the values of the columns A and B of the sheet "First 50 Programs" (I though of creating a loop with the autofilter function) and run an if-statement that checks all the cells among the visible cells of the column "Motor Type" of the sheet "Data" that match multiple criteria as shown below.
- if within the visible cells of filtered range (column "Motor Type"), there is at least one cell with the text "aa" or "bb" or "cc" or "dd" AND no cells with "ee", then return in column "Motor Category" the text "Conventional"
- if within the visible cells of filtered range (column "Motor Type"), there is at least one cell with the text "aa" or "bb" or "cc" or "dd" AND also at least one cell with text "ee", then return in column "Motor Category" the text "Multienergy"
- if within the visible cells of filtered range (column "Motor Type"), there are no cells with the text "aa" or "bb" or "cc" or "dd" AND there is at least one cell with text "ee", then return on cell "Motor Category" the text "Electric"
At the end of the macro, the result should appear on the sheet "First 50 Programs" as follows:
| Customer | Platform | Motor Category |
| A | 1 | Multienergy |
| A | 2 | Electric |
| A | 3 | Conventional |
| B | 1 | Multienergy |
| B | 2 | Conventional |
| B | 3 | Electric |
| B | 4 | Conventional |
<tbody>
</tbody>
This is the code I have tried to apply, but it doesn't work
Code:
Sub Types_Of_Platforms()
Dim i As Long
Dim lRow As Long
Dim VisCell, c As Range
lRow = Worksheets("First 50 Programs").Cells(Rows.Count, 2).End(xlUp).Row
For i = 5 To lRow
Sheets("Data").Select
With ActiveSheet.Range("$A$1:$D$34")
.AutoFilter Field:=1, Criteria1:=Sheets("First 50 Programs").Range("A" & i).Value
.AutoFilter Field:=2, Criteria1:=Sheets("First 50 Programs").Range("B" & i).Value
End With
Set VisCell = Worksheets("Data").Range("D1:D34").SpecialCells(xlCellTypeVisible)
For Each c In VisCell.Cells
If c.Value2 = "aa" Or c.Value2 = "bb" Or c.Value2 = "cc" Or c.Value2 = "dd" And c.Value2 <> "ee" Then
Sheets("First 50 Programs").Range("C" & i).Value = "Conventional"
ElseIf c.Value2 <> "aa" And c.Value2 <> "bb" And c.Value2 <> "cc" And c.Value2 <> "dd" And c.Value2 = "ee" Then
Sheets("First 50 Programs").Range("C" & i).Value = "Only BEV"
ElseIf c.Value2 = "aa" Or c.Value2 = "bb" Or c.Value2 = "cc" Or c.Value2 = "dd" And c.Value2 = "ee" Then
Sheets("First 50 Programs").Range("C" & i).Value = "Multi-Energy"
Else: Sheets("First 50 Programs").Range("C" & i).Value = "Error"
End If
Next c
Next i
End SubThanks a lot.
