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............................
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...
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.
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?
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? ....
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?
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)?
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.
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
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.
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.
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.
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.
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".
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
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
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..........
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