Nested If Or Select Case Statement To Handle A Worksheet Event
Jan 31, 2010
I would like to have a nested if or select case statement to handle a worksheet event.
The conditions it will check are:
1.Make sure target is w/in range, otherwise EXIT
2.Make sure that target offset value is not empty, otherwise display message
3.All is good, open form
Ive tried various formulations and positionings of the statements ,but not all conditions are met with equal success.
What happens is I get the the first two conditions, but the third doesnt work.
View 6 Replies
ADVERTISEMENT
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
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 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
Apr 25, 2013
I'm having problems implementing a Select Case statement. Keep getting case without select case at Case condition2.
Code:
Select Case Condition
Case condition1
If Cells(rsRow, rsCol).Value = "" Then
Cells(rsRow, rsCol) = TextBox2.Value
[Code] .......
View 3 Replies
View Related
Jan 14, 2007
I have a workbook with 2 sheets I want to make an autofilter by two method :
- select case statement
- two dates
View 4 Replies
View Related
Jun 8, 2006
I'm trying to create a nested IFs statement that looks at a range of cells between 2 tabs within the same worksheet, determines if there is a value of 1 in ANY of those cells, and if there is, to multiply the value of yet another cell by 1 in a cell on the second tab. So, if 1 is a value in any cell of Tab 1 cells C82 to C86, then in Tab 2 cell B21, multiply 1 by the value in 'Tab 1 Cell H5'.
I've tried this =IF(('LM fundamentals'!C82>=1), ('LM fundamentals'!C83>=1), ('LM fundamentals'!C84>=1), ('LM fundamentals'!C85>=1), ('LM fundamentals'!C86>=1), 'Order Information'!$B$21!=(1* 'LMfundamentals'!$H$5!))
I even tried a vlookup, having given the cell range in Tab 1 a name, etc. But I have little skill with logic statements, apparently.
View 5 Replies
View Related
Apr 29, 2007
The code handles keydown, keypress events with no problem. Also similar change events, and probably more.
This code is at UF initialize event:
Option Explicit
Dim TextBoxes() As New Class1
Dim ComboBoxes() As New Class1
Private Sub UserForm_Initialize()
Dim TCount As Long, CCount As Long, c As Control
TCount = 0
CCount = 0
'Set TabIndexCollection = New Collection
For Each c In Me.Controls
' TabIndexCollection.Add c, CStr(c.TabIndex)
If TypeOf c Is MSForms.TextBox Then................
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 27, 2009
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
View 9 Replies
View Related
Oct 26, 2009
I wrote this short program that parses out last name and zip code from one cell into two cells so I can map the sales data. I flagged each sale as either member, non-member, or neither, based on the prices of the items (members get 25% off). I used the case statement below.
the problem is that for one of the sale items it's getting flagged as "neither" despite being at the members price level. it works for that same item in other records. the quantity at error point is 3 for two of the errors and 6 for the third, but the strange thing is it works for a few other sale records in which the person also bought 3 of that item (the quantity is not the culprit, or sole culprit).
Here's the code. Obviously Price and Quant are the two variables involved. I declared Price as a double and Quant as an integer, even though both have two decimal places in the data file. That way the product of Quant and each items price for one and Price will both have only two decimal places even though it was rounding off that product to two decimal points without declaring either variable. I cut out some of the case statement so as not to exceed the character limit.
View 4 Replies
View Related
Apr 6, 2008
i have followed the following criteria correctly
Create a function GetProducerRow() which takes in one String input (give it any name following the conventions) and returns an Integer.
Write a Select Case statement inside the function.It should compare the input string with the various companies listed in the Voting tables in cells F5:F9.
The Case should not use hard coded values for the names of the companies but something like Range("F5").Value.
In each case, set the return value by assigning the appropriate row number. For example, the value assigned would be 5 for "The Hershey Company" store in F5.
Remember to write Case statement for each of the 5 producers.
Add the Case Else in-case none of the producers match the input and have the return value set to 0.
Function GetProducerRow(strProducer As String) As Integer
strProducer = Range("F5")
Select Case strProducer
View 9 Replies
View Related
Jun 16, 2009
I made this case statment below to look at a cell in Q and if the cell value is "Large Area" then the cell in P same row should be 1 if the case is Varsity then it would be 2 however I keep getting a Type Mismatch. I tried Picker.Text and Picker.Value as well I didn't get errors but it didn't work either.
View 5 Replies
View Related
Sep 9, 2012
I am attempting to use the following code to move certain sheets to specific locations in my workbook. The case statement containing the InStr function isn't working eventhough the "Data" tab does exist and the InStr function does return 1.
Code:
ThisSheetToMove = Sheets(SCount).Name
Select Case ThisSheetToMove
Case "Schedule"
Sheets("Schedule").Move Before:=Sheets(1)
Case InStr(1, Trim(ThisSheetToMove), "Data") > 0
Sheets(ThisSheetToMove).Move After:=Sheets(SShtLast)
End Select
View 4 Replies
View Related
Nov 19, 2013
I want to use wildcards and it seems I cannot do it with case select, is it possible?
Its required because for the list of PSV's I have more than 2000 names and I would like to use *PSV* for them all instead of typing them all.
Here is the code below:
Option Explicit
Sub Colorize(Rng As Range)
Dim Cel As Range
Application.DisplayAlerts = False
Application.EnableEvents = False
[Code] .......
View 4 Replies
View Related
Dec 28, 2007
Set MyRange = Range("A1:AZ9615") ' Range to apply format to
For Each Item In MyRange
Select Case Item.Value
Case "1780", "1800", "1810", "2050", "6170"
Item.Font.ColorIndex = 3
'x = 3
Case Else
x = xlNone
End Select
Item.Rows.Interior.ColorIndex = x
Next Item
I want it to search that range and turn those Numbers in Quotes to Red, which it works fine, but Somtimes those numbers are Imbedded in a string excample "1810-1-DAV". So my Question is When It turns my normal numbers red, how can I get it to turn the STRING RED ALSO?
View 9 Replies
View Related
Apr 25, 2008
I am trying to eliminate the use of formulas for conditional formatting in my code. I heard that the CASE statement might work for this. Below is the code I have but the case does not like the second case statement.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range(("S2:V" & TTRows), ("W2:W" & TTRows))) Is Nothing Then
Select Case Target
Case Is >= 0.8
icolor = 3
Case is >= 0.7 and not >= 0.8
icolor = 6
Case Else
icolor = 0
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
View 9 Replies
View Related
Mar 7, 2014
I am trying to do a nested IF statement in which if Cell A has a number, display it. If Cell A = "", look in Cell B. If Cell B has a number, display it but if its "", then display Cell C.
I have the IF statement working for the first two pieces but don't know how to incorporate a third statement: =IF(A="",B,A)
This works - how do I get it to check Cell B and show Cell C if B is empty?
View 2 Replies
View Related
Mar 7, 2009
I work on a sea going vessel and manage an Emergency Response document. My dilemma is automatically assigning a Muster location based upon data entered in a certain column. I'm having a problem figuring out this Nested IF Statement: Column J will either be blank (no entry), "Fire" or "HRC".
If Column J is blank, column A needs to read "STBD". If column J has either "Fire" or "HRC" column A should read "PORT". See attached Excel file for an example with only 3 or 4 rows.
View 2 Replies
View Related
Apr 7, 2009
I dont know how to nest and if (if that is the right way to go about it). this is what I have ....
View 10 Replies
View Related
Feb 20, 2014
I have a nested IF statement that I am using in an application I am building in Excel. I am using logic to get the current month and then evaluate whether the value is a number. I would like to know if there is a better way to perform what I am trying to accomplish using another function. I am trying to cocantonate the the statements together which has worked in the past but not for this particular function.See Below... The function works if i use 7 IF statements.
=IF(I3="Jan",IF(ISNUMBER(SEARCH("NA",Data!$B$355)),"NA",Data!$B$355),
IF(I3="Feb",IF(ISNUMBER(SEARCH("NA",Data!$C$355)),"NA",Data!$C$355),
IF(I3="Mar",IF(ISNUMBER(SEARCH("NA",Data!$D$355)),"NA",Data!$D$355),
[Code] ............
View 9 Replies
View Related
Jun 26, 2007
In the example below I have created a validation list in B1 that references D2:D8. What I am then trying to do for cell B2 is create a vlookup and nested if statement that says if b1 is a number lookup the value in column D and return the value in colunm E and if b1 doesn't = a number, ie 'none' then return a null value. The same goes for B3 where if b1 is a number lookup the value in column D and return the value in columm F and if b1 doesn't = a number, ie 'none' then return a null value.dropdown list problem.
************************************************************************>Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1Numbernone*NumberDescriptionRef2Description**none**3Ref**1one44***2two55***3three66***4four77***5five88***6six9Sheet2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Apr 17, 2008
I am trying to write a nested if statement for my excel table. I calculated the betas of the stock portfolio. I would like to create an if statement that would do something like this:
if beta is less than 1 -- Below Average Risk
if beta is greater than 1 but less than 1.25 -- Average Risk
if beta is greater than 1.25 but less than 1.75 --Medium Risk
if beta is greater than 1.75 -- High Risk...
View 9 Replies
View Related
Jul 3, 2009
I have the following formula which consumes forward demand against an initial stock figures, it nests the next 7 buckets and then finally takes an average of the next 13 buckets to give a weeks forward cover value, unfortunately if the next 13 values are 0 it returns DIV value, how do I nest an ISERR function to replace DIV with 0 ? Thanks in anticipation as I am pulling my hair out, whats left! alternatively a formula to consume demand past 7 buckets?
IF(M40
View 9 Replies
View Related
Oct 11, 2006
I have the following nested if statement that works very well which is designed to ensure data is entered in a specified format.
Sub Enter_Batch_Number()
Dim ans As String
Err_Invalid:
ans = Application.InputBox("Please Enter Batch Number," & vbNewLine & "Batch Number Must Be One Letter & 4 Digits. e.g A9999")
Range("D1") = ans
ActiveSheet.Range("D1").Value = (UCase(ActiveSheet.Range("D1").Value))
If Range("D1") = False Then
MsgBox "Batch Number Must be Entered", vbCritical + vbOKOnly, "Invalid entry"
Goto Err_Invalid......................
this then says that the file exists even when i know it does not. I'm sure as usual I'm missing something obvious but cant find it yet
View 5 Replies
View Related
May 31, 2007
I have spent the last 2 hours trying to figure out why this formula will not work. I am guessing it is probably something simple like a "format" issue. Okay here is my formula, and the data it represents.
Column F is The Primary Tool. This cell will have X if it applies, or be blank if it does not. the formula is based on whether or not there is an X in this field. Column D is the Tab Title. This cell can be empty or contain a title. If the cell is Blank, the result should be blank, if the title is the same title as the cell above it, the result should be the next consecutive number. And if D is neither blank nor the same title as above it, it should display 1. Column N is where a numerical value for each title is displayed.
=IF($ F8="X",IF(D8="","",IF(D8=D7,L7,L7+1,(IF(D7="",1),""))))
View 6 Replies
View Related
Aug 27, 2013
I've selected a case statement (see below) but I want to that code to work automatically. So i do not want to press a button to show a certain value in S2. But I want cell c2 to automatically pop up the value (depending of the value in V4). So kind like an if/then statement.
Sub CASEMEDEWERKER()
Select Case Range("F4").Value
Case "Medewerker"
Range("S2") = "M"
Case "Interview"
Range("S2") = "I"
Case "Data"
Range("S2") = "D"
Case "Observatie"
Range("S2") = "O"
End Select
End Sub
View 4 Replies
View Related
Jun 17, 2008
this line
Cells. Find(What:=searchterm, After:=Cells(1, 1), MatchCase:=False).Activate 'find pn that is stored in searchterm
causes a runtime error 91 when there are no results. "Object variable or with block variable not set"
Ideally, when there are no results, I would like to enter a loop, where I could provide a drop down list of fuzzy matches, but I am not sure excel can do fuzzy matches. I'd settle for just highlighting the row and moving on... any help?
Option Explicit
Sub Macro1()
ActiveSheet.Cells.Find(What:="AES", After:=Cells(1, 1), MatchCase:=False).Activate 'find the aes part number column
Dim aescolumn 'create variable for index value of aes column
aescolumn = ActiveCell.Column 'set variable to index number
View 3 Replies
View Related
