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


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

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 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.

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

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.

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.

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

VBA Intersect And Target

Jan 29, 2008

Im tring to run the procedure called KeyEventsOn ONLY when a single cell in range E29:E30 changes.

The Intersect and Target are borrowed from a book , I dont fully understand how these work. I am assuming that 'Target' should simply reflect the currently selected range, and the 'intersect' function runs a check as to whether the target is in the range to be checked.

The problem Im having is the variable 'Target'. If the above is true, why for example when I type 'ABC' into the current selected cell does the variable 'Target' show as "ABC" and not "$E$29" (or whatever cell is selected)...

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

Range Select Statement To Select A Cell

Jul 7, 2008

I want to put a range select statement to select a cell and count down 10 cells and copy.

View 9 Replies View Related

If Select Sheet, Select Range Statement

Oct 4, 2007

I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.

Private Sub Worksheet_Activate()
If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then
Me.ClearPrevious.Visible = True
Me.ClearPrevious.Visible = False
End If
End Sub

View 9 Replies View Related

Function-select Statement

Jul 21, 2008

I have this select statement:

Dim ResourceMonth As String
Dim mActiveSheet As String
ResourceMonth = Sheets("Finanace").Range("J2")
Select Case ResourceMonth
Case "Jan"
Cells(8, 4).Select
z = ActiveCell.Row
SSRR = ActiveCell.Value
mActiveSheet = "Jan"
Sheets(mActiveSheet).Range("[test1julycheckbook.xls]Jan!MaxHoursJan") = (Sheets("Workdays").Range("F2")) * 8

View 9 Replies View Related

Syntex Error In Select Statement

Apr 16, 2009

when i write the following line to open the recordset then it gives me syntex error.

rs.Open "select batchno from tblmain", cn, adOpenKeyset, adLockOptimistic, adCmdTable

and if i write the following line then it works fine.

rs.Open "tblmain", cn, adOpenKeyset, adLockOptimistic, adCmdTable

View 8 Replies View Related

VBA If Statement - Select Active Cell

Oct 14, 2013

If statement, any way where I can, when I run the macro, choose active cell?

Start macro
and it will ask for active cell,
check if active cell higher then 0
then set cell to color red

I have this atm:

Sub LookForValue()

' Declare Current as a worksheet object variable.
Dim Current As Worksheet

' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets

[Code] .........

View 8 Replies View Related

Run Time Error 1004 (select Statement)

Apr 13, 2007

I get an application defined or object defined error at the select statement 1004...I know I know I shouldn't be selecting in code so I'll fix that but why can't I set the range?

NumRows = Sheets("Timelines").Rows.Count
LastColumn = Sheets("Timelines").Range("A1").End(xlToRight).Column
LastRow = Sheets("Timelines").Range("A" & NumRows).End(xlUp).Row

Sheets("Timelines").Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
TIA, Charlie Brown.

View 7 Replies View Related

If Statement Within If Statement (recognize The Cell)

Oct 1, 2008

I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.

View 2 Replies View Related

Excel 2010 :: Use One Set Statement To Set Second Statement?

Feb 14, 2012

I am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.

My purpose is trying to look up


from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if


matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.

Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.


Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False


Windows 7 with Excel 2010

View 9 Replies View Related

ADODB SQL Statement ORDER BY Statement

May 5, 2014

I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:

Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer

Dim strTest As String

strTest = "test"

[Code] .......

Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:

Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String

strTest = "test"

[Code] ....

The result I am getting looks like this:

I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.

View 5 Replies View Related

Switch Statement Is Too Long For VBA. To Make The Switch Statement Work Over Two Lines

Jan 6, 2009

I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.

"Invalid procedure call or argument"

I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.

View 9 Replies View Related

Intersect In VBA: Select More Than One Cell In The Range

Jan 30, 2009

I have a piece of code that changes the format of a cell using an intersect statement on the range in question. However, if I try and select more than one cell in the range then an error message is returned: 'Run Time Error 13': Type mismatch. I know that it is caused by selectin multiple cells - is there a way to error handle this so that, when multiple cells are selected, it ignores the intersect statement?

View 2 Replies View Related

CASE Depending On Target Value

Jan 20, 2009

How would I set up a series of CASES depending on the Target.Value (a doubleclick code within spreadsheet) such that IF target value isblank, then change to Φ. If target value is Φ, then change to "NA". If target value is "NA" then clear contents
Would CASE be the best way to go or a series of IF's?

View 3 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.

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

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