case select

Hello! I am needing assistance on the code for Select Case in VBA. I have a column of Integers, x, and want to use the Select case to find their value in a piecwise function. I'm not very skilled with VBA or Macros, so I did some research and this is what I came up with: Function Calc(x)...

What is a less rigorous way or writing this? Sub COSTPLANBREAKDOWN() Dim rngstart As Range Dim tst As Range, lastrow As Long Dim cl As Object Dim L As Long, strcells As String, lsr As String, toplvl As String, comm As String, flat As String Dim CLT As String, PLT As String...

Hi all VBA experts out there, I have a chart where a macro is able to change the series color using case select. However when I try to introduce wild card into this case select, I am hitting a road block. Below is what I got working, as you can see I would like to introduce a wild card...

Hello All, I've been trying to find a way to search down a single column and based on the text value in a cell, enter a numerical value in the adjacent cell to the left. I want to be able to assign the numbers to allow me to sort the text columns in a particular order, by using the numbers in...

Hi, Excel 2016 rolled out many new functions such as SWITCH, TEXTJOIN, etc. Unfortunately, these functions are not available for non-365 subscribers. I was wondering if somebody built a VBA UDF for SWITCH already? Its syntax is the following: Syntax SWITCH(expression, value1, result1...

I have a "Project" spreadsheet in Excel 2016 with a variety of columns to track potential projects for our organization. Column "M" is called "Status" and all cells have a drop-down list with values such as "Submitted", "Awarded", "Declined" etc. I would like a different message box to pop up...

I have lots of Case Select scenarios set up. I'm going to have to add a lot more. A greatly condensed sample is below. How can I recreate this scenario using a reference table? Beginning on row 84 to the end of the data, it's comparing what's in column O (college names) to hundreds/thousands of...

I am very new to VBA but have an ambitious goal that you may be able to help with. I have the code below which is a small part of a larger code from Ron De Bruin importing using ADO method. The code enables opening of a closed workbook and importing data from that workbook into current...

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

I've read around and do not know if a nested IF statement Would be best, or a Case Select, or there may be something else I am unaware of that would perform better in my scenario. The responses in column T should be one of the following(answers are in bold) and should be answered for all data...

Okay I am having a bit of a creativity/brain freeze issue here :stickouttounge: and cannot come up with a simple solution to really capture the used range on a column. At the moment I am selecting up to the next blank cell and I need to capture the used range. I simply want to change the format...

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

Objective: reference 3-digit number in column "D" on a row-by-row basis and assign a target price value based on condition Sub assignTargetPrice()' Dim x As Integer Dim targetPrice As Integer Dim i As Integer targetPrice = Cells(i, 186) x = Range("D2:D500").Select Do While Cells(i...

Dear Excel vba gurus, A question about filling in a range for a vba newbie: In column A of my sheet 1, I have a listing of 9000+ items, but all of these are text, with no values. Also, all of these are being drawn from a pool of around 300 unique items. I have numbers that corresponds to...

Hi There, Is there a way in VBA to use an IF statement and have multiple checks? So for example if I wanted to check a cell value for one of several string segments, could I do that with one if statement? I could do multiple ELSEIF statements or multiple CASE statements, but I thought it would...

I see answers to problems using 'case' , I think this is exactly what I need , but I don't know how to set it up and the syntax, does the case have numeric values or can they be text. I'm hoping to avoid nested if's , maybe even nested ifs within nested ifs

hello, i would need to create a Case is Statement where the criteria is complex, meaning i would have to use the AND. how should it be written to work? i would need something like Select Case valuetocompare case is <=3 AND case is >1 but if i write it like that i get an error. any help is...

Here is the code I wrote that doesn't work because I don't know how to set the column nnumber as an appropriate variable (I think that is the problem): Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim iColumn As Integor If Target.Count > 1 Then Exit Sub iColumn = Target.Column...

Hello there, can you please tell me whats wrong with case VbNo? its suposed to close the workboo when user select "No" Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True Application.DisplayAlerts = False Dim exit1 As Integer exit1 = MsgBox(" Would you like to save...