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


ADVERTISEMENT

Macro To Change Case

Mar 31, 2007

And last but not least, is there a macro where I can perform a "change case" on the titles of all my graphs to be using "title case"?

To recap, this is what you have all come up with so far:

Sub DoAll()

Dim wb As Workbook
Dim ws As Worksheet
Dim objCht As ChartObject

For Each wb In Application.Workbooks

View 3 Replies View Related

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

If Statement In Macro: Macro To Change A Range Of Cells Colours Based On A Single Cell?

Mar 16, 2007

1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?

2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?

View 2 Replies View Related

Proper Case/Sentence Case In Macro Code

May 8, 2008

Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub

fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"

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

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

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

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

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

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 View Related

Change Case Without A Formula

Mar 3, 2008

is there a way to change the case of a cell/column without having to use a formula, i.e. just like you would in Word?

The formula seems to be a huge pain and I have to do lots of quick case changes in a large document.

View 9 Replies View Related

Change Case Of Text

Mar 22, 2008

I have the following text in B3

The Average of the last 6 items in column A is R112.50

I want a formula that will make the first letter of the sentence in proper case i.e "The average" as well as "R" before the value in proper case i.e R 112.50

See example below ...

View 9 Replies View Related

Change To Proper Case In VBA

Aug 10, 2009

I found this bit of Worksheet_Change code to change the target area to UpperCase. This works fine.

If Not Intersect(Target, Columns(2)) Is Nothing Then
Set rng1 = Intersect(Target, Columns(2))
Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
For Each cell In rng2
If cell.Formula "" Then
cell.Formula = Format(cell.Formula, ">")
End If
Next cell
End If
I could not find anything telling me what the ">" means. I'm assuming that it is a special symbol/wildcard for UCase in VBA.

My question(s), is there a symbol for ProperCase so I can use the same code, just making it change the Target column to Proper? Also is there a list of the special symbols.

View 9 Replies View Related

Change To Case Construct

Feb 20, 2007

I use the applicaiton timer but it said time is 5 so what's the problems? Below i want to change to case construct.

Sub GreetMe()
If Time < 0.5 Then
MsgBox "Good morning!" & " Its now " & Time, , The Time
ElseIf Time >= 0.5 And Time < 0.75 Then
MsgBox "Good afternoon!" & " Its now " & Time, , The Time
Else
MsgBox "Good evening!" & " Its now " & Time, , The Time
End If
End Sub

this is what i did so far but still having problems with it with case construct

Sub GreetMe()
Dim morning As Integer
Dim afternoon As String
Dim goodevening As Integer
Select Case morning
Case Application.AutoRecover.Time < 0.5.......................

View 3 Replies View Related

How To Change Lower Case To Uppercase

Apr 28, 2009

How to change the lowercase letters to uppercase letters?

While using the userform, for example "name" is entered in the form.

My requirement is the values should reflect as uppercase letters like "NAME"

View 2 Replies View Related

Change Case In Multiple Ranges

Sep 1, 2009

I needed to change the case of entries in range B8:B100 to uppercase, for which I used the VBA I have been using, and it works fine. I now require to also change the case of ranges C8:C100, D8:D100 and E8:E100 to propercase. So, I changed the script to:

View 4 Replies View Related

Change Value From Uppercase To Lower Case

Jan 22, 2010

I am trying to change string values of a name from uppercase to lowercase. The data is layed out as lastname then first name. As an example, I have the following values in column C:

ANDERSON MICHAEL
SMITH SUSAN P
JOHNSON PATRICK
JORDAN MICHAEL R
WOODS TIGER
CLINTON WILLIAM J

I am trying to convert to following (with "," between lastname & firstname):

Anderson, Michael
Smith, Susan P
Johnson, Patrick
Jordan, Michael R
Woods, Tiger
Clinton, William J

I have a start of how I think the code should flow but can't figure out how to do the conversion.

View 4 Replies View Related

Change Case In Upper And Lower

Sep 6, 2005

=upper("venkat") returns VENKAT
=upper(a1) returns the text in A1 in upper case

similary lower
proper will turn the first letter into uppercase

is this what you want;.

Terry <terrybetts11138@hotmail.com> wrote in message
news:dbl0dk$poa$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Win XP Pro
> Office 2003
>
> Using "Excel" and wish to select any text in a worksheet to ALTER the case
> to either upper or lower.
> When using "Word" it is easy via the menu.
> Is there a menu driven option in Excel
of using a function for this purpose.

View 14 Replies View Related

Change Case From Lower To Upper

Sep 7, 2005

find the "add in" that converts the case text
from lower to upper in excel. i've already downloaded this before but can't
remember where from and it is much easier than creating a formula or a macro

View 9 Replies View Related

Change Case Size Code?

Feb 2, 2012

I have found the code below that is very good and changes the case size to Upper, Proper or Lower. The problem is that when I select a cell, range of cells, column or row it changes the entire sheet instead of the selected range.

Code:
Sub CaseSize()
Dim myCase, rng As Range, r As Range
myCase = Application.InputBox("Enter" & vbLf & "1 for Upper Case" & vbLf & _
"2 for Lower Case" & vbLf & "3 for Proper Case", Type:=1)
If (myCase = False) + (Not myCase Like "[1-3]") Then Exit Sub
On Error Resume Next

[code]....

View 9 Replies View Related

How To Change And Entire Sheet To Upper Case

Jan 28, 2007

Is it possible to change and entire sheet to Upper case using the 'Upper' function?

We are need to change all the text to caps but can only seem to apply the function to one cell at a time and then copy it down for the column. This is going to be a fairly labour intensive process as we have many columns.

View 12 Replies View Related

Change Cells In Column To Upper Case

Oct 28, 2009

How can I make this code fire for column 2 AND column 3?

View 2 Replies View Related







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