Select Case Multiple Options
Apr 5, 2007
I have a Case Select statement I just learned how to use yesterday (kind of) What I am wondering is can I have multiple arguements or conditions to each case? For example: my sheet is a "Job Cost Estimate" for construction sales people. The section I am working on is "Fasteners"
I have 2 drop down boxes per line. The first box selects the fastener type, (Screw, Nail, Auger) the next box selects the size in inches and half inches (16 different sizes). Currently the select statement is set up for screws and says if this size then this price. I want to say if this type of fastener and this size then this price.
Additionally, when I select a size in my drop down box, I have to click somewhere else to get it to calculate. Is there a way to select the option and it auto calculates?
Here is my statement in my Worksheet selection change section:
Select Case Range("AZ21").Value ....
View 9 Replies
ADVERTISEMENT
May 14, 2012
correct below code?
Code:
Private Sub WorkSheet_Change(ByVal target As Range)
Dim rngdv As Range
Dim oldval As String
Dim newval As String
If target.Count > 1 Then GoTo exithandler
On Error Resume Next
Set rngdv = Cells.spcialcells(xlCellTypeAllValidation)
[code].....
i would like to select multiple options from dropdown... but it is not working...
View 4 Replies
View Related
Jun 3, 2009
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?
View 9 Replies
View Related
Jul 20, 2006
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? ....
View 9 Replies
View Related
Mar 21, 2014
how to select, for example, 10 consecutive options that follow each other within the filter drop down box? (instead of individually clicking/checking each option) for example, clicking a cell A1, pressing and holding shift, and clicking cell A10, which highlights cells 1 - 10...
Is something like this possible within a filter drop down box?
View 3 Replies
View Related
May 5, 2014
I want to fill up specific text to cell according to values in columns before. I wrote this, but it doesn't work:
Sub test()
Dim Lastrow As Long
Lastrow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).row - 1
Dim i As Long
[Code].....
View 2 Replies
View Related
Oct 24, 2008
I have the following:
Column A---------Column G
EACH-------------(Formula = (B1*C1)*.4536
CASE-------------(Formula = (B1*C1)*.4536
SHTB-------------(Formula = (B1*C1*D1)
SHTD-------------(Formula = (B1*C1*D1)
MFG--------------(Formula = B1*C1)
NO---------------(Formula = B1*C1)
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.
View 9 Replies
View Related
Dec 19, 2008
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
View 9 Replies
View Related
Feb 12, 2014
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.
[Code].....
View 14 Replies
View Related
Oct 15, 2008
Select Case Condition Not Being Met
View 4 Replies
View Related
Oct 16, 2008
I don't see what I am doing wrong in this code using "Select Case". When activated and the correct string is entered I should get a msgbox pop up.
View 8 Replies
View Related
Feb 2, 2009
I seem to be unable to crack the bug in my macro... I want to define 2 variables before launching the next macro with the defined variables.
When I run this macro - the debugger points all the time to the following line: ...
View 4 Replies
View Related
Apr 17, 2009
Is there any difference between these 2 codes:
View 12 Replies
View Related
May 5, 2009
If i have a case select say like this
View 4 Replies
View Related
May 8, 2009
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.
View 14 Replies
View Related
Jul 31, 2009
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.
View 6 Replies
View Related
Sep 29, 2009
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.
View 3 Replies
View Related
Sep 24, 2009
How do I loop a select case so that it ends at a specific cell.
View 14 Replies
View Related
Feb 9, 2012
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".
View 7 Replies
View Related
Aug 29, 2012
Can I do a select case on an array?
Something like?
Code:
Dim Array As Variant
Dim Range as Range
Array = Array(Worksheets("sheet1"), ("sheet2"), ("sheet3"))
Select Case Array
[Code] ........
View 9 Replies
View Related
Nov 4, 2012
How do you:
Code:
Select Case x
Case _______ -1 = -1 Then
'code
ElseIf x = 1 Then
'code
End If
View 4 Replies
View Related
Jan 7, 2013
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
[code].....
View 3 Replies
View Related
May 29, 2013
I am having issues with a Select Case failing at the end. Here is the code:
Code:
Public jan As Double
Public feb As Double
Public mar As Double
Public apr As Double
Public may As Double
Public jun As Double
[Code] ........
I have bolded the font on where it fails. If I delete the bolded lines the code runs perfect.
View 3 Replies
View Related
Aug 9, 2013
I have a subroutine involving a Select Case and a loop.
I am of the opinion that writing the loop within the Select Case would be faster than having the Select Case within the loop.
Am I correct or does it not make a difference?
View 3 Replies
View Related
Sep 18, 2013
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
[Code]....
View 3 Replies
View Related
Feb 21, 2007
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..........
View 2 Replies
View Related
Nov 8, 2007
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
View 9 Replies
View Related
Dec 2, 2007
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.
View 9 Replies
View Related
Jan 31, 2008
I have below codes:
Private Sub Worksheet_Calculate()
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?
View 9 Replies
View Related
Sep 1, 2008
Is there a way to count if there are 5 or more in a range without
using 'select case'
or not using the 'intcounter' bit bit of code?
Set Startcell = Sheets("meetingstodate").Cells(xlrow, 2)
Set Endcell = Sheets("meetingstodate").Cells(xlrow, 7)
Set rng = Range(Startcell, Endcell)
lastrow = Sheets("meetingstodate").Cells(xlrow, 2).End(xlDown).Row
For xlrow = 2 To lastrow
arr = Range(Startcell, Endcell).Value
For j = 1 To UBound(arr)
For k = 1 To UBound(arr, 2)
Select Case arr(j, k)
Case A
intcounter1 = 1
Case B .....................
View 9 Replies
View Related
