How to I change this code to use 'select case' instead of using IF? I have about 20 more if's and I though select case may be easier . .
'20
If ComboBox8.Value = "20" Then
MyPlace.Formula = "=IF(OR(RIGHT($AU16,2)=""ge"",COUNTIF(X16:aq16,""X"")=20),""Reject"",""Accept"")"
End If
' '19
If ComboBox8.Value = "19" Then
MyPlace.Formula = "=IF(OR(RIGHT($AU16,2)=""ge"",COUNTIF(X16:ap16,""X"")=19),""Reject"",""Accept"")"
End If
' '18
If ComboBox8.Value = "18" Then
MyPlace.Formula = "=IF(OR(RIGHT($AU16,2)=""ge"",COUNTIF(X16:ao16,""X"")=18),""Reject"",""Accept"")"
End If
I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.
Rows("2:2").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Dim Cell As Variant For Each Cell In Range("A1:OL1") Select Case Cell.Value Case "Eng1" Cell.Offset(1, 0).Value = "Engine One" tons more in the middle here Case Else Cell.Offset(1, 0).Value = "N/A"
Rather then returning "N/A", how could I reference the cell above and just copy it instead?
For the following code, I'm getting the " Case without Select Case" error (On Case 3 to 5...assuming more are wrong too, but debug can't get there yet). I thought I had it right, obviously don't. Can anyone spot how my code is wrong? ....
I need a macro that when the worksheet is open if the text in Column A is as presented in the sample then the Mathmatical calculations will be performed in Column G. Currently I use a Do While Loop which works (takes a long time), but I know there must be a faster more efficient way.
I have a report and users will need to key in password while opening. Currently, i am using inputbox and it works exactly like what i expected. However, the only downside is that i cannot mask the password.
I am aware that I have to use userform to do so. How to replace the input box with userform and still deliver the same result.
I know how to create a user from with title, text box, OK and Cancel buttons, and that's it. How to put it in my current code.
Below are the codes that I currently have. There are 4 possible outputs.
I was wondering if any one can help me with simplifying my Select Case, i have provided 3 examples , 101 , 102 and 103. I will be going all the way from 101 to 199 and I dont want to write every single one. and note that the range values change for each one as well.
I'm creating a small spreadsheet for client data in Excel and I want it formatted a certain way, I did consider data validation but it proved to just be annoying.
I've been working on some VBA code to automatically change whatever text is typed into a cell to the correct case (ucase, lcase or proper) and while I can get it working for a single range of cells getting it to work for more is proving difficult.
If there is a different way, like to select the value in the selected range and cross reference it against a list. i'm not sure... trying to learn and be efficient.
I have a multiple condition and i try using Select case rather than If else. However i try to combine Select case with Like operator but it's not running as it should.
Code:
Sub SetHeader (FuncName as String) Dim xSheet as Worksheet
Select Case FuncName Case Is = "_EXCRT" Set xSheet = Activeworkbooks.Sheets(FuncName)
[Code] .........
This code running good with first case but it will not goto second case even the FuncName string are : "_MB51_00" or "_MB51_01".
While proof-reading my code, I've noticed that these two true select cases ("S" and "W") shown below wouldn't "yield" what I want. I've tried to replace AND (highlighted in red) with "," (comma) but I still get the same result - it still returns a value evaluating either one of two conditions but not BOTH:
Code: sChar = Split(Waypoint, ",") For j = LBound(sChar) To UBound(sChar) Step 3 Lat = sChar(j) Lon = sChar(j + 1) Elev = Val(sChar(j + 2)) Next j
I am trying to loop through and read values from 4 combo boxes which I have renamed SaveOptionBox1 to 4. They are ActiveX controls. The code throws no errors but I can't output the values of my array 'dirname' on the sheet to prove it worked.
Code: Sub Directory() Dim ROOT_PATH As String Dim cnt, dirnumber As Integer
I have a form with seven check boxes on it. The code that I have been working on is below. Using the select case works exactly as I want it to unless someone checks the combination of boxes that returns a "True" for both "Apples" and "Oranges". If that combination is selected I cannot get the select case to return the correct form. I was trying stay with the select case code but I am not sure that this is the correct approach for what I am trying to accomplish.
Private Sub CommandButton1_Click() If Form1.CheckBox1.Value = True Then X = "Apple" If Form1.CheckBox2.Value = True Then X = "Apple" If Form1.CheckBox3.Value = True Then X = "Apple" If Form1.CheckBox4.Value = True Then X = "Orange" If Form1.CheckBox5.Value Then X = "Orange" If Form1.CheckBox6.Value = True Then X = "Orange" If Form1.CheckBox7.Value = True Then X = "Orange" Select Case X..........
I'm trying to put the following into a workable VB code. I don't know whether it is best to use Select Case or If Then statements or whether either can perform the tasks.
Case 1 - Range(“N1”).Value = “KG” Then Range(“M1”).Value = Range(“V1”).Value Case 2 - Range(“N1”).Value = “M2” Then Range(“M1”).Value = Range(“V1”).Value Case 3 - Range(“N1”).Value = “NO” Then Range(“M1”).Value = Range(“U1”).Value
i hv a excel file which have 200k row. recently i learn from this forum using VBA (excel macro code) for select some data for analysis. it take very long. any suggestion will be apprecaited.
i already increase my note book ram to 2GB. it still slow.
For Each rCell In Range("D2:CU5") Select Case rCell.Value Case 1, 0.000000001 To 89.499999999 rCell.Interior.ColorIndex = 3 Case 2, 89.5 To 99.499999999 rCell.Interior.ColorIndex = 6 Case 3, 99.5 To 109.499999999 rCell.Interior.ColorIndex = 4 Case 4, 109.5 To 999 rCell.Interior.ColorIndex = 8 Case Else rCell.Interior.ColorIndex = 15 End Select Next rCell
End Sub
However if a value within the range "D2:CU5" happens to be #Div/0! (or any error value for that matter) I get a debug error message. Then the CASE ELSE formatting doesn't apply. I expect that everything else should fall under CASE ELSE. Is there a way to handle this i.e. the formatting under CASE ELSE applies?
DayCompare = Day(Date) Select Case DayCompare Case Is < 7 And Sheets("Roster").Range("D8") = "N" MsgBox "Hello." Case Is > 15 And Sheets("Roster").Range("D8") = "N" MsgBox "Goodbye." Case Else End Select
I thought this would work OK, however even though today is the 10th, and therefore ignored by this statement, it is picked up in the >=15 statement
What I'm trying to do is use SELECT CASE to do conditional formatting on a range of cells that I've named "ContactDate" (this range covers cells J3 to J42). I need to loop through the ContactDate range and test the current cell (which is in date format) against the cell in the adjacent column to the left (which is also in date format). The cell and font colours are to change based on the number of days difference. Below is the VBA code that I'm using. When I run it, it doesn't do anything to the cells. When I step through the code, however, and test it using the Immediate window it appears to work.
Sub ConditionalFormat()
Dim CurrentCell As Range
For Each CurrentCell In Range("ContactDate") Select Case CurrentCell Case CurrentCell - CurrentCell.Offset(0, -1) <= 1 CurrentCell.Interior.ColorIndex = 3 ' Changes background to red CurrentCell.Font.ColorIndex = 2 'Changes font to white Case CurrentCell - CurrentCell.Offset(0, -1) = 2 CurrentCell.Interior.ColorIndex = 5 'Changes background to blue CurrentCell.Font.ColorIndex = 2 'Changes font to white Case CurrentCell - CurrentCell.Offset(0, -1) = 3 CurrentCell.Interior.ColorIndex = 16 'Changes background to grey CurrentCell.Font.ColorIndex = 2 'Changes font to white Case CurrentCell - CurrentCell.Offset(0, -1) >= 4 CurrentCell.Interior.ColorIndex = 10 'Changes background to forest green CurrentCell.Font.ColorIndex = 2 'Changes font to white Case Else CurrentCell.Interior.ColorIndex = xlNone 'Changes background to none CurrentCell.Font.ColorIndex = xlAutomatic 'Changes font to automatic End Select Next CurrentCell
Select Case Statements. if there is a better or shorter way to write the following code.
Select Case Sheet1. Range("I6") Case 0 Exit Sub Case 5 returnvalue = Sheet1.Range("G35").Value If returnvalue = "Yes" Then
myresult = MsgBox("Hi " & Sheet1.Name & ". Your prescription for " & Sheet1.Range("C6") & " expires " & Sheet1.Range("B2") & " Would you like like an Email reminder?", 36) Else End If Select Case myresult Case Is = vbYes Application.Run "Mail" Sheet1.Range("H6") = 0 Case Is = vbNo Sheet2.Activate....................
