Macro - If Statement

[michael_allen_24]

Hello, I need some help. I am not very experienced at writing macros and I cannot figure this one out so any help would be much appreciated. I am trying to create a macro that checks whether a value in Cell C2 is either "Rec" or "Spec". And then depending on that result, copy the value from cell D1 or I1 and paste it into the either filter C19:E19 or G19:J19. It works when I take the second IF argument out but when I put them both in, it does not. And if I put the End If after the first or second argument, it give me the compile error: end if without block if. Not sure what I am doing wrong, it’s probably something very basic. Again any help would be much appreciated. Thanks in advance.</SPAN>

' If ActiveSheet.Range("C2") = "Rec" Then</SPAN>
Range("D1").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-1]"</SPAN>
Range("D1").Select</SPAN>
Selection.Copy</SPAN>
ActiveSheet.Range("$C$19:$AF$1205").AutoFilter Field:=1, Criteria1:=Range("D1").Value</SPAN>
Range("C19:E19").Select</SPAN>

' If ActiveSheet.Range("C2") = "Spec" Then</SPAN>
Range("I1").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-1]"</SPAN>
Range("I1").Select</SPAN>
Selection.Copy</SPAN>
ActiveSheet.Range("$C$19:$AF$1205").AutoFilter Field:=1, Criteria1:=Range("I1").Value</SPAN>
Range("C19:E19").Select</SPAN>

End Sub</SPAN>

 

[njimack]

Try the below (untested) code:

Sub b()
Dim Criteria
With ActiveSheet
Select Case .Range("C2")
    Case "Rec"
        Criteria = .Range("C1").Value
        
    Case "Spec"
        Criteria = .Range("H1").Value
End Select
.Range("$C$19:$AF$1205").AutoFilter field:=1, Criteria1:=Criteria
End With
End Sub

 

[michael_allen_24]

Thanks. It didn't quite work. Sorry I am learning as we go. I think that my code above was misleading because depending on what is in cell C2 (Rec or Spec), the resulting value will be pasted into a separate filter. According to your formula above, it is pasting the value of C1 or H1 into </SPAN>AutoFilter field:=1. </SPAN></SPAN>

What should happen is the value of C1 should be pasted into AutoFilter field:=1 and the value of H1 should be pasted into AutoFilter field:=14.</SPAN></SPAN>

How do I work that into the code?</SPAN></SPAN>

Hopefully that makes sense and thanks again for your help.</SPAN></SPAN>

 

[njimack]

Thanks. It didn't quite work. Sorry I am learning as we go. I think that my code above was misleading because depending on what is in cell C2 (Rec or Spec), the resulting value will be pasted into a separate filter. According to your formula above, it is pasting the value of C1 or H1 into AutoFilter field:=1.

What should happen is the value of C1 should be pasted into AutoFilter field:=1 and the value of H1 should be pasted into AutoFilter field:=14.

How do I work that into the code?

Hopefully that makes sense and thanks again for your help.

Like this?

Sub b()
Dim Criteria
With ActiveSheet
    Select Case .Range("C2")
        Case "Rec"
            .Range("$C$19:$AF$1205").AutoFilter field:=1, Criteria1:=.Range("C1").Value
            
        Case "Spec"
            .Range("$C$19:$AF$1205").AutoFilter field:=1, Criteria1:=.Range("H1").Value
    End Select
    
    End With
End Sub

 

[michael_allen_24]

Awesome! It worked. Just had to change the Auto Filter field to 14 on the second one. Thanks again for your help.