Date Spans In Select Case
Jul 24, 2007
I'm trying to write a code to define different months and shade their backgrounds but i can't seem to get it to work.
The fields are set out as follows:
01/01/2007 13:31
The code i have at current is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:Z1000")) Is Nothing Then
Select Case Target
Case 1 / 1 / 2007 To 31 / 1 / 2007
icolor = 6
Case 1 / 2 / 2007 To 31 / 2 / 2007
icolor = 7
Case 1 / 3 / 2007 To 31 / 3 / 2007
icolor = 8
View 9 Replies
ADVERTISEMENT
Dec 23, 2008
It sits in the "This Workbook" code window to run each time the wbook is opened. What I want it to do, is based on the date in Sheets("Control").Range("L3") select a particular cell in another sheet called "Risk Sum". I'm assuming this is easy to do, I've come up with the following, but it just doesnt want to work ...
View 9 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, 2007
I need to make formulas to count the number of weeks that each phase of a project takes, but I currently have the start and end dates of the phases. (See attachment)
View 5 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
