Re: Filter with multiple criteria in VBA Hello Kerrold, I have a similar setup, however I use my code compiling multiple sheets into one and only copying data based on my filter so it may need some tweeking but should help.
Select code and click icons to indent/outdentIf you look at any code examples on this website you will see that the code is indented.A Simple If Then ExampleThe following code prints out the names of all students with marks greater than 50 in French. Sub ReadMarksDim i As Long ' Go through the marks columns For i = 2 To 11' Check if marks greater than 50 If Sheet1.Range( 'C' & i).Value 50 Then ' Print student name to the Immediate Window(Ctrl + G) Debug.Print Sheet1.Range( 'A' & i).Value & ' ' & Sheet1.Range( 'B' & i).ValueEnd If Next End SubResultsBryan SnyderJuanita MoodyDouglas BlairLeah FrankMonica BanksPlay around with this example and check the value or the sign and see how the results change.If ConditionsThe piece of code between the If and the Then keywords is called the condition. A condition is a statement that evaluates to true or false. They are mostly used with Loops and If statements. When you create a condition you use signs like ,=, 5x is greater than 5x = 5x is greater than or equal to 5x = 5x is equal to 5x 5x does not equal 5x 5 And x 10x is equal to 2 OR x is greater than 10Range('A1') = 'John'Cell A1 contains text 'John'Range('A1') 'John'Cell A1 does not contain text 'John'You may have noticed x=5 as a condition.
ResultsUsing If And/If OrYou can have more than one condition in an If Statement. The VBA keywords And and Or allow use of multiple conditions.These words work in a similar way to how you would use them in English.Let’s look at our sample data again. We now want to print all the students that got over between 50 and 80 marks.We use And to add an extra condition. The code is saying: if the mark is greater than or equal 50 and less than 75 then print the student name. Sub CheckMarkRangeDim i As Long, marks As Long For i = 2 To 11' Store marks for current studentmarks = Sheet1.Range( 'C' & i).Value' Check if marks greater than 50 and less than 75 If marks = 50 And marks = 6 Then Debug.Print 'True' Else Debug.Print 'False' End If End SubUsing If NotThere is also a NOT operator. This returns the opposite result of the condition. ConditionResultTRUEFALSEFALSETRUEThe following two lines of code are equivalent.
If marks = 40 Thenas are If True Then If Not False Thenand If False Then If Not True ThenPutting the condition in parenthesis makes the code easier to read If Not (marks = 40) ThenA common usage of Not when checking if an object has been set. Take a worksheet for example. Here we declare the worksheet Dim mySheet As Worksheet' Some code hereWe want to check mySheet is valid before we use it. We can check if it is nothing. If mySheet Is Nothing ThenThere is no way to check if it is something as there is many different ways it could be something.
Therefore we use Not with Nothing If Not mySheet Is Nothing ThenIf you find this a bit confusing you can use parenthesis like this If Not (mySheet Is Nothing) ThenThe IIF functionVBA has an fuction similar to the Excel If function. In Excel you will often use the If function as follows:=IF(F2=””,””,F1/F2)The format is=If(condition, action if true, action if false).VBA has the IIf statement which works the same way. Let’s look at an example. In the following code we use IIf to check the value of the variable val.
If the value is greater than 10 we print true otherwise we print false. Sub CheckValDim result As BooleanDim val As Long ' Prints Trueval = 11result = IIf(val 10, True, False)Debug.Print result' Prints falseval = 5result = IIf(val 10, True, False)Debug.Print resultEnd SubIn our next example we want to print out Pass or Fail beside each student depending on their marks. In the first piece of code we will use the normal VBA If statement to do this. Sub CheckMarkRangeDim i As Long, marks As Long For i = 2 To 11' Store marks for current studentmarks = Sheet1.Range( 'C' & i).Value' Check if student passes or fails If marks = 40 Then ' Write out names to to Column FSheet1.Range( 'E' & i) = 'Pass' ElseSheet1.Range( 'E' & i) = 'Fail' End If Next End SubIn the next piece of code we will use the IIf function. You can see that the code is much neater here.
Sub CheckMarkRangeDim i As Long, marks As Long For i = 2 To 11' Store marks for current studentmarks = Sheet1.Range( 'C' & i)' Check if student passes or failsSheet1.Range( 'E' & i).Value = IIf(marks = 40, 'Pass', 'Fail')Next End SubYou can see the IIf function is very useful for simple cases where you are dealing with two possible options.Using Nested IIfYou can also nest IIf statements like in Excel. This means using the result of one IIf with another. Let’s add another result type to our previous examples. Result of exerciseAnswer to ExerciseThe following code shows how to complete the above exercise.
Note: There are many ways to complete this so don’t be put off if your code is different. Hi, i hope this is related enough to ask. I am not that good at vb & if worded correct:– how do you add a boolean test to a vb, where i need a ‘byref’ as part of that boolean eg:If FX(“DD”) Then ‘example below for doing a FIND of characters in a cell. Need “y” variable for eg: “DD”for eg:Function isA(ByRef x As Range): On Error Resume Next: isA = 0 ‘portions here hope enoughDim a As String, findX As Range, rng As Integer: rng = x.row: a = Cells(rng, D2).Valuedim findX As Range, rng As Integer: rng = x.row ‘PROBLEM LINE BELOW: (Y as string not work)‘FX(y as string) As Boolean: Set findX = x.find(y, LookAt:=xlPart, MatchCase:=True): If Not findX Is Nothing Then FX = True ‘where i have an external macro this very same thing (is unreliable/ vb loses its place? With wacky results) for:If FX(“DD”) Then If a 3 And a.
Hi,I am trying to do 2 things. I want it so that when you click on the Quarterly check box, the button below will apply to the main sheet (FormB) and sheet 2 (FormAquart).
I also want it so that when you click on the Semi-Annual check box that the code below applies to the main sheet and sheet 3(FormASemiannual). Do you have any insight on how to do so? At the moment, the code below lets you click on the ADD Row button and both sheets 2 and 3 duplicate the additional rows and pastes the formulas and format.
I just need it so that the conditions change based on whether you click on Quarterly or Semi-Annual. ThanksSub AddRowClickApplication.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualDim mySheetsDim i As LongmySheets = Array(“formb”, “formaquart”, “formasemiannual”)For i = LBound(mySheets) To UBound(mySheets)With Sheets(mySheets(i)).Range(“A28”).EntireRow.Insert Shift:=xlDownEnd WithWith Sheets(mySheets(i)).Range(“A28:P28”).FillDownEnd WithNext iApplication.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomaticEnd Sub.
Is it possible to refer to a variable’s value if that variable name is in your data? IE (very over simplified), in cells 1,1 I have the word “SuperMan”. I have a variable; Dim SuperMan as String; SuperMan = “Big red S on his chest”. I would like to have a procedure that goes down the rows (for each, or for to, don’t really care) and adds in the variable’s values based on the values in column 1.
I can get it to say SuperMan, and in the immediate window I can see that SuperMan = “Big red S on his chest”, but I can’t get that value into cells 1,2. Unless I specifically type “SuperMan” in the coding, I can’t reference cells 1,1 to return the variables value. Hi below is a script that is stopping at;If Cells(x, “C”) = “”and will not go further.
The “Marks” code above works great for my use, but I’m having trouble when I try to convert “Debug.Print” to an output cell. Here’s what I have — the commented items at the bottom are what I’ve tried, unsuccessfully. Please forgive the overly detailed commenting — I have a poor memory and really rely on the “breadcrumbs.”PS I just signed up for the classes today — the articles on the If statement, the Dim, and Error Handling alone were worth the price of admission. Hello Sir Paul,I’m a newbie in using VBA. I want to create a form with Macro Excel. I’ll set an example, I have a dropdown in a cell with the list of countries and a country has its own corresponding number.
For example, Australia, Austria and Belgium is equals to 5 and some countries are equal to 1,2,3 or 4. For example, upon choosing Australia in the dropdown and when I click submit, the corresponding value of Australia which is 5 will appear on the other cell. Badly need your help for our project please. Thank You Sir.
Hi,I have a userform that writes data to a database but I want to validate the contents of three comboboxes against three columns in a hidden sheet.What I'm looking for (and I've searched this forum all night to try and find it) is some code to find the row in the hidden sheet that matches all three combobox values in the userform.Ideally I'd like to accomplish this without looping (the hidden sheet may have a lot of rows)Can anyone point me in the right direction or to a post where this has been done already?TIA.Uncle Peter. The following example runs when command Button 1 is clicked. It takes values from 3 comboboxes and see's if they are located on Sheet1 in columns A, B, C. Firstly, many thanks for taking the time to help Nimrod.Your code works well (and fast too!) but unfortunately the function returns a true if it finds the values anywhere within each of the worksheet columns. I'm only after a valid condition if the three combobox values are present on the same row like:Col B Col C Col D-Big Dogs BrownSmall Dogs BlackBig Dogs BlackSmall Cats WhiteSmall Dogs WhiteBig Cats BrownComboBox1.Value = 'Small'ComboBox2.Value = 'Dogs'ComboBox3.Value = 'White'Is there a way of doing this without loops (slow)Many thanks in advance. Hello cakeypig.
(19 responses) Trying to do something like this If 'Yards' is in column A then copy and paste all the rows to a new book If 'Grass' is in Column A the.(9 responses) I have a user from that is the first part of a mass email script that I am trying to write. What I would like is select one or several checkb.(7 responses) I want to enter information on to the first worksheet, then the code to copy and paste the data to the selected hospital and ward entered in.(24 responses) I have figured out some code to copy all the data from a table, create a new workbook, paste the data and then save the workbook as a CSV as.(10 responses) I have a series of columns for orders on my spreadsheet. The furthest left column contains item numbers, the second column contains the quan.The Holy Grail of Excel Tips $12.60.