SELECT CASE Or If Statement?

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


ADVERTISEMENT

Utilize A Select Case Statement In Target Intersect Statement

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

Select Case Statement

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

VBA Implementing Select Case Statement - Compile Error

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

Custom Autofilter (select Case Statement, Two Dates)

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

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

I’ve 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 doesn’t work.

View 6 Replies View Related

Select Case, Case Else Copy From Above Cell

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

Select Case Error "Case Without Select Case"

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

Case Statement Not Working

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

VBA Case Statement Verification

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

Case Statement Getting A Type Mismatch

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

Using InStr Function In Case Statement

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

VBA Macro Change From Case To If Statement

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

Case Statement On Conditional Formatting

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

Case Statement For Conditional Formatting

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

Case Statement - VBA Code To Work Automatically

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

Select Case With Between Value?

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

Select Case Or If

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

Select Case Instead Of Using IF

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

Case Statement Changing Cell And Font Colors

Sep 25, 2009

I have the following Case statement that changes all the cells to the correct color, but does not change the Font color as specified in the Case. All the text is White (fcolor = 2) instead of just the 3 types of cells specified.

View 2 Replies View Related

Using Userform For Select Case?

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

Select Case Condition Not Being Met

Oct 15, 2008

Select Case Condition Not Being Met

View 4 Replies View Related

Select Case With Msgbox Pop Up

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

Select Case Construction

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

Is There A Difference Between Select Case And If..Then

Apr 17, 2009

Is there any difference between these 2 codes:

View 12 Replies View Related

Case Select With A Timer

May 5, 2009

If i have a case select say like this

View 4 Replies View Related

Simplifing Select Case

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

Select Case Using A Range

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

Long Select Case With If/Then

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

Loop Select Case

Sep 24, 2009

How do I loop a select case so that it ends at a specific cell.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved