Skip Column In Select Case

Jul 7, 2007

I've been through the forum and I've got my VBA to check that columns 37, 38
and 40 are entered if column 2 = 'O'. However I just can't get it to exclude column 39 from the check! Is there a way of getting Case Else to do this or should I use an Array?

Dim rCell As Range
Dim strMessage As String
Dim RowCounter As Integer
Dim ColumnsChecked As Integer

For Each rCell In Range("B14:B5000")
RowCounter = rCell.Row
If rCell = "O" Then
For ColumnsChecked = 37 To 40
If Cells(RowCounter, ColumnsChecked).value = "" Then
Select Case ColumnsChecked
Case 37
strMessage = "COMMENTS"
Case 38
strMessage = "INVOICE NUMBER"
Case 40............................

View 4 Replies


ADVERTISEMENT

Select Case Code For Column A Values To Populate Results In Column B In Same Row?

May 28, 2014

I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").

I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...

[Code].....

View 7 Replies View Related

Select Case - Change Values After Finding Gender Column

Aug 22, 2012

I need some code that will find the column with the header of "Gender" and change the values from "M" and "F" to "Male" and "Female" but I keep running into issues.

I want to use a Select Case statement so I can set all other found values as blanks.

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

Transpose Function - How To SKIP / SELECT Certain Columns?

Feb 3, 2014

I am working on a =transpose function. Pretty simple right? Well now I am trying to have it SKIP/SELECT certain columns.

I have =transpose("date!B1:T1"), but now I want to "Select A,B,D,E,F"

=TRANSPOSE(INDIRECT("date!B1:T1"),"select A,B,D,E,F,G,H,P,Q,R,S,T")

View 1 Replies View Related

How To Select Range And Skip Over Hidden Columns

May 1, 2014

I have below a code that is selecting from A11 down as far as there is data and across a certain number of columns then sets a print area.

Currently the columns go A, B, JW, JX, JY, JZ, KA, KB as C through to JV are hidden. I have tried to offset the columns by 7 to see if it will select A-KB but it is selecting cells that are hidden and only selecting A-G. I need it to go past the hidden columns. Here is the code:

[Code] ......

Could I also get a code to go from A10 and move over to KB and create a range from there e.g. KB-KJ and last row from there?

View 10 Replies View Related

Count And Select Outcomes, Skip Duplicates

Oct 27, 2009

This code counts all cells with a value from column B1:B:10, C1:C10, D1:D10, E1:E10, F1:F10, G1:G10 and H1:H10. And then give the cells of the 3 lowest outcomes a blue backcolor. The problem is that the code also colors any duplicate outcomes. How can I let the code skip any following duplicate outcome(s)?

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

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

Select Case Combine With Like

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

Select Case On Array?

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

Select Case In A Range

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

(Select Case) With AND Operator?

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

Select Case Gets To End And Fails

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

Select Case And Loop?

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

Using Select Case In A Loop?

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

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







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