Macro - If Statement

michael_allen_24

Board Regular
Joined
Dec 29, 2010
Messages
64
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>
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Try the below (untested) code:

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

Board Regular
Joined
Dec 29, 2010
Messages
64
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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?

Code:
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

Board Regular
Joined
Dec 29, 2010
Messages
64
Awesome! It worked. Just had to change the Auto Filter field to 14 on the second one. Thanks again for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top