case statement

  1. Johnny Thunder

    VBA Help - If Cell Contains Partial String Then.......

    Hello all, Have a question that I thought I could easily answer but for whatever reason it doesn't seem to be working so another pair of eyes may help. I have a For Each Cell in Range Statement that looks at a Doc Type (2 Digit Variable) and when it finds "KR" to Search a Offset Cell for...
  2. S

    Multiple case statements VBA

    I need to search column E for specific values and if that row has them then it will not delete the row The problem is that I need to search it based on if it starts with something. So in the example below if cell E98 started with "AG____ " then that row would not be deleted Dim iLastRow As...
  3. Johnny Thunder

    VBA Help - Select Case with Greater than or Less than Statement

    Hi Guys, Working on a piece of code and not getting the desired results. My code is looking in column C for a specific cell with text and giving me a row number, I am using the row number to dynamically set how many pages to make the worksheet print too (i.e small amount of data print to 1...
  4. C

    Case Statement for Multiple Checkboxes Hiding/Unhiding Columns

    Hi! :) I'm new to VBA and need some help changing this to a case statement. Thank you for your help!!! Cheryl If ActiveSheet.Shapes("Check Box 110").ControlFormat.Value = 1 Then Sheets("DHMO Copay").Columns("C").EntireColumn.Hidden = False Else: Sheets("DHMO...
  5. R

    Hiding/Showing Columns Based on Drop-Down List - VBA

    Hi All, I have been struggling to get this working. I am trying to hide columns (C thru AA is the range) based on the value selected from the drop down in cell A12. The values are something like "Show All Details", "Show Financials", "Show KPI Results".... etc. For Example, i want to hide...
  6. T

    Select Case - Named Ranges

    I have a spreadsheet that contains currency name in col A e.g. USD, AUD etc. Col B contains the amount that needs to be converted into GBP. The calculations will happen in the VBA code below and results stored in col C, D and E</SPAN> Sub SoSo()</SPAN></SPAN> Dim Rng As Range</SPAN></SPAN> Dim...
  7. E

    Case or If statement

    I have the following code to change a cell's color based on certain conditions. I am comparing the values of two cells on the same worksheet. I have tried this with If Else If, seperate If statements and Case statements but cannot get it to work. Any clues would be appreciated and Happy...
  8. N

    How do I speed up a CASE statement that loops through a large data set?

    Hi there I have a CASE statement that is looping through a very large data set (>75,000 rows) and if the state says "New South Wales" i need it changed to NSW. I need this abbreviation for all Australian states. My code works but it is taking FOREVER to loop through all the data. Can i convert...
  9. J

    Copy-Pasting Dynamically Using Case Statements And Named Ranges

    I want to do a simple copy and paste from one tab of my workbook to another. I created 24 named ranges (not efficient I know), 12 for the copies and 12 for the pastes, again all a part of 12 cases. The copy is somewhat static, it will always be one row of cells, the reason I have it to be...
  10. F

    Case with like

    hi, I have macro where I'm opening workbooks and for diffrent sheets I want to run code. ... Set wb = Workbooks.Open(strPath2 & strFile2) Dim myArray() As String Dim myCount, NumShts As Integer NumShts = wb.Worksheets.Count ReDim myArray(1 To NumShts) For myCount = 1...
  11. K

    VBA help! Function Case Statement and multi conditions for cell coloring

    Ok - I admit - I am adding two questions into one thread.:eeek: First scenario: In column G - I have different phases. I need to calucate how many dates it's been in that phase. It's an easy formula =networkdays(O2,Today(), Holidays A1:A10). O2 is the day that it moved into that phase...
  12. LostInTheTrees

    Combining Conditionally Visible Worksheets with Case Statement in VBA

    Hi! I'm using Excel 2010 (Windows 7) to develop a workbook with the goal of tying conditionally visible worksheets to a case statement. Conceptually, I want a user to enter a password and click on the enter password button, and have the sheets they are authorized to see appear in the tabs...
  13. Z

    How should I tackle this problem?

    Hey so I just wanted to see what the opinions are on how I should tackle the probelm I am trying to solve. Below is what my table looks like and I need to paste in a number to its corripsonding cell. The program should find out what the crop is (found on another worksheet) and then find out...
  14. C

    Select Case Statement in For Each Loop

    Hi there, will do my best to be brief: I have the code below to loop through cells in column A and then place a result into column B based upon the value in column A. I also want the value of the cell to be put to Upper Case only for the purposes of comparison, i.e. I do not need it changed in...
  15. Gingertrees

    custom function to search for multiple instr?

    I'm trying to write a function to test if a cell contains a website. So far I've got: Function IsURL(text) Dim test As Variant Dim eml As Variant eml = InStr(text, "@") 'if not present, position = 0 test = InStr(text, ".com") If eml > 1 Then IsURL = "email" ElseIf test > 4 Then IsURL =...
  16. J

    Case Statement with Wild Cards

    Hi, I'm trying to delete sheets that have specific names or similar (e.g. delete "Sheet1" and "Sheet1 (2)") I am using the following code: For Each ws In Worksheets Select Case ws.Name Case "Remaining*", _ "No_Break*", _ "Listed_Instruments*", _...
  17. Maverick99

    To Call a Sub from a Case Statement

    I have run across some code that uses Case Statements, based upon a user's Button Selection from a UserForm. This is located in the UserForm Event Module. I thought this would be straight forward to have it call a Sub "WriteToLog," if the Case Statement is true. However, it's not working for...
  18. G

    Simple Code to open multiple hidden worksheets

    Hello, Very new to VBA. Working off a code that hides worksheets until a password given to open them. I want to modify this so that it will open more than one sheet... What I have: Option Compare Text Const pWord2 = "XXX" Sub showsheets2() Select Case InputBox("Enter Raw Data...
  19. R

    Case Statement Usage

    Please help me code the proper method to using a Case Statement. Here's what I have coded: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) '---------------------------------------------- ' Test where the double click occurred...
  20. D

    Run code when formula updates.

    I am using this code to update the cell color in F4:F3000 base on the corresponding value in each case, however because I am using a formula to update the cells containing each case this operation is not functioning only the formula is updating, not the actual value in E4:E3000. Is it possible...

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top